SQL Puzzle | The Pivot Puzzle – VII

In this puzzle you have to write a T-SQL which will transform the data into column format. If any column is missed then we have to generate that column.

Please check out the sample input values and sample expected output below.

Sample Input

ID Cau Times
939861018 E8889 0
939861018 E8889 1
939861954 E8889 0
939861988 E8788 5
939861772 E8889 0
939861772 E8889 4

Expected Output

Id 0 1 2 3 4 5 6
939861018 E8889 E8889
939861772 E8889       E8889
939861954 E8889
939861988           E8788

Script

Use below script to create table and insert sample data into it.

 ```-- CREATE TABLE Transform ( ID VARCHAR(100) ,Cau VARCHAR(100) ,Times INT ) GO INSERT INTO Transform VALUES ('939861018','E8889',0), ('939861018','E8889',1), ('939861954','E8889',0), ('939861988','E8788',5), ('939861772','E8889',0), ('939861772','E8889',4) GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

 ``` -- ;WITH SingleDigits(Number) AS ( SELECT Number FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (0)) AS X(Number) ) ,Series AS ( SELECT (d1.Number+1) + (10*d2.Number) Number from SingleDigits as d1, SingleDigits as d2 ) ,CTE AS ( SELECT Number - 1 Number FROM Series ) ,CTE1 AS ( SELECT t.Id,t.Cau,ISNULL(t.Times,s.Number) Times FROM CTE s LEFT JOIN Transform t ON S.Number = t.Times WHERE Number <= ( (SELECT MAX(Times) FROM Transform t ) ) AND Number >= 0 ) SELECT Id,ISNULL(,'') ,ISNULL(,'') ,ISNULL(,'') , ISNULL(,'') , ISNULL(,'') , ISNULL(,'') , ISNULL(,'')  FROM CTE1 PIVOT ( MIN(Cau) FOR [Times] IN (,,,,,,) )t WHERE Id IS NOT NULL -- ```