Pivot/Unpivot multiple columns

Question

How do I get the data:

OrderID | Ver   |   Res |    OI | Ret   |  Corp
-----------------------------------------------
      1 | 'BUD' | 57823 | 11111 | 34304 | 12344
      2 | 'ACT' | 12345 | 23456 | 45678 |  NULL

To

Ver1  |  Res1 |   OI1 |  Ret1 | Corp1 | Ver2  |  Res2 |   OI2 |  Ret2 | Corp2
-----------------------------------------------------------------------------
'BUD' | 57823 | 11111 | 34304 | 12344 | 'ACT' | 12345 | 23456 | 45678 |  NULL

Do I use multiple PIVOTS?!?!

Do you have variable number of rows in table? – Sateesh Pagolu Oct 20 '15 at 04:44 — Sateesh Pagolu, Oct 20 '15 at 04:44

score 2 · Answer 1 · answered Oct 20 '15 at 04:48

2

Without pivot it can be done by using this code :

SELECT 
MAX(CASE WHEN ORDERID=1 THEN VER END) VER1,
MAX(CASE WHEN ORDERID=1 THEN RES END) RES1,
MAX(CASE WHEN ORDERID=1 THEN OI END) OI1,
MAX(CASE WHEN ORDERID=1 THEN RET END )RET1,
MAX(CASE WHEN ORDERID=1 THEN CORP END) CORP1,

MAX(CASE WHEN ORDERID=2 THEN VER END )VER2,
MAX(CASE WHEN ORDERID=2 THEN RES END) RES2,
MAX(CASE WHEN ORDERID=2 THEN OI END) OI2,
MAX(CASE WHEN ORDERID=2 THEN RET END )RET2,
MAX(CASE WHEN ORDERID=2 THEN CORP END) CORP2

FROM yourtable

answered Oct 20 '15 at 04:48

NewSQL

181
5

As much as I am impressed with the solution above by @DarkKnight which I assume will cater for any number of OrderIDs I am happy to go with this solution as I didn't specify that there are only 2 possible OrderID's and I was writing this inside a UDF and I pass the 2 possible Ver ID's as 2 separate parameters, also I cannot use dynamic sql in a function. So thank you. – Glen Oct 20 '15 at 05:25

score 0 · Answer 2 · answered Oct 20 '15 at 04:50

Following query will work for any number of rows.

DECLARE @SQL NVARCHAR(MAX) 
WITH CTE
AS
(
SELECT 1 AS REF,Q,CONCAT(ITEM,ORDERID) AS COL FROM(
SELECT CAST(ORDERID AS VARCHAR(100)) AS ORDERID,
CAST(VER AS VARCHAR(100)) AS VER,
CAST(RES AS VARCHAR(100)) AS RES,
CAST(OI AS VARCHAR(100)) AS OI,
CAST(RET AS VARCHAR(100)) AS RET,
CAST(CORP AS VARCHAR(100)) AS CORP FROM ORDERS) K
UNPIVOT
(Q FOR ITEM IN (VER,RES,[OI],RET,CORP)) UP
)

SELECT @SQL = N'WITH CTE
AS
(
SELECT 1 AS REF,Q,CONCAT(ITEM,ORDERID) AS COL FROM(
SELECT CAST(ORDERID AS VARCHAR(100)) AS ORDERID,
CAST(VER AS VARCHAR(100)) AS VER,
CAST(RES AS VARCHAR(100)) AS RES,
CAST(OI AS VARCHAR(100)) AS OI,
CAST(RET AS VARCHAR(100)) AS RET,
CAST(CORP AS VARCHAR(100)) AS CORP FROM ORDERS) K
UNPIVOT
(Q FOR ITEM IN (VER,RES,[OI],RET,CORP)) UP
)
SELECT * FROM CTE
PIVOT
(MAX(Q) FOR COL IN ('+ STUFF((SELECT ','+COL FROM CTE FOR XML PATH('')),1,1,'') +')) P'

EXEC SP_EXECUTESQL @SQL

Pivot/Unpivot multiple columns

2 Answers2