Pivoting data to show unique values for specific columns

user_17114428 0
Viewed 68

I have a table:

ID col1 col2 col3 col4 col5
1 Y G CA DA EA
2 Y G CA DA EA
3 Y Q CA DA EA
1 X G RA DA EA
2 X G CA DA EA
3 X Q CA DA EA

For col1 and col2, I want to create 4 additional columns. Two for the distinct values for col1 (X,Y) and two for the distinct values for col2 (G,Q). For example, if the ID 1 have col1 = X then I want to display 'Y' under col1_X. How do I do that? I think I'll need a pivot/group by as I would want to eliminate duplicated rows of data.

Desired output:

ID col3 col4 col5 col1_X col1_Y col2_G col2_Q
1 CA DA EA Y Y Y N
1 RA DA EA Y Y Y N
2 CA DA EA Y Y Y N
3 CA DA EA Y Y N Y
pivot distinct ssms sql-server sql
2 Answers

Use a CASE expression for each new column:

SELECT DISTINCT ID, col3, col4, col5,
       CASE WHEN col1 = 'X' THEN 'Y' ELSE 'N' END col1_X,
       CASE WHEN col1 = 'Y' THEN 'Y' ELSE 'N' END col1_Y,
       CASE WHEN col2 = 'G' THEN 'Y' ELSE 'N' END col2_G,
       CASE WHEN col2 = 'Q' THEN 'Y' ELSE 'N' END col2_Q
FROM tablename;

See the demo.

user_104988280

This is a naive solution but may help to get to a more dynamic way of approaching the problem:

drop table if exists #test;
drop table if exists #result;

create table #test (
    [ID] int,
    [col1] varchar(1),
    [col2] varchar(1),
    [col3] varchar(2),
    [col4] varchar(2),
    [col5] varchar(2));

insert into #test ([ID], [col1], [col2], [col3], [col4], [col5]) 
values
(1, 'Y', 'G', 'CA', 'DA', 'EA'),
(2, 'Y', 'G', 'CA', 'DA', 'EA'),
(3, 'Y', 'Q', 'CA', 'DA', 'EA'),
(1, 'X', 'G', 'RA', 'DA', 'EA'),
(2, 'X', 'G', 'CA', 'DA', 'EA'),
(3, 'X', 'Q', 'CA', 'DA', 'EA');

-- create the output table
select 
distinct ID, col3, col4, col5, 
    'N' as col1_X, 'N' as col1_Y, 'N' as col2_G, 'N' as col2_Q
into #result 
from #Test;

-- update the results
update t1
set t1.col1_X = 'Y'
from #result t1
where exists (select * from #test t2 
    where t2.ID = t1.ID and t2.col3 = t1.col3 and t2.col4 = t1.col4 and t2.col5 = t1.col5 
    and t2.col1 = 'X');

update t1
set t1.col1_Y = 'Y'
from #result t1
where exists (select * from #test t2 
    where t2.ID = t1.ID and t2.col3 = t1.col3 and t2.col4 = t1.col4 and t2.col5 = t1.col5
    and t2.col1 = 'Y');

update t1
set t1.col2_G = 'Y'
from #result t1
where exists (select * from #test t2 
    where t2.ID = t1.ID and t2.col3 = t1.col3 and t2.col4 = t1.col4 and t2.col5 = t1.col5
    and t2.col2 = 'G');

update t1
set t1.col2_Q = 'Y'
from #result t1
where exists (select * from #test t2 
    where t2.ID = t1.ID and t2.col3 = t1.col3 and t2.col4 = t1.col4 and t2.col5 = t1.col5 
    and t2.col2 = 'Q');


select * from #result;
user_14679140
Related