CODEWITHSUNDEEP
sqlsql-serverpivotssmsdistinct
BKB
BKB

Reputation: 145

Pivoting data to show unique values for specific columns

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

Upvotes: 3

Views: 123

Answers (2)

topsail
topsail

Reputation: 3130

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;

Upvotes: 0

forpas
forpas

Reputation: 164194

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.

Upvotes: 3

Related Questions