April 19, 2011 at 5:05 am
Hello,
i need to return data that it is stored in rows in a query converting this data in columns...
I mean:
I have table A with this columns: [item number], [product batch], [price]
Example Table A:
[font="Courier New"]
Item_1 - 1 - 5
Item_1 - 10 - 4.5
Item_2 - 1 - 7
Item_2 - 50 - 6.7
Item_2 - 100 - 6.5[/font]
And I need a query that return me data like this:
[font="Courier New"]
[Item Number] - [Product Batch_1] - [Price_1] - [Product Batch_2] - [Price_2] - [Product Batch_x] - [Price_x]
Item_1 - 1 - 5 - 10 - 4.5 - NULL - NULL
Item_2 - 1 - 7 - 50 - 6.7 - 100 - 6.5
[/font]
Can anyone help me? Thank you so much
April 19, 2011 at 6:36 am
Is there a fixed maximum for x ?
Far away is close at hand in the images of elsewhere.
Anon.
April 19, 2011 at 7:08 am
There is a maximum of 5.
There are items that may have 1 or 2, and others up to 5.
April 19, 2011 at 7:32 am
You will need to sequence the data in a temp table and then pivot
CREATE TABLE #A (RowID int IDENTITY(1,1),ColNum int,[item number] varchar(20), [product batch] int, [price] decimal(9,2))
INSERT #A (ColNum ,[item number], [product batch], [price])
SELECT 0 ,[item number], [product batch], [price]
FROM A
ORDER BY [item number] ASC, [product batch] ASC
UPDATE a
SET a.ColNum = a.RowID - b.MinID
FROM #A a
JOIN (SELECT a.[item number],MIN(a.RowID)-1 AS [MinID] FROM #A a GROUP BY a.[item number]) b
ON b.[item number] = a.[item number]
SELECT [item number],
MAX(CASE WHEN ColNum=1 THEN [product batch] ELSE NULL END) AS [Product Batch_1],
MAX(CASE WHEN ColNum=1 THEN [price] ELSE NULL END) AS [Price_1],
MAX(CASE WHEN ColNum=2 THEN [product batch] ELSE NULL END) AS [Product Batch_2],
MAX(CASE WHEN ColNum=2 THEN [price] ELSE NULL END) AS [Price_2],
MAX(CASE WHEN ColNum=3 THEN [product batch] ELSE NULL END) AS [Product Batch_3],
MAX(CASE WHEN ColNum=3 THEN [price] ELSE NULL END) AS [Price_3],
MAX(CASE WHEN ColNum=4 THEN [product batch] ELSE NULL END) AS [Product Batch_4],
MAX(CASE WHEN ColNum=4 THEN [price] ELSE NULL END) AS [Price_4],
MAX(CASE WHEN ColNum=5 THEN [product batch] ELSE NULL END) AS [Product Batch_5],
MAX(CASE WHEN ColNum=5 THEN [price] ELSE NULL END) AS [Price_5]
FROM #A
GROUP BY [item number]
ORDER BY [item number]
Far away is close at hand in the images of elsewhere.
Anon.
April 19, 2011 at 7:59 am
It works fine.
Thank you David for your help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply