August 20, 2013 at 2:39 pm
Need a query help, on unpivoting columns
Have the data as below...
create table #Test
(itemNo int,
Cat1int,
SubCat11int,
SubCat12int,
Cat2int,
SubCat21int,
Cat3int,
SubCat31int
)
---
Insert into #Test select 888,3,97,245,4,272,5,128
Insert into #Test select 999,1,20,200,3,211,6,455
--
Need the results as
ItemCatID SubCatID
888 397
888 3 245
888 4272
888 5128
999 120
999 1200
9993 211
9996455
Tried the following query...
SELECT ItemNo,
CategoryID
,SubCatID
FROM
(
SELECTItemNo,
Cat1,Cat2,Cat3,
Subcat11,Subcat12,Subcat21,Subcat31
FROM #Test
) Main
UNPIVOT
(
CategoryID FOR Categories IN (Cat1,Cat2,Cat3)
) Sup
UNPIVOT
(
SubCatID For SubCats IN (Subcat11,Subcat12,Subcat21,Subcat31)
) Ct
WHERE RIGHT(Categories,1) = RIGHT(SubCats,1)
The SubCatID is repeated for cat column combinations...showing wrong results...
Any help appreciated...
August 20, 2013 at 3:31 pm
There's an alternate method to UNPIVOT using CROSS APPLY, you could read about it on the following link to understand the code I'm posting.
http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
SELECT itemNoAS Item,
CatAS CatID,
SubCatAS SubCatID
FROM #Test
CROSS APPLY( VALUES
( Cat1, SubCat11),
( Cat2, SubCat21),
( Cat3, SubCat31))x( Cat, SubCat)
ORDER BY Cat, SubCat
August 21, 2013 at 7:26 am
The query worked. Made a minor modification, and it gave me the desired results.
SELECT itemNoAS Item,
CatAS CatID,
SubCatAS SubCatID
FROM #Test
CROSS APPLY( VALUES
( Cat1, SubCat11),
( Cat1, SubCat12),
( Cat2, SubCat21),
( Cat3, SubCat31))x( Cat, SubCat)
ORDER BY itemNo,Cat, SubCat
August 21, 2013 at 8:30 am
I'm glad it worked. I missed the Subcat12, but if you found out how to modify it, it means that you at least understand how the query is created. Be sure to understand how it works for future maintenance or explanations to other programmers. 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply