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 TableIF (SELECT Object_id('tempdb..#Test_Pivot_Example')) <> 0 BEGIN DROP TABLE #Test_Pivot_Example END-- Create Temp tablesCREATE 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_ExampleVALUES('Comp1','Brand2','PQ1254','BLACK','S','1','524568456545','2');INSERT INTO #Test_Pivot_ExampleVALUES('Comp1','Brand2','PQ1254','BLACK','M','2','524568456777','3');INSERT INTO #Test_Pivot_ExampleVALUES('Comp1','Brand2','PQ1254','BLACK','L','3','524568456999','10');INSERT INTO #Test_Pivot_ExampleVALUES('Comp1','Brand2','PQ1254','RED','S','1','524568456744','4');INSERT INTO #Test_Pivot_ExampleVALUES('Comp1','Brand2','PQ1254','RED','M','2','524568456000','-1');INSERT INTO #Test_Pivot_ExampleVALUES('Comp1','Brand2','PQ1254','RED','L','3','524568456012','5');INSERT INTO #Test_Pivot_ExampleVALUES('Comp2','Brand2','RS4587','GREEN','8','1','524568456888','32');INSERT INTO #Test_Pivot_ExampleVALUES('Comp2','Brand2','RS4587','GREEN','10','2','524568456956','108');INSERT INTO #Test_Pivot_ExampleVALUES('Comp2','Brand2','RS4587','GREEN','12','3','524568456648','99');INSERT INTO #Test_Pivot_ExampleVALUES('Comp2','Brand2','RS4587','GREEN','14','4','524568456857','14');INSERT INTO #Test_Pivot_ExampleVALUES('Comp2','Brand1','HI6588','GREEN','STD','4','52456787568','77');-- This Pivots OKSELECT * 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 lineSELECT * 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 pvt1PIVOT (MAX([Size]) FOR [Size Rank] IN ([1],[2],[3],[4],[5])) AS pvt2PIVOT (MAX([Barcode No_]) FOR [Size Rank] IN ([1],[2],[3],[4],[5])) AS pvt3 |
|
|
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_ExampleGROUP BY [Item No_] ,[Colour] |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
|
rogerclerkwell
Yak Posting Veteran
85 Posts |
Posted - 2014-04-24 : 08:40:03
|
Great thanks gbritton I'll take a look. |
|
|
|
|
|