CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlpivot
Sherwin
Sherwin

Reputation: 377

How to pivot this table

Supposed I have the following dataset

+--------------+-----------------------------+------------+-----+-----------+-----------+
| account_name |     PositionDescription     |  FullDate  | Day |  DayName  | ShiftType |
+--------------+-----------------------------+------------+-----+-----------+-----------+
| employee1    | Customer Support Specialist | 2012-11-01 |   1 | Thursday  | G         |
| employee1    | Customer Support Specialist | 2012-11-03 |   3 | Saturday  | G         |
| employee1    | Customer Support Specialist | 2012-11-05 |   5 | Monday    | G         |
| employee1    | Customer Support Specialist | 2012-11-06 |   6 | Tuesday   | G         |
| employee1    | Customer Support Specialist | 2012-11-08 |   8 | Thursday  | G         |
| employee1    | Customer Support Specialist | 2012-11-10 |  10 | Saturday  | G         |
| employee1    | Customer Support Specialist | 2012-11-12 |  12 | Monday    | G         |
| employee1    | Customer Support Specialist | 2012-11-13 |  13 | Tuesday   | G         |
| employee1    | Customer Support Specialist | 2012-11-15 |  15 | Thursday  | G         |
| employee1    | Customer Support Specialist | 2012-11-17 |  17 | Saturday  | G         |
| employee1    | Customer Support Specialist | 2012-11-19 |  19 | Monday    | G         |
| employee1    | Customer Support Specialist | 2012-11-20 |  20 | Tuesday   | G         |
| employee1    | Customer Support Specialist | 2012-11-22 |  22 | Thursday  | G         |
| employee1    | Customer Support Specialist | 2012-11-24 |  24 | Saturday  | G         |
| employee1    | Customer Support Specialist | 2012-11-26 |  26 | Monday    | G         |
| employee1    | Customer Support Specialist | 2012-11-27 |  27 | Tuesday   | G         |
| employee1    | Customer Support Specialist | 2012-11-29 |  29 | Thursday  | G         |
| employee2    | Game Support Specialist     | 2012-11-02 |   2 | Friday    | M         |
| employee2    | Game Support Specialist     | 2012-11-03 |   3 | Saturday  | M         |
| employee2    | Game Support Specialist     | 2012-11-04 |   4 | Sunday    | M         |
| employee2    | Game Support Specialist     | 2012-11-07 |   7 | Wednesday | M         |
| employee2    | Game Support Specialist     | 2012-11-09 |   9 | Friday    | M         |
| employee2    | Game Support Specialist     | 2012-11-10 |  10 | Saturday  | M         |
| employee2    | Game Support Specialist     | 2012-11-11 |  11 | Sunday    | M         |
| employee2    | Game Support Specialist     | 2012-11-14 |  14 | Wednesday | M         |
| employee2    | Game Support Specialist     | 2012-11-16 |  16 | Friday    | M         |
| employee2    | Game Support Specialist     | 2012-11-17 |  17 | Saturday  | M         |
| employee2    | Game Support Specialist     | 2012-11-18 |  18 | Sunday    | M         |
| employee2    | Game Support Specialist     | 2012-11-21 |  21 | Wednesday | M         |
| employee2    | Game Support Specialist     | 2012-11-23 |  23 | Friday    | M         |
| employee2    | Game Support Specialist     | 2012-11-24 |  24 | Saturday  | M         |
| employee2    | Game Support Specialist     | 2012-11-25 |  25 | Sunday    | M         |
| employee2    | Game Support Specialist     | 2012-11-28 |  28 | Wednesday | M         |
| employee2    | Game Support Specialist     | 2012-11-30 |  30 | Friday    | M         |
+--------------+-----------------------------+------------+-----+-----------+-----------+

Is it possible to format it as this table?

+--------------+-----------------------------+----------------+
| account_name |     PositionDescription     | 1 | 2 | 3 | 4  | and so on...
+--------------+-----------------------------+------------+---+
| employee1    | Customer Support Specialist | G | G | G | G  |
| employee2    | Game Support Specialist     | G | G | G | G  | 
+-----------------------------+------------+-----+------------+

I was trying to figure it out using PIVOT and I just can't get it. I don't know if it's possible or not :(

Supposed this is is the original query

SELECT   account_name               
                ,PositionDescription ,FullDate, Day, DayName ,ShiftType         
        FROM ManpowerSchedule ms

Upvotes: 1

Views: 259

Answers (2)

Taryn
Taryn

Reputation: 247720

I am adding an answer because the other is missing a few things in the syntax to get it to work properly. You can use the PIVOT function to get the results that you need. There are two ways to use PIVOT either a static version or a dynamic version.

In the static version, you will hard-code all of the values that need to be transformed:

select  * 
from  
( 
  select account_name, PositionDescription, ShiftType, day
  from ManpowerSchedule 
) src
pivot   
( 
  max(ShiftType) 
  FOR day IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], .....) 
)  piv;

See SQL Fiddle with Demo

If you have an unknown number of columns that you need to transform, then you can use dynamic SQL:

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

select @cols = STUFF((SELECT ',' + QUOTENAME(day) 
                    from ManpowerSchedule
                    group by day
                    order by day
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsNull = STUFF((SELECT ', IsNull(' + QUOTENAME(day) +', '''') as '+QUOTENAME(day)
                    from ManpowerSchedule
                    group by day
                    order by day
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT account_name, PositionDescription, ' + @colsNull + ' 
              from 
              (
                select account_name, PositionDescription, ShiftType, day
                from ManpowerSchedule 
              ) x
              pivot 
              (
                max(ShiftType)
                for day in (' + @cols + ')
              ) p '

execute(@query)

See SQL Fiddle with Demo

If you do not have access to the PIVOT function, then this can be replicated using an aggregate function and a CASE statement:

select account_name, 
  PositionDescription,
  max(case when day = 1 then shifttype else '' end) [1],
  max(case when day = 2 then shifttype else '' end) [2],
  max(case when day = 3 then shifttype else '' end) [3],
  max(case when day = 4 then shifttype else '' end) [4],
  max(case when day = 5 then shifttype else '' end) [5],
  max(case when day = 6 then shifttype else '' end) [6],
  max(case when day = 7 then shifttype else '' end) [7],
  max(case when day = 8 then shifttype else '' end) [8],
  max(case when day = 9 then shifttype else '' end) [9],
  max(case when day = 10 then shifttype else '' end) [10]
from ManpowerSchedule
group by account_name, PositionDescription

See SQL Fiddle with Demo

All three versions will give you the same results.

Upvotes: 2

Royi Namir
Royi Namir

Reputation: 148534

SELECT  * 
FROM    ( select account_name ,PositionDescription  from ManpowerSchedule )
 PIVOT   ( max([ShiftType ]) FOR day IN ([1], [2],......) )  f;

Upvotes: 1

Related Questions