CODEWITHSUNDEEP
sql-server
Sona
Sona

Reputation: 45

Concatenate multiples rows in the column while transposing dynamic columns into rows

The below is my table structure.

ScheduleDate    FirstName   ShiftName
3/1/2019        Emp2        SHIFT A 
3/2/2019        Emp2        SHIFT A 
3/2/2019        Emp3        SHIFT A 
3/2/2019        Emp1        SHIFT A 
3/1/2019        Emp3        SHIFT B 
3/2/2019        Emp2        SHIFT B 
3/2/2019        Emp3        SHIFT B 
3/2/2019        Emp1        SHIFT B 
3/1/2019        Emp1        SHIFT C 
3/2/2019        Emp2        SHIFT C 
3/2/2019        Emp3        SHIFT C 
3/2/2019        Emp1        SHIFT C 
3/1/2019        Emp4        WEEKLY OFF
3/2/2019        Emp4        WEEKLY OFF

I need the result as 

FirstName   3/1/2019      3/2/2019
Emp1        SHIFT C       SHIFT A,SHIFT B ,SHIFT C 
Emp2        SHIFT A       SHIFT A,SHIFT B ,SHIFT C 
Emp3        SHIFT B       SHIFT A,SHIFT B ,SHIFT C 
Emp4        WEEKLY OFF    WEEKLY OFF

I need to convert dynamic rows into columns as each month dates would be different. An employee would be having multiple shifts in a day. So I need multiple shifts to be displayed on each day separated by comma, if available.

Here is what I have tried, the below is my SQL SERVER Query and its result

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

Select * Into 
    #Temp
    From 

(Select * from T_Test) as f
                        order by FirstName, ScheduleDate

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.ScheduleDate) 
            FROM  #Temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT FirstName, ' + @cols + ' from 
            (
                select FirstName
                    ,  ShiftNAme
                    , ScheduleDate
                from  #Temp 
           ) x
            pivot 
            (
                 max(ShiftName)
                for ScheduleDate in (' + @cols + ')
            ) p '

execute(@query)

The result is

FirstName   3/1/2019    3/2/2019
Emp1       SHIFT C      SHIFT C
Emp2       SHIFT A      SHIFT C 
Emp3       SHIFT B      SHIFT C
Emp4       WEEKLY OFF   WEEKLY OFF

But I need multiple shifts to be concatenate with the comma in the each day. Would someone help me to achieve this, appreciate your help.

Schema to create the table

CREATE TABLE [dbo].[T_Test](
    [ScheduleDate] [date] NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [ShiftName] [nvarchar](50) NOT NULL
) ON [PRIMARY]

Insert Query as well

Insert into [dbo].[T_Test] (ScheduleDate,FirstName,ShiftName) values 
 ('2019-3-1','Emp2','SHIFT A'),
 ('2019-3-2','Emp2','SHIFT A'),
 ('2019-3-2','Emp3','SHIFT A'),
 ('2019-3-2','Emp1','SHIFT A'),
 ('2019-3-1','Emp3','SHIFT B'),
 ('2019-3-2','Emp2','SHIFT B'),
 ('2019-3-2','Emp3','SHIFT B'),
 ('2019-3-2','Emp1','SHIFT B'),
 ('2019-3-1','Emp1','SHIFT C'),
 ('2019-3-2','Emp3','SHIFT C'),
 ('2019-3-2','Emp1','SHIFT C'),
 ('2019-3-2','Emp2','SHIFT C'),
 ('2019-3-1','Emp4','WEEKLY OFF'),
 ('2019-3-2','Emp4','WEEKLY OFF')

Note : Edit 1 : I have edited as the free plain text and given the table schema and query so that it would be handy to help me.Just create the table, insert the data and execute my query.

Upvotes: 1

Views: 63

Answers (1)

Thom A
Thom A

Reputation: 95554

Well, i just finished anyway; wow this is some awful/insane SQL:

 DECLARE @SQL nvarchar(MAX);
 SET @SQL = N'SELECT T.FirstName,' + NCHAR(10) +
            STUFF((SELECT CONCAT(',',NCHAR(10),N'       STUFF((SELECT CONCAT('','',ShiftName)' + NCHAR(10) +
                                               N'              FROM T_Test S' + NCHAR(10) +
                                               N'              WHERE S.Firstname = T.FirstName' + NCHAR(10) +
                                               N'                AND S.ScheduleDate = ' + QUOTENAME(CONVERT(varchar(8),T.ScheduleDate,112),N'''')) + NCHAR(10) +
                                               N'              FOR XML PATH(N''''),TYPE).value(''.'',''varchar(MAX)''),1,1,N'''') AS ' + QUOTENAME(ScheduleDate)
                   FROM dbo.T_Test T
                   GROUP BY T.ScheduleDate
                   FOR XML PATH(N''),TYPE).value('.','varchar(MAX)'),1,2,N'') + NCHAR(10) +
            N'FROM T_Test T' + NCHAR(10) + 
            N'GROUP BY T.FirstName' + NCHAR(10) +
            N'ORDER BY T.FirstName;';

PRINT @SQL
EXEC sp_executesql @SQL;

Upvotes: 1

Related Questions