SQL - Create a pivot-table like table filling columns with values from other columns

Question

I have a table like in SQL like:

Id flag_0 doc date
1  1      1   d1
1  1      2   d2
1  1      3   d3
2  0      1   d4
2  0      2   d5

I want a query to reach the following table:

Id flag_0 doc_1 doc_2 doc_3
1  1      d1     d2   d3 
2  0      d4     d5   nan (or missing)

Answer 1

You can pivot the given table in generalised sql query as in this demo

SELECT 
    Id, 
    flag_0,
    MAX(CASE WHEN doc = 1 THEN date END) AS doc_1,
    MAX(CASE WHEN doc = 2 THEN date END) AS doc_2,
    MAX(CASE WHEN doc = 3 THEN date END) AS doc_3
FROM 
    MyTable
GROUP BY 
    Id, flag_0
Order by Id;

This shall give you the output as :

Id  flag_0  doc_1   doc_2   doc_3
1   True    d1      d2      d3
2   False   d4      d5      null

if the date value do not exist; you can mark it as nan or missing in MYSQL using IFNULL function as in this demo :

SELECT 
    Id, 
    flag_0,
    IFNULL(MAX(CASE WHEN doc = 1 THEN date END), 'nan') AS doc_1,
    IFNULL(MAX(CASE WHEN doc = 2 THEN date END), 'nan') AS doc_2,
    IFNULL(MAX(CASE WHEN doc = 3 THEN date END), 'nan') AS doc_3
FROM 
    MyTable
GROUP BY 
    Id, flag_0;

Output :

Id  flag_0  doc_1   doc_2   doc_3
 1  True    d1      d2      d3
 2  False   d4      d5      nan

If you are using SQL-Server; You can write the query using PIVOT operator as in this demo :

SELECT 
    Id, 
    flag_0, 
    ISNULL([1], 'nan') AS doc_1, 
    ISNULL([2], 'nan') AS doc_2, 
    ISNULL([3], 'nan') AS doc_3
FROM 
    (SELECT Id, flag_0, doc, date FROM MyTable) AS SourceTable
PIVOT 
(
    MAX(date) FOR doc IN ([1], [2], [3])
) AS PivotTable
Order by Id;

Output :

Id  flag_0  doc_1   doc_2   doc_3
 1  True    d1      d2      d3
 2  False   d4      d5      nan

is there a way to create the columns doc automatically. I have about 40 different docs, My table has over 1M rows

The below solution shall work for you. Here is a demo.

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(doc) 
                    from MyTable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = 'SELECT Id, flag_0, ' + @cols + ' from 
            (
                select Id, flag_0, doc, date
                from MyTable
            ) x
            pivot 
            (
                MAX(date)
                for doc in (' + @cols + ')
            ) p 
            order by Id'

EXECUTE(@query);