CODEWITHSUNDEEP
sqlsql-serverpivot
Pete
Pete

Reputation: 171

Pivot table in SQL-Server 2008

I have a table (SQL Server 2008) with shipments, so "Stage 0" means "New shipment" and consecutive stages are diferent stages of shipment tracking.

I'm trying to pivot the following table:

CREATE TABLE TableName
    ([ID] int, [IDShip] int, [Date] datetime, [Stage] int, [Arg] int)
;

INSERT INTO TableName
    ([ID], [IDShip], [Date], [Stage], [Arg])
VALUES
    (1, 1, '2013-10-03 08:36:00', 0, Null),
    (2, 1, '2013-10-03 08:37:25', 1, 1),
    (3, 2, '2013-10-03 08:38:25', 0, Null),
    (4, 1, '2013-10-03 08:39:25', 2, 1),
    (5, 2, '2013-10-03 08:40:25', 1, 3)
;

("Arg" is ID of Stage0. Select * would be:)

ID IDShip Date                Stage   Arg
1  1      2013-10-03 08:36:00     0  Null
2  1      2013-10-03 08:37:25     1     1
3  2      2013-10-03 08:38:25     0  Null
4  1      2013-10-03 08:39:25     2     1
5  2      2013-10-03 08:40:25     1     3

into something like:

ID0 IDShip DateShipment         ID1 DateFirstStage      ID2 DateSecondStage
1   1      2013-10-03 08:36:00  2   2013-10-03 08:37:25 4   2013-10-03 08:39:25    
3   2      2013-10-03 08:38:25  5   2013-10-03 08:40:25

Any help? Thanks

Upvotes: 1

Views: 126

Answers (2)

Andriy M
Andriy M

Reputation: 77657

It appears you need to pivot more than one column at the same time. However, standard PIVOT syntax does not support multi-columnar pivoting. You could use it to pivot one of the columns, then, using those as look-up values, pull the other column values with a series of correlated subqueries.

My take on the approach was similar to @Szymon's, except I managed to avoid grouping in the outer query, although I made things more messy at other stages. Here is my attempt:

SELECT
  IDShip,
  ID0,
  ID1,
  ID2,
  DateShipment    = (SELECT Date FROM TableName WHERE ID = p.ID0),
  DateFirstStage  = (SELECT Date FROM TableName WHERE ID = p.ID1),
  DateSecondStage = (SELECT Date FROM TableName WHERE ID = p.ID2)
FROM (
  SELECT
    ID,
    IDShip,
    StageID = 'ID' + CAST(Stage AS varchar(10))
  FROM TableName
) AS s
PIVOT (
  MAX(ID) FOR StageID IN (ID0, ID1, ID2)
) AS p
;

With proper indexing, it may not be too bad, although you could also try this alternative, which uses the older pivoting technique of grouping with conditional aggregation:

SELECT
  IDShip,
  ID0 = MAX(CASE Stage WHEN 0 THEN ID END),
  ID1 = MAX(CASE Stage WHEN 1 THEN ID END),
  ID2 = MAX(CASE Stage WHEN 2 THEN ID END),
  DateShipment    = MAX(CASE Stage WHEN 0 THEN Date END),
  DateFirstStage  = MAX(CASE Stage WHEN 1 THEN Date END),
  DateSecondStage = MAX(CASE Stage WHEN 2 THEN Date END)
FROM TableName
GROUP BY IDShip
;

This SQL Fiddle demo will let you try both solutions.

Upvotes: 0

Szymon
Szymon

Reputation: 43023

Turned out to be a bit more messy than I hoped for but here it is:

SELECT MAX([0]) AS ID0,
    IDShip,
    (SELECT [Date] FROM TableName T1 WHERE T1.ID = MAX([0]) AND T1.IDShip = Y.IDShip) AS DateShipment,
    MAX([1]) AS ID1,
    (SELECT [Date] FROM TableName T2 WHERE T2.ID = MAX([1]) AND T2.IDShip = Y.IDShip) AS DateFirstStage,
    MAX([2]) AS ID2,
    (SELECT [Date] FROM TableName T3 WHERE T3.ID = MAX([2]) AND T3.IDShip = Y.IDShip) AS DateSecondStage
FROM
    (SELECT * FROM TableName
    PIVOT (MAX([ID]) FOR Stage IN ([0], [1], [2])) AS X) Y
GROUP BY IDShip

You first pivot the table into ID's of 3 stages and then select each stage and its date.

Upvotes: 1

Related Questions