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)
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);
