Convert Columns values into Rows in SQL 2008

Question

It would be great if you can help me to convert Column into Rows in SQL Table.

Let say, I have 3 columns called:

Employee_ID
Shift_Date
Shift_ID

Currently it come up like this:

Table Like this

I want to appear like that as the result - Result Table

But I want value in it, can you guys please help. Thanks.

This is a query I wrote but still error:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(shift_date) 
                    from dbo.tbl_Multi_Shift_Employee WHERE Shift_Date BETWEEN CONVERT(DATE, '01/02/2018 00:00:00',103) AND CONVERT(DATE, '28/02/2018 00:00:00',103)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT Employee_ID, total, Department_ID, shift_id, ' + @cols + ' from 
             (
                select count(*) over(partition by t.Employee_ID) total,
                  s.Department_ID,
                  t.Employee_ID,
                  t.shift_id
                from dbo.tbl_Multi_Shift_Employee t
                inner join dbo.tbl_department s
                  on t.Department_ID = s.Department_ID
            ) x
            pivot 
            (
                count(shift_id)
                for shift_id in (' + @cols + ')
            ) p '
PRINT @query
execute(@query)

Create your questions with text, not external links to pictures. — tukan, Mar 24 '18 at 16:20
That's called a "crosstab query". See for example https://stackoverflow.com/questions/15714265/i-need-to-know-how-to-create-a-crosstab-query#15714457 — Andomar, Mar 24 '18 at 16:24

score 0 · Answer 1 · answered Mar 27 '18 at 20:28

First you must to remove shift_id from primary select because is used as pivot. Second, change shift_id with shift_date that is the correct column you are using as pivot columns.

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(shift_date) 
                from dbo.tbl_Multi_Shift_Employee WHERE Shift_Date BETWEEN CONVERT(DATE, '01/02/2018 00:00:00',103) AND CONVERT(DATE, '28/02/2018 00:00:00',103)
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

set @query = 'SELECT Employee_ID, total, Department_ID, ' + @cols + ' from 
         (
            select count(*) over(partition by t.Employee_ID) total,
              s.Department_ID,
              t.Employee_ID,
              t.shift_id,
                t.shift_date
            from dbo.tbl_Multi_Shift_Employee t
            inner join dbo.tbl_department s
              on t.Department_ID = s.Department_ID
        ) x
        pivot 
        (
            count(shift_id)
            for shift_date in (' + @cols + ')
        ) p '
PRINT @query
execute(@query)

Convert Columns values into Rows in SQL 2008

1 Answers1