Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 PIVOT multiple columns

Author  Topic 

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2014-04-24 : 06:43:25
-- Here's a test table where I'm trying to workout how to Pivot more than one column.

-- Drop the Temp Table
IF (SELECT Object_id('tempdb..#Test_Pivot_Example')) <> 0
BEGIN
DROP TABLE #Test_Pivot_Example
END

-- Create Temp tables
CREATE TABLE [dbo].#Test_Pivot_Example
( [MyCompanyCode] [varchar](10) NULL,
[MyBrand] [varchar](10) NULL,
[Item No_] [varchar](20) NOT NULL,
[Colour] [varchar](30) NULL,
[Size] [varchar](10) NULL,
[Size Rank] [int] NULL,
[Barcode No_] [varchar](20) NULL,
[Current_Stock_Qty] [decimal](38, 20) NULL
)
;

INSERT INTO #Test_Pivot_Example
VALUES('Comp1','Brand2','PQ1254','BLACK','S','1','524568456545','2')
;
INSERT INTO #Test_Pivot_Example
VALUES('Comp1','Brand2','PQ1254','BLACK','M','2','524568456777','3')
;
INSERT INTO #Test_Pivot_Example
VALUES('Comp1','Brand2','PQ1254','BLACK','L','3','524568456999','10')
;
INSERT INTO #Test_Pivot_Example
VALUES('Comp1','Brand2','PQ1254','RED','S','1','524568456744','4')
;
INSERT INTO #Test_Pivot_Example
VALUES('Comp1','Brand2','PQ1254','RED','M','2','524568456000','-1')
;
INSERT INTO #Test_Pivot_Example
VALUES('Comp1','Brand2','PQ1254','RED','L','3','524568456012','5')
;
INSERT INTO #Test_Pivot_Example
VALUES('Comp2','Brand2','RS4587','GREEN','8','1','524568456888','32')
;
INSERT INTO #Test_Pivot_Example
VALUES('Comp2','Brand2','RS4587','GREEN','10','2','524568456956','108')
;
INSERT INTO #Test_Pivot_Example
VALUES('Comp2','Brand2','RS4587','GREEN','12','3','524568456648','99')
;
INSERT INTO #Test_Pivot_Example
VALUES('Comp2','Brand2','RS4587','GREEN','14','4','524568456857','14')
;
INSERT INTO #Test_Pivot_Example
VALUES('Comp2','Brand1','HI6588','GREEN','STD','4','52456787568','77')
;

-- This Pivots OK
SELECT * FROM
(
SELECT [Item No_],[Colour],[Size Rank],[Current_Stock_Qty]
FROM #Test_Pivot_Example
)t

PIVOT (SUM([Current_Stock_Qty]) FOR [Size Rank]
IN ([1],[2],[3],[4],[5])) AS pvt


-- But I need to Pivot the Size, Barcode and Current Stock Quantity so Item No_ and Colour are on one line

SELECT * FROM
(
SELECT [Item No_],[Colour],[Size Rank],[Size],[Barcode No_],[Current_Stock_Qty]
FROM #Test_Pivot_Example
)t

PIVOT (SUM([Current_Stock_Qty]) FOR [Size Rank]
IN ([1],[2],[3],[4],[5])) AS pvt

Once I have worked this out then I need to dynamic populate the IN ([1] etc with the val;ue sin field [SIZE])

but one step at a time trying to workout pivot on more than one column.

Any ideas?

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2014-04-24 : 06:52:03
I'm trying to maybe do something like this, but sure if I'm heading in the right direction:

SELECT * FROM
(
SELECT [Item No_],[Colour],[Size Rank],[Size],[Barcode No_],[Current_Stock_Qty]
FROM #Test_Pivot_Example
)t

PIVOT (SUM([Current_Stock_Qty]) FOR [Size Rank]
IN ([1],[2],[3],[4],[5])) AS pvt1

PIVOT (MAX([Size]) FOR [Size Rank]
IN ([1],[2],[3],[4],[5])) AS pvt2

PIVOT (MAX([Barcode No_]) FOR [Size Rank]
IN ([1],[2],[3],[4],[5])) AS pvt3
Go to Top of Page

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2014-04-24 : 07:04:08
I suppose I could use a CASE and GROUP BY to get the result, but problem here is I don't always know what is in the Size column i.e the MAX number:



SELECT [Item No_]
,[Colour]

,MAX(CASE WHEN [Size Rank] = 1 THEN [Size] END) AS [Size1]
,MAX(CASE WHEN [Size Rank] = 1 THEN [Barcode No_] END) AS [Barcode No_1]
,MAX(CASE WHEN [Size Rank] = 1 THEN [Current_Stock_Qty] END) AS [Current_Stock_Qty1]

,MAX(CASE WHEN [Size Rank] = 2 THEN [Size] END) AS [Size2]
,MAX(CASE WHEN [Size Rank] = 2 THEN [Barcode No_] END) AS [Barcode No_2]
,MAX(CASE WHEN [Size Rank] = 2 THEN [Current_Stock_Qty] END) AS [Current_Stock_Qty2]

,MAX(CASE WHEN [Size Rank] = 3 THEN [Size] END) AS [Size3]
,MAX(CASE WHEN [Size Rank] = 3 THEN [Barcode No_] END) AS [Barcode No_3]
,MAX(CASE WHEN [Size Rank] = 3 THEN [Current_Stock_Qty] END) AS [Current_Stock_Qty3]

,MAX(CASE WHEN [Size Rank] = 4 THEN [Size] END) AS [Size4]
,MAX(CASE WHEN [Size Rank] = 4 THEN [Barcode No_] END) AS [Barcode No_4]
,MAX(CASE WHEN [Size Rank] = 4 THEN [Current_Stock_Qty] END) AS [Current_Stock_Qty4]

,MAX(CASE WHEN [Size Rank] = 5 THEN [Size] END) AS [Size5]
,MAX(CASE WHEN [Size Rank] = 5 THEN [Barcode No_] END) AS [Barcode No_5]
,MAX(CASE WHEN [Size Rank] = 5 THEN [Current_Stock_Qty] END) AS [Current_Stock_Qty5]


FROM #Test_Pivot_Example

GROUP BY [Item No_]
,[Colour]
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-24 : 08:15:32
Have a look here:

https://stackoverflow.com/questions/18023479/sql-server-pivot-multiple-columns-based-on-one-column

there are probably some things you can use.
Go to Top of Page

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2014-04-24 : 08:40:03
Great thanks gbritton I'll take a look.
Go to Top of Page
   

- Advertisement -