Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query help with unpivot qry Expand / Collapse
Author
Message
Posted Tuesday, August 20, 2013 2:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 10:05 AM
Points: 177, Visits: 355
Need a query help, on unpivoting columns
Have the data as below...

create table #Test
(itemNo int,
Cat1 int,
SubCat11 int,
SubCat12 int,
Cat2 int,
SubCat21 int,
Cat3 int,
SubCat31 int
)

---
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

Item CatID SubCatID
888 3 97
888 3 245
888 4 272
888 5 128
999 1 20
999 1 200
999 3 211
999 6 455


Tried the following query...

SELECT ItemNo,
CategoryID
,SubCatID
FROM
(
SELECT ItemNo,
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...
Post #1486438
Posted Tuesday, August 20, 2013 3:31 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:37 PM
Points: 3,342, Visits: 7,215
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 itemNo	AS Item, 
Cat AS CatID,
SubCat AS SubCatID
FROM #Test
CROSS APPLY( VALUES
( Cat1, SubCat11),
( Cat2, SubCat21),
( Cat3, SubCat31))x( Cat, SubCat)
ORDER BY Cat, SubCat




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1486452
Posted Wednesday, August 21, 2013 7:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 10:05 AM
Points: 177, Visits: 355
The query worked. Made a minor modification, and it gave me the desired results.

SELECT itemNo AS Item,
Cat AS CatID,
SubCat AS SubCatID
FROM #Test
CROSS APPLY( VALUES
( Cat1, SubCat11),
( Cat1, SubCat12),
( Cat2, SubCat21),
( Cat3, SubCat31))x( Cat, SubCat)
ORDER BY itemNo, Cat, SubCat


Post #1486700
Posted Wednesday, August 21, 2013 8:30 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:37 PM
Points: 3,342, Visits: 7,215
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.


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1486752
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse