Transform rows to columns in sql server

Question

This is my table returned after my sp executed:

ChildName  |      DATE        |      CLASS       | SERVING TIME

 Andrew        20.11.2019             1            Breakfast,Lunch,Snacks
 Andrew        21.11.2019             1            Breakfast,Lunch
 David           NULL                 1            NULL  
 Ijas          22.11.2019             1            Snacks            
 Kerry           NULL                 1            NULL  
 Paul            NULL                 1            NULL 
 Ram           22.11.2019             1            Snacks
 Ram           23.11.2019             1            Lunch

And I want the result like this

ChildName  |      20.11.2019         |      21.11.2019       |  22.11.2019   |  23.11.2019    |Class

Andrew      Breakfast,Lunch,Snacks        Breakfast,Lunch         NULL            NULL          1
David              NULL                       NULL                NULL            NULL          1   
Ijas               NULL                       NULL                Snacks          NULL          1
Kerry              NULL                       NULL                NULL            NULL          1
Paul               NULL                       NULL                NULL            NULL          1
Ram                NULL                       NULL                Snacks          Lunch         1

How can i build the result?

What have you tried till now? Please read [minimal-reproducible-example](https://stackoverflow.com/help/minimal-reproducible-example) and post an attempt. You can use PIVOT to achieve this. — ankyskywalker, Nov 28 '19 at 06:39
You can sure Pivot for this, refer this - https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — Mukesh Arora, Nov 28 '19 at 06:44
Is there 4 fixed dates in the input data set or more (undefined)? — mkRabbani, Nov 28 '19 at 07:21

score 1 · Answer 1 · answered Nov 28 '19 at 09:18

Creating a Temporary table to populate the sample data

IF OBJECT_ID('tempdb..#Table') IS NOT NULL
DROP TABLE #Table;

CREATE TABLE #Table (ChildName NVARCHAR(20), [DATE] NVARCHAR(20), CLASS NVARCHAR(20),[SERVING TIME] NVARCHAR(200))
DECLARE @Date AS NVARCHAR(MAX),
        @Query AS NVARCHAR(MAX)

INSERT INTO #Table 
VALUES
('Andrew','20.11.2019','1','Breakfast,Lunch,Snacks'),
('Andrew','21.11.2019','1','Breakfast,Lunch'),
('David',NULL,'1','NULL'),
('Ijas','22.11.2019','1','Snacks'),           
('Kerry',NULL,'1',NULL),
('Paul',NULL,'1',NULL),
('Ram','22.11.2019','1','Snacks'),
('Ram','23.11.2019','1','Lunch')

Assigning the DATE value to a variable in order to dynamically handle the DATE

SELECT @Date = STUFF((SELECT DISTINCT
                        ',' + QUOTENAME([DATE]) 
                      FROM 
                        #Table
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

Dynamic PIVOT Query to obtain the required result

    SET @Query= 'SELECT 
                *
            FROM 
            (
              SELECT 
                ChildName,[SERVING TIME],[DATE]
              FROM 
                #Table
            ) SRC
            PIVOT
            (
              MAX([SERVING TIME])
              FOR [DATE] IN ('+@Date+')
            ) PIV;'

EXEC sp_executesql @Query

OUTPUT : -

ChildName   20.11.2019              21.11.2019      22.11.2019  23.11.2019
Andrew      Breakfast,Lunch,Snacks  Breakfast,Lunch NULL        NULL
David       NULL                    NULL            NULL        NULL
Ijas        NULL                    NULL            Snacks      NULL
Kerry       NULL                    NULL            NULL        NULL
Paul        NULL                    NULL            NULL        NULL
Ram         NULL                    NULL            Snacks      Lunch

@Ijasibrahim If it worked, you can upvote and make it as answer. :) — Praveen ND, Nov 28 '19 at 10:37
@Ijasibrahim I couldn't understand the question regarding "ORDER BY". Can you explain? — Praveen ND, Nov 28 '19 at 10:40

Tohm · Accepted Answer · 2019-11-28T10:25:56.103

https://rextester.com/XIWYS42509

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

--DATA
CREATE TABLE #T  (ChildName nvarchar(50), DATE  nvarchar(50), CLASS int, [SERVING TIME] nvarchar(50))

INSERT INTO #T (ChildName, DATE , CLASS , [SERVING TIME])
VALUES ('Andrew',        '20.11.2019'    ,         1      ,      'Breakfast,Lunch,Snacks'),
 ('Andrew',        '21.11.2019'     ,        1     ,      'Breakfast,Lunch'),
 ('David',           NULL          ,       1       ,     NULL)  ,
 ('Ijas' ,         '22.11.2019'    ,         1     ,       'Snacks' )  ,         
 ('Kerry',           NULL          ,       1       ,     NULL ) ,
 ('Paul' ,           NULL          ,       1       ,     NULL ),
 ('Ram'  ,         '22.11.2019'    ,         1     ,       'Snacks'),
 ('Ram'  ,         '23.11.2019'    ,         1     ,       'Lunch' )

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

--PIVOT
SELECT @cols =  CASE WHEN @cols IS NULL THEN C 
                      ELSE @cols +',' + C
                END
                FROM (SELECT DISTINCT '[' + DATE + ']' as C FROM  #T WHERE DATE IS NOT NULL)t;


SELECT @query = N'
SELECT ChildName,'+@cols+N' , CLASS
FROM  
(SELECT ChildName, DATE , CLASS , [SERVING TIME]    FROM #T) AS SourceTable  
PIVOT  
(  
MAX([SERVING TIME])
FOR DATE IN ('+@cols+N')  
) AS PivotTable
ORDER BY ChildName
;'

--debug
PRINT @query
EXEC Sp_EXECUTESQL @query




DROP TABLE #T
GO

Add ORDER BY clause in dymamic query (between Pivotable and ';') — Tohm, Nov 28 '19 at 10:25

Transform rows to columns in sql server

This is my table returned after my sp executed:

And I want the result like this

2 Answers2