在sql server中将行转换为列

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