Pivoting data to show unique values for specific columns-CodePudding

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

CodePudding user response：

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.