July 21, 2015 at 9:25 am
Hello to all gurus!
Having a bit of a conundrum and reaching out before I loose my mind. I have a table that has 1-n records of a foreign key. here's what I am working with:
declare @OrderItems table
(
ItemID int Primary Key Identity(1,1),
OrderID int,
ItemNumber varchar(20),
ItemCost decimal(13,2),
ItemQty int
)
insert @OrderItems
select 1,'320F',1.00,20
union
select 1,'322A',1.00,10
union
select 2,'A345-438',5.00,1
union
select 1,'F22RAPTOR',1500.00,20
union
select 2,'MIG22-BAZOOKA',25.00,2000
union
select 3,'320A',1.00,500
union
select 3,'320B',2.00,200
union
select 3,'320C',100.00,2000
union
select 3,'ACAGOLD', 10000.00, 10
select *
from @OrderItems
I'd like to get out something like this:
OrderID Item1 Item2 Item3 Item4
1 320F 322A F22RAPTOR NULL
2 A345-438 MIG22-BAZOOKA NULL NULL
3 320A 320B 320C ACAGOLD
As always, any help is highly appreciated and praised.
Kind regards,
🙂
July 21, 2015 at 9:51 am
You can use PIVOT or you can use cross tabs. I personally prefer cross tabs.
To do this correctly, you need to create a row number for each item in the order.
Here's an example:
WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY ItemID) rowID,
OrderID,
ItemNumber
FROM @OrderItems
)
SELECT OrderID,
MAX(CASE WHEN rowID = 1 THEN ItemNumber END) Item1,
MAX(CASE WHEN rowID = 2 THEN ItemNumber END) Item2,
MAX(CASE WHEN rowID = 3 THEN ItemNumber END) Item3,
MAX(CASE WHEN rowID = 4 THEN ItemNumber END) Item4
FROM CTE
GROUP BY OrderID;
Read more about Cross tabs and pivots in these articles:
July 23, 2015 at 2:50 pm
Thanks Luis! Very clean and precise, I like it. If I may take advantage of your disposition to help, I have instances when an OrderID will have a N items. If I am understanding your approach, I would have to "hard code" N times (max ItemID) into the case statement? My apologies if my question's dumb.
July 23, 2015 at 5:21 pm
johnnycash (7/23/2015)
Thanks Luis! Very clean and precise, I like it. If I may take advantage of your disposition to help, I have instances when an OrderID will have a N items. If I am understanding your approach, I would have to "hard code" N times (max ItemID) into the case statement? My apologies if my question's dumb.
There´s a way to make the code dynamic. If you read the second article in my previous post, you can get a good introduction to it. You might need as well a tally or numbers table[/url] to generate the dynamic code. I might come back with a solution, but please try to get one on your own.
July 24, 2015 at 8:04 am
I'm not sure if you still need it, but here's a way to make it dynamic. I changed the table variable into a permanent table because I didn't want to set up everything to use a table valued parameter.
DECLARE @SQL nvarchar(MAX);
WITH ItemsMax AS(
SELECT TOP 1 COUNT(*) items --We define the maximum number of columns
from OrderItems
GROUP BY OrderID
ORDER BY items DESC
)
SELECT @SQL = --This is the header part. It's static and copied directly from the original cross tab query.
'WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY ItemID) rowID,
OrderID,
ItemNumber
FROM OrderItems
)
SELECT OrderID'
+ ( --This is how we create the variable number of columns. The row numbers come from a Tally table.
--The concatenation method is explained in here: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
SELECT ' ,MAX(CASE WHEN rowID = ' + CAST(n AS varchar(7)) + ' THEN ItemNumber END) Item' + CAST(n AS varchar(7)) + CHAR(10)
FROM Tally t
JOIN ItemsMax im ON t.n <= im.items
ORDER BY t.n
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
+ --This is the footer. It could contain additional columns added after the variable columns and it should include the FROM and GROUP BY clauses.
'FROM CTE
GROUP BY OrderID;';
EXEC sp_executesql @SQL;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply