Transpose rows to columns in SQL with categorical values

February 9, 2024

Is there a way to transpose categorical values from rows to columns in SQL

Example

create table sample1 (
    Col1 nvarchar(max),
    [Values] nvarchar(max),
)

insert into sample1
values ('CatA','XCY'), ('CatB','XCY'), ('CatA','XC'), ('CatB','XC'), ('CatA','KJ'), ('CatA','KG'),('CatA','KFD'), ('CatB','KG')

select * from sample1 (will give you below values)

Col1    Values
CatA    XCY
CatB    XCY
CatA    XC
CatB    XC
CatA    KJ
CatA    KG
CatA    KFD
CatB    KG

expected output

CatA   CatB
XCY    XCY
XC     XC
KJ     NULL
KG     KG
KFD    NULL

I tried with below approach but not getting


select *
from
(
  select [Values], Col1
  from sample1
) d
pivot
(
  max([Values])
  for Col1 in (CatA, CatB)
) piv;

>Solution :

This can be solved by a full join, which makes sure both sides can get their values and fills out missing ones with NULLs:

create table sample1 (
Col1 nvarchar(max),
[Values] nvarchar(max),
)

insert into sample1
values ('CatA','XCY'), ('CatB','XCY'), ('CatA','XC'), ('CatB','XC'), ('CatA','KJ'), ('CatA','KG'),('CatA','KFD'), ('CatB','KG')

select a.[values] as catA, b.[values] as catB
from (
    select col1, [values]
    from sample1
    where col1 = 'CatA'
    ) a
full join (
    select col1, [values]
    from sample1
    where col1 = 'CatB'
    ) b
    ON b.[values] = a.[values]

Output: