Pivoting a table in SQL Server

Question

Say I have the following excerpt of a table

Name    Dept        Shift   Time    Date        Section
------- ----------- ------- ------- ----------- -------
GAN     BREAKER     Day     8-10    2015-10-27  NULL
GAN     BREAKER     Day     10-12   2015-10-27  NULL
GAN     BREAKER     Day     12-2    2015-10-27  Stone
GAN     BREAKER     Day     2-4     2015-10-27  NULL
GAN     BREAKER     Day     4-6     2015-10-27  NULL
GAN     BREAKER     Day     6-8     2015-10-27  NULL
GAN     BREAKER     Night   8-10    2015-10-27  NULL
GAN     BREAKER     Night   10-12   2015-10-27  NULL
GAN     BREAKER     Night   12-2    2015-10-27  NULL
GAN     BREAKER     Night   2-4     2015-10-27  NULL
GAN     BREAKER     Night   4-6     2015-10-27  Wall
GAN     BREAKER     Night   6-8     2015-10-27  NULL

And I would like to do pivoting so that it would look like this:

                            2015-10-27
Name    Dept        Shift   8-10    10-12   12-2    2-4     4-6     6-8
------- ----------- ------- ------- ------- ------- ------- ------- ------
GAN     BREAKER     Day     NULL    NULL    Stone   NULL    NULL    NULL
GAN     BREAKER     Night   NULL    NULL    NULL    NULL    Wall    NULL

Or maybe something that is close to the above suggested pivot.

Can it be done in SQL Server? Can someone point me the correct query to this?

Thank you for your kind attention guys! :)

Answer 1

To be fair, this is a little lazy. I've never used SQL Server before and neither have I heard of PIVOT. In 5 minutes, I've learned enough to pass on to you for you to take over. Do some more research next time.

Yes, Google said that SQL Server has the PIVOT function. So...

SELECT * FROM Table1
PIVOT(
  MIN([section]) 
  FOR [time]
  IN ([8-10],[10-12],[12-2],[4-6],[6-8])
) AS shiftTimes

OUTPUT:

Name | Dept     | Shift    | Date         | 8-10   | 10-12   | 12-2   | 4-6    | 6-8
----------------------------------------------------------------------------------------
GAN  | BREAKER  | Day      | 2015-10-27   | (null) | (null)  | Stone  | (null) | (null)
GAN  | BREAKER  | Night    | 2015-10-27   | (null) | (null)  | (null) | Wall   | (null)

DEMO

...is very, very close to your desired output. Maybe with some tweaking and some research, you can find your perfect output. I could probably finish it with another 5, but I'll leave that for you.

Here I have assumed that your shift patterns (8-10) will always be the same and so I entered them manually. If they may change in the future, I would definitely consider using a dynamic pivot like Paul's answer.

The differences in his answer compared to mine are ease of maintenance and performance. My query is simple to read and runs at 0.38ms compared to his 0.5ms, based on the tiny dataset you gave us. But, it's not about that, it's about whether or not you need your new columns to be dynamically created. If you don't, use mine. If you do, use his.

Enjoy.

Answer 2

Try Dynamic Pivot,

CREATE TABLE #Your_Table
(
    NAME VARCHAR(10),
    DEPT VARCHAR(20),
    [SHIFT] VARCHAR(10),
    [TIME] VARCHAR(50),
    [DATE] DATE,
    SECTION VARCHAR(50)
)

INSERT INTO #Your_Table
VALUES      ('GAN','BREAKER','Day','8-10','2015-10-27',NULL),
            ('GAN','BREAKER','Day','10-12','2015-10-27',NULL),
            ('GAN','BREAKER','Day','12-2','2015-10-27','Stone'),
            ('GAN','BREAKER','Day','2-4','2015-10-27',NULL),
            ('GAN','BREAKER','Day','4-6','2015-10-27',NULL),
            ('GAN','BREAKER','Day','6-8','2015-10-27',NULL),
            ('GAN','BREAKER','Night','8-10','2015-10-27',NULL),
            ('GAN','BREAKER','Night','10-12','2015-10-27',NULL),
            ('GAN','BREAKER','Night','12-2','2015-10-27',NULL),
            ('GAN','BREAKER','Night','2-4','2015-10-27',NULL),
            ('GAN','BREAKER','Night','4-6','2015-10-27','Wall'),
            ('GAN','BREAKER','Night','6-8','2015-10-27',NULL)

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName = ISNULL(@ColumnName + ',', '')
                     + Quotename([TIME])
FROM   (SELECT DISTINCT [TIME]
        FROM   #Your_Table) AS Courses
--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = N'SELECT Name, Dept,[Shift], '
                         + @ColumnName + 'FROM #Your_Table PIVOT(MAX(SECTION) FOR [TIME]
          IN ('+ @ColumnName + ')) AS PVTTable'

--Execute the Dynamic Pivot Query
EXEC SP_EXECUTESQL
  @DynamicPivotQuery 

SQLFIDDLE DEMO