SQL convert row values to column headers

Question

I have the following table:

tableA
+-----------+--------+
| tableA_id |  code  |
+-----------+--------+
|         1 | code A |
|         2 | code B |
|         3 | code A |
|         3 | code C |
|         3 | code B |
|         4 | code A |
|         4 | code C |
|         4 | code B |
|         5 | code A |
|         5 | code C |
|         5 | code B |
+-----------+--------+

I want to use a query to display code A, code B, code C as the column headers and then the values would display whether or not the tableA_id entry contains that code in the code field. So something like this:

+-----------+------------------------------+
| tableA_id |  code A |  code B  |  code C |
+-----------+------------------------------+
|         1 |   yes   |          |         |
|         2 |         |   yes    |   yes   |
|         3 |   yes   |   yes    |   yes   |

etc...

Can you do this in SQL?

No you can't ;) There is nothing like `PIVOT/CROSSTAB/SELF JOIN/CONDITIONAL AGGREGATION`. — Lukasz Szozda, Jan 07 '16 at 12:48
I removed the extraneous database tags. Feel free to add back the tag(s) for the database you are actually using. — Gordon Linoff, Jan 07 '16 at 12:50
I have to admit one thing. When I see `Can you do this in SQL?` or `Is it possible with SQL?` I treat it as challange accepted . — Lukasz Szozda, Jan 07 '16 at 13:02

Lukasz Szozda · Accepted Answer · 2016-01-07T12:57:51.560

Using condtitional aggregation(portable between different dialects):

SELECT tableA_id,
       MAX(CASE WHEN code ='code A' THEN 'yes' END) AS "code A",
       MAX(CASE WHEN code ='code B' THEN 'yes' END) AS "code B",
       MAX(CASE WHEN code ='code C' THEN 'yes' END) AS "code C"
FROM tableA
GROUP BY tableA_id;

SqlFiddleDemo_MySQL SqlFiddleDemo_Postgresql

Output:

╔════════════╦═════════╦═════════╦════════╗
║ tableA_id  ║ code A  ║ code B  ║ code C ║
╠════════════╬═════════╬═════════╬════════╣
║         1  ║ yes     ║ (null)  ║ (null) ║
║         2  ║ (null)  ║ yes     ║ (null) ║
║         3  ║ yes     ║ yes     ║ yes    ║
║         4  ║ yes     ║ yes     ║ yes    ║
║         5  ║ yes     ║ yes     ║ yes    ║
╚════════════╩═════════╩═════════╩════════╝

There are many possibilities(search for):

PIVOT            -> SQL Server/Oracle
CROSSTAB         -> Postgresql
SELF OUTER JOIN  -> All
CONDITIONAL AGG  -> All
...

Do mysql and postgres have the same syntax for this? Also what is the point of MAX? — user740521, Jan 07 '16 at 12:57
@user740521 It will work on MySQL/PostgreSQL/Oracle. Conditional aggregation is multiplatform. MAX() is needed to satisfy `GROUP BY`. — Lukasz Szozda, Jan 07 '16 at 12:59

score 0 · Answer 2 · answered Jan 07 '16 at 13:02

0

You Need something like this in SQL Syntax

Select *
From Table1
pivot ( Aggregate function For Column Name in ([CodeA], [CodeB] , [CodeC])) as PivotTable

answered Jan 07 '16 at 13:02

Abdul Hannan Ijaz

794
6
11

1

Good luck in MySQL/Postgresql. Also quoting with `[]` is SQL Server specific – Lukasz Szozda Jan 07 '16 at 13:02

score 0 · Answer 3 · answered Jan 07 '16 at 14:32

Or if you want a dynamic SQL statement you can try this bellow. You need to change only the name of the table and/or columns.

declare
cursor code is select code, rownum rn, count(*) over (partition by 1 ) as cnt from (select distinct code from test) order by 1;
build_Case clob;
a varchar2(100);
begin

for i in code loop
if i.rn <> i.cnt then
build_case := build_case || CHR(10) || ' max((case when code = ''' || i.code || ''' then ''yes'' else null end)) ' || i.code || ',';
else 
build_case := build_case || CHR(10) || ' max((case when code = ''' || i.code || ''' then ''yes'' else null end)) ' || i.code;
end if;
end loop;

build_case := 'select id,' || build_case || ' from test group by id';
dbms_output.put_line(build_Case);

--execute immediate build_Case;
end;
/

SQL convert row values to column headers

3 Answers3