Pivoting in TSQL

Question

I have raw data that looks like this

I want to pivot it for a report to look like this

How do I pivot it like this in TSQL where the Starttimes is in order from left to right?

Here is the TSQL to generate the data

Declare @ProcessHistory Table ( Id Int, ProcessId Int, LocationId Int, StartTime DateTime, EndTime DateTime, Duration Time (0) )
Insert Into @ProcessHistory Values
(2, 13, 0, '2012-01-27 12:23:35.373', '2012-01-27 12:23:35.963', '00:00:00'),
(3, 13, 10, '2012-01-27 12:23:35.373', '2012-01-27 12:23:35.633', '00:00:00'),
(4, 13, 20, '2012-01-27 12:23:35.633', '2012-01-27 12:23:35.633', '00:00:00'),
(5, 13, 30, '2012-01-27 12:23:35.633', '2012-01-27 12:23:35.657', '00:00:00'),
(6, 13, 40, '2012-01-27 12:23:35.657', '2012-01-27 12:23:35.847', '00:00:00'),
(7, 13, 50, '2012-01-27 12:23:35.847', '2012-01-27 12:23:35.950', '00:00:00'),
(8, 13, 60, '2012-01-27 12:23:35.950', '2012-01-27 12:23:35.963', '00:00:00'),
(218, 13, 0, '2012-01-27 22:05:34.650', '2012-01-27 22:05:37.297', '00:00:03'),
(219, 13, 10, '2012-01-27 22:05:34.653', '2012-01-27 22:05:35.453', '00:00:01'),
(220, 13, 20, '2012-01-27 22:05:35.457', '2012-01-27 22:05:35.463', '00:00:00'),
(221, 13, 30, '2012-01-27 22:05:35.467', '2012-01-27 22:05:35.570', '00:00:00'),
(222, 13, 40, '2012-01-27 22:05:35.570', '2012-01-27 22:05:35.620', '00:00:00'),
(223, 13, 50, '2012-01-27 22:05:35.620', '2012-01-27 22:05:37.280', '00:00:02'),
(224, 13, 60, '2012-01-27 22:05:37.283', '2012-01-27 22:05:37.293', '00:00:00'),
(434, 13, 0, '2012-01-29 04:35:32.370', '2012-01-29 04:35:36.913', '00:00:04'),
(435, 13, 10, '2012-01-29 04:35:32.400', '2012-01-29 04:35:33.817', '00:00:01'),
(436, 13, 20, '2012-01-29 04:35:33.883', '2012-01-29 04:35:33.933', '00:00:00'),
(437, 13, 30, '2012-01-29 04:35:33.933', '2012-01-29 04:35:34.153', '00:00:01'),
(438, 13, 40, '2012-01-29 04:35:34.180', '2012-01-29 04:35:34.660', '00:00:00'),
(439, 13, 50, '2012-01-29 04:35:34.727', '2012-01-29 04:35:36.700', '00:00:02'),
(440, 13, 60, '2012-01-29 04:35:36.763', '2012-01-29 04:35:36.853', '00:00:00'),
(650, 13, 0, '2012-01-29 22:19:18.297', '2012-01-29 22:19:22.103', '00:00:04'),
(651, 13, 10, '2012-01-29 22:19:18.297', '2012-01-29 22:19:19.450', '00:00:01'),
(652, 13, 20, '2012-01-29 22:19:19.453', '2012-01-29 22:19:19.473', '00:00:00'),
(653, 13, 30, '2012-01-29 22:19:19.473', '2012-01-29 22:19:19.493', '00:00:00'),
(654, 13, 40, '2012-01-29 22:19:19.493', '2012-01-29 22:19:19.537', '00:00:00'),
(655, 13, 50, '2012-01-29 22:19:19.537', '2012-01-29 22:19:22.073', '00:00:03'),
(656, 13, 60, '2012-01-29 22:19:22.073', '2012-01-29 22:19:22.083', '00:00:00'),
(866, 13, 0, '2012-01-30 11:09:21.437', '2012-01-30 11:09:24.163', '00:00:03'),
(867, 13, 10, '2012-01-30 11:09:21.437', '2012-01-30 11:09:22.437', '00:00:01'),
(868, 13, 20, '2012-01-30 11:09:22.440', '2012-01-30 11:09:22.443', '00:00:00'),
(869, 13, 30, '2012-01-30 11:09:22.447', '2012-01-30 11:09:22.490', '00:00:00'),
(870, 13, 40, '2012-01-30 11:09:22.490', '2012-01-30 11:09:22.670', '00:00:00'),
(871, 13, 50, '2012-01-30 11:09:22.670', '2012-01-30 11:09:24.150', '00:00:02'),
(872, 13, 60, '2012-01-30 11:09:24.150', '2012-01-30 11:09:24.163', '00:00:00'),
(1038, 13, 0, '2012-01-30 23:12:51.240', '2012-01-30 23:12:54.837', '00:00:03'),
(1039, 13, 10, '2012-01-30 23:12:51.240', '2012-01-30 23:12:52.663', '00:00:01'),
(1040, 13, 20, '2012-01-30 23:12:52.667', '2012-01-30 23:12:52.680', '00:00:00'),
(1041, 13, 30, '2012-01-30 23:12:52.683', '2012-01-30 23:12:52.733', '00:00:00'),
(1042, 13, 40, '2012-01-30 23:12:52.733', '2012-01-30 23:12:53.003', '00:00:01'),
(1043, 13, 50, '2012-01-30 23:12:53.003', '2012-01-30 23:12:54.813', '00:00:01'),
(1044, 13, 60, '2012-01-30 23:12:54.817', '2012-01-30 23:12:54.833', '00:00:00'),
(1254, 13, 0, '2012-01-31 23:08:30.760', '2012-01-31 23:08:33.787', '00:00:03'),
(1255, 13, 10, '2012-01-31 23:08:30.763', '2012-01-31 23:08:31.733', '00:00:01'),
(1256, 13, 20, '2012-01-31 23:08:31.737', '2012-01-31 23:08:31.743', '00:00:00'),
(1257, 13, 30, '2012-01-31 23:08:31.743', '2012-01-31 23:08:31.800', '00:00:00'),
(1258, 13, 40, '2012-01-31 23:08:31.800', '2012-01-31 23:08:31.940', '00:00:00'),
(1259, 13, 50, '2012-01-31 23:08:31.943', '2012-01-31 23:08:33.627', '00:00:02'),
(1260, 13, 60, '2012-01-31 23:08:33.627', '2012-01-31 23:08:33.783', '00:00:00'),
(1470, 13, 0, '2012-02-02 04:03:14.497', '2012-02-02 04:03:17.323', '00:00:03'),
(1471, 13, 10, '2012-02-02 04:03:14.497', '2012-02-02 04:03:15.257', '00:00:01'),
(1472, 13, 20, '2012-02-02 04:03:15.257', '2012-02-02 04:03:15.263', '00:00:00'),
(1473, 13, 30, '2012-02-02 04:03:15.267', '2012-02-02 04:03:15.360', '00:00:00'),
(1474, 13, 40, '2012-02-02 04:03:15.360', '2012-02-02 04:03:15.637', '00:00:00'),
(1475, 13, 50, '2012-02-02 04:03:15.640', '2012-02-02 04:03:17.310', '00:00:02'),
(1476, 13, 60, '2012-02-02 04:03:17.310', '2012-02-02 04:03:17.320', '00:00:00'),
(1686, 13, 0, '2012-02-02 10:30:32.460', '2012-02-02 10:30:35.197', '00:00:03'),
(1687, 13, 10, '2012-02-02 10:30:32.463', '2012-02-02 10:30:33.487', '00:00:01'),
(1688, 13, 20, '2012-02-02 10:30:33.490', '2012-02-02 10:30:33.493', '00:00:00'),
(1689, 13, 30, '2012-02-02 10:30:33.497', '2012-02-02 10:30:33.517', '00:00:00'),
(1690, 13, 40, '2012-02-02 10:30:33.520', '2012-02-02 10:30:33.547', '00:00:00'),
(1691, 13, 50, '2012-02-02 10:30:33.550', '2012-02-02 10:30:35.180', '00:00:02'),
(1692, 13, 60, '2012-02-02 10:30:35.180', '2012-02-02 10:30:35.190', '00:00:00'),
(1858, 13, 0, '2012-02-02 22:20:36.840', '2012-02-02 22:20:39.997', '00:00:03'),
(1859, 13, 10, '2012-02-02 22:20:36.843', '2012-02-02 22:20:37.853', '00:00:01'),
(1860, 13, 20, '2012-02-02 22:20:37.857', '2012-02-02 22:20:37.863', '00:00:00'),
(1861, 13, 30, '2012-02-02 22:20:37.863', '2012-02-02 22:20:37.930', '00:00:00'),
(1862, 13, 40, '2012-02-02 22:20:37.933', '2012-02-02 22:20:38.317', '00:00:01'),
(1863, 13, 50, '2012-02-02 22:20:38.320', '2012-02-02 22:20:39.980', '00:00:01'),
(1864, 13, 60, '2012-02-02 22:20:39.983', '2012-02-02 22:20:39.993', '00:00:00'),
(2030, 13, 0, '2012-02-03 03:45:27.820', '2012-02-03 03:45:30.723', '00:00:03'),
(2031, 13, 10, '2012-02-03 03:45:27.820', '2012-02-03 03:45:28.880', '00:00:01'),
(2032, 13, 20, '2012-02-03 03:45:28.880', '2012-02-03 03:45:28.887', '00:00:00'),
(2033, 13, 30, '2012-02-03 03:45:28.887', '2012-02-03 03:45:28.923', '00:00:00'),
(2034, 13, 40, '2012-02-03 03:45:28.923', '2012-02-03 03:45:29.123', '00:00:01'),
(2035, 13, 50, '2012-02-03 03:45:29.127', '2012-02-03 03:45:30.707', '00:00:01'),
(2036, 13, 60, '2012-02-03 03:45:30.707', '2012-02-03 03:45:30.720', '00:00:00'),
(2246, 13, 0, '2012-02-06 10:25:28.507', '2012-02-06 10:25:31.693', '00:00:03'),
(2247, 13, 10, '2012-02-06 10:25:28.507', '2012-02-06 10:25:29.503', '00:00:01'),
(2248, 13, 20, '2012-02-06 10:25:29.503', '2012-02-06 10:25:29.523', '00:00:00'),
(2249, 13, 30, '2012-02-06 10:25:29.523', '2012-02-06 10:25:29.570', '00:00:00'),
(2250, 13, 40, '2012-02-06 10:25:29.570', '2012-02-06 10:25:29.810', '00:00:00'),
(2251, 13, 50, '2012-02-06 10:25:29.810', '2012-02-06 10:25:31.667', '00:00:02'),
(2252, 13, 60, '2012-02-06 10:25:31.667', '2012-02-06 10:25:31.690', '00:00:00'),
(2506, 13, 0, '2012-02-06 22:15:44.787', '2012-02-06 22:15:47.633', '00:00:03'),
(2507, 13, 10, '2012-02-06 22:15:44.787', '2012-02-06 22:15:45.913', '00:00:01'),
(2508, 13, 20, '2012-02-06 22:15:45.913', '2012-02-06 22:15:45.920', '00:00:00'),
(2509, 13, 30, '2012-02-06 22:15:45.923', '2012-02-06 22:15:45.967', '00:00:00'),
(2510, 13, 40, '2012-02-06 22:15:45.970', '2012-02-06 22:15:46.007', '00:00:01'),
(2511, 13, 50, '2012-02-06 22:15:46.007', '2012-02-06 22:15:47.610', '00:00:01'),
(2512, 13, 60, '2012-02-06 22:15:47.610', '2012-02-06 22:15:47.620', '00:00:00'),
(2678, 13, 0, '2012-02-07 03:44:24.393', '2012-02-07 03:44:27.510', '00:00:03'),
(2679, 13, 10, '2012-02-07 03:44:24.393', '2012-02-07 03:44:25.670', '00:00:01'),
(2680, 13, 20, '2012-02-07 03:44:25.673', '2012-02-07 03:44:25.680', '00:00:00'),
(2681, 13, 30, '2012-02-07 03:44:25.680', '2012-02-07 03:44:25.750', '00:00:00'),
(2682, 13, 40, '2012-02-07 03:44:25.753', '2012-02-07 03:44:25.990', '00:00:00'),
(2683, 13, 50, '2012-02-07 03:44:25.993', '2012-02-07 03:44:27.497', '00:00:02'),
(2684, 13, 60, '2012-02-07 03:44:27.500', '2012-02-07 03:44:27.510', '00:00:00'),
(2894, 13, 0, '2012-02-07 10:29:23.753', '2012-02-07 10:29:26.587', '00:00:03'),
(2895, 13, 10, '2012-02-07 10:29:23.753', '2012-02-07 10:29:24.700', '00:00:01'),
(2896, 13, 20, '2012-02-07 10:29:24.700', '2012-02-07 10:29:24.710', '00:00:00'),
(2897, 13, 30, '2012-02-07 10:29:24.710', '2012-02-07 10:29:24.733', '00:00:00'),
(2898, 13, 40, '2012-02-07 10:29:24.733', '2012-02-07 10:29:24.760', '00:00:00'),
(2899, 13, 50, '2012-02-07 10:29:24.760', '2012-02-07 10:29:26.540', '00:00:02'),
(2900, 13, 60, '2012-02-07 10:29:26.540', '2012-02-07 10:29:26.560', '00:00:00')

Select * From @ProcessHistory

Answer 1

Maybe something like this:

The test data:

CREATE TABLE ProcessHistory ( Id Int, ProcessId Int, LocationId Int, StartTime DateTime, EndTime DateTime, Duration Time (0) )
Insert Into ProcessHistory Values
(2, 13, 0, '2012-01-27 12:23:35.373', '2012-01-27 12:23:35.963', '00:00:00'),
..............................

Getting the unique columns:

DECLARE @cols VARCHAR(MAX)
;WITH CTE AS
(
    SELECT 
        convert(varchar, tbl.StartTime, 101)+' '+LEFT(convert(varchar, tbl.StartTime, 114),5) AS StartTime
    FROM 
        ProcessHistory AS tbl
), 
CTE2 AS
(
    SELECT
        ROW_Number() OVER(PARTITION BY CTE.StartTime ORDER BY CTE.StartTime DESC) AS RowNbr,
        CTE.StartTime
    FROM
        CTE
)
SELECT
    @cols = COALESCE(@cols + ','+QUOTENAME(StartTime),
                 QUOTENAME(StartTime))
FROM 
    CTE2
WHERE
    CTE2.RowNbr=1

Executing the dynamic sql (I have chosen to sum the duration in seconds):

DECLARE @query NVARCHAR(4000)=
N'SELECT
    *
FROM
(
    SELECT
        tbl.ProcessId,
        tbl.LocationId,
        DATEDIFF(s, 0, tbl.Duration) AS Duration,
        convert(varchar, tbl.StartTime, 101)+'' ''+LEFT(convert(varchar, tbl.StartTime, 114),5) AS StartTime
    FROM
        ProcessHistory AS tbl
) AS P
PIVOT
(
    SUM(Duration)
    FOR StartTime IN('+@cols+')
) AS Pvt'

EXECUTE(@query)

Then in my case I'am dropping the table:

DROP TABLE ProcessHistory

Answer 2

Ok, there is a lot to take into account with your question. You need dynamic SQL, so you really should take a look at this link first. Also, your Duration column is of data type TIME, so its not easy to SUM, there is a lot of CAST and CONVERT to do. Once that I said that, I tried this query and worked fine (looking at your expected results):

DECLARE @Dates NVARCHAR(MAX)='', @Q NVARCHAR(MAX), @FormatedDates NVARCHAR(MAX) = ''

SELECT  @Dates = @Dates + '[' + CONVERT(VARCHAR(16),StartTime,120) + '],',
        @FormatedDates = @FormatedDates + 'CONVERT(TIME(0),CAST([' + CONVERT(VARCHAR(16),StartTime,120) +'] AS DATETIME)) AS [' + CONVERT(VARCHAR(16),StartTime,120) +'],'
FROM ProcessHistory
GROUP BY CONVERT(VARCHAR(16),StartTime,120)

SELECT  @Dates = LEFT(@Dates,LEN(@Dates)-1),
        @FormatedDates = LEFT(@FormatedDates,LEN(@FormatedDates)-1)

SET @Q = '
SELECT ProcessId, LocationId, '+@FormatedDates+'
FROM (  SELECT  ProcessId, LocationId, CONVERT(VARCHAR(16),StartTime,120) StartTime, 
                CAST(CONVERT(DATETIME,Duration) AS FLOAT) Duration
        FROM ProcessHistory) A
PIVOT (SUM(Duration) FOR StartTime IN ('+@Dates+')) PT'


EXEC sp_executesql @Q

Answer 3

As @Norla said, this is pretty nasty to do in SQL, but the below should get you there (totally untested, by the way, so may have a few gremlins):

First, you need to construct a list of column names (which is the unique values in your starttime column:

DECLARE @cols NVARCHAR(2000)
SELECT  @cols = COALESCE(@cols + ',[' + startTime+ ']',
                         '[' + startTime+ ']')
FROM    ProcessHistory
ORDER BY startTime

Then you need to feed this string into a dynamic SQL query:

DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT processId,locationId, '+@cols +'
               FROM
               (SELECT  t.processId,
                        t.locationId,
                        t.starttime,
                        t.duration
               FROM    ProcessHistory) t PIVOT ( SUM(duration)
                                                 FOR starttime IN ( '+@cols +' )
                                               ) AS pvt'

and then execute it with:

EXECUTE(@query)

Answer 4

In regards to the comments in the original question:

The easiest way to do this is going to be doing the pivot in Excel. SQL Server will require that you specify every single column in the pivot operation, which you'll have to do dynamically. (Which is awful!)

If you can get the results as shown above into an Excel sheet, then have a second sheet (in the same workbook) read from the first and perform the pivot, you're golden.