July 30, 2013 at 9:40 pm
I am trying to write a query using the table below - this table comes from a vendor supplied system, so I can't modify it:
Item_ID, Tag
ITEM1, Blue
ITEM1, Warm
ITEM2, Green
ITEM3, Coarse
ITEM2, Fine
There is a maximum of four Tag records for one Item. I want to get the result set below:
Item_ID, TAG1, TAG2, TAG3, TAG4
ITEM1, Blue, Warm, NULL, NULL
ITEM2, Green, Fine, NULL, NULL
ITEM3, Coarse, NULL, NULL
I have done this previously by creating a temp table with an ID column and the structure of the second table, inserting the select distinct Item_IDs, then using a while loop to iterate through the first table, updating the rows in the second where the second table's Item_ID matches the first's, but there isn't an existing Tag field with the value for that Item_ID.
The problem with this solution is it means looping through the first table, then inside of that loop, looping through the second and updating where needed, which is very resource intensive. I've looked at the PIVOT command, but I can't find any samples with varchar values (the samples I've seen all have some sort of aggregation/count which I can't see how to adapt).
Does anyone know any more efficient ways of doing the above transformation?
July 30, 2013 at 10:43 pm
Something like this might work. I used a CASE here but PIVOT would also work.
IF object_id('dbo.vendor', 'u') IS NOT NULL
DROP TABLE vendor
GO
CREATE TABLE dbo.vendor (
item_id VARCHAR(10)
,tag VARCHAR(10)
)
INSERT INTO dbo.vendor
SELECT item_id = 'ITEM1'
,tag = 'Blue'
UNION
SELECT item_id = 'ITEM1'
,tag = 'Blue'
UNION
SELECT item_id = 'ITEM1'
,tag = 'Warm'
UNION
SELECT item_id = 'ITEM2'
,tag = 'Green'
UNION
SELECT item_id = 'ITEM3'
,tag = 'Coarse'
UNION
SELECT item_id = 'ITEM2'
,tag = 'Fine'
GO
SELECT ITem_ID
,Tag1 = MAX(CASE
WHEN RowNum = 1
THEN tag
END)
,Tag2 = MAX(CASE
WHEN RowNum = 2
THEN tag
END)
,Tag3 = MAX(CASE
WHEN RowNum = 3
THEN tag
END)
,Tag4 = MAX(CASE
WHEN RowNum = 4
THEN tag
END)
FROM (
SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY item_id)
,Item_Id
,Tag
FROM dbo.vendor
) vendor
GROUP BY Item_ID
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply