UNPIVOTING - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

UNPIVOTING

Author

Topic

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-12-20 : 02:02:57

Hi,
I have the below table structure

ITEMITEM AGE_FRM01 AGE_FRM02 AGE_FRM03 AGE_TO01 AGE_TO02 AGE_TO03 CESS_FRM01 CESS_FRM02 CESS_FRM03 CESS_TO01 CESS_TO02 CESS_TO03
E10R*INR 0 0 0 60 0 0 10 20 30 10 20 30

Here CESS is my input..
I want to fetch AGE_FRM02, AGE_TO02, if MY CESS falls in CESS_FRM02 and CESS_TO02 and so on.

If incase , My AGE_FRM02 and AGE_TO02 values are ZERO, i want t o FETCH First Record ( i.e 0,60 (AGE_FRM01,AGE_TO01))

Please help me....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-20 : 03:17:25

how is it different from this?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=169314

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-20 : 03:18:03

and i cant understand why you need UNPIVOTing here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-12-20 : 03:31:56

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=169314

The Table structure that i have mentioned in my prevoius topic is the one which i got after performing UNPIVOT on the above mentioned table structure.

I saved the result into Temp Table and used the query that u have posted.

Now, what i m trying to do is instead of using Temp table, want to add complete logic in my Unpivot query itself, There is nothing different

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-20 : 04:31:18

for unpivotting use below


SELECT ITEMITEM, AGE_FRM01 AS AGE_FRM, AGE_TO01 AS AGE_TO,CESS_FRM01 AS CESS_FRM,CESS_TO01  AS CESS_TO
FROM table
UNION ALL
SELECT ITEMITEM, AGE_FRM02, AGE_TO02,CESS_FRM02,CESS_TO02
FROM Table
UNION ALL
SELECT ITEMITEM, AGE_FRM03, AGE_TO03,CESS_FRM03,CESS_TO03
FROM TABLE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-12-20 : 04:37:38

But i have many columns.. for each FROM and TO .there are around 9 columns (01-09)
thats y, i used UNPIVOT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-20 : 04:45:56

UNPIVOT you can use them directly. you have to again spilt it up based on column groups. thats why i used union all

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-12-20 : 05:55:00

i did something like

SELECT AGE_FRM, AGE_TO, CESS_AGE_FRM,CESS_AGE_TO FROM
(
SELECT * FROM TABLE WHERE ITEMITEM='XYZ')PRODRULE
UNPIVOT (AGE_FRM FOR ITEM IN(AGE_FRM01,AGE_FRM02,AGE_FRM03) AS AGE_FRM
UNPIVOT (AGE_TO FOR ITEM1 IN(AGE_TO01,AGE_TO02,AGE_TO03) AS AGE_TO
UNPIVOT (CESS_AGE_FRM FOR ITEM2 IN(CESS_AGE_FRM01,CESS_AGE_FRM02,CESS_AGE_FRM03) AS CESS_AGE_FRM
UNPIVOT (CESS_AGE_TO FOR ITEM3 IN(CESS_AGE_TO01,CESS_AGE_TO02,CESS_AGE_TO03) AS CESS_AGE_TO
WHERE RIGHT(ITEM,2)=RIGHT(ITEM1,2) AND
RIGHT(ITEM1,2)=RIGHT(ITEM2,2) AND
RIGHT(ITEM2,2)=RIGHT(ITEM3,2)

And got the result as i mentioned in my previous post.

Now please let me know , how can i consolidate my query to get the desired result

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-20 : 06:57:55

quote:
Originally posted by Sql_forum

i did something like

SELECT AGE_FRM, AGE_TO, CESS_AGE_FRM,CESS_AGE_TO FROM
(
SELECT * FROM TABLE WHERE ITEMITEM='XYZ')PRODRULE
UNPIVOT (AGE_FRM FOR ITEM IN(AGE_FRM01,AGE_FRM02,AGE_FRM03) AS AGE_FRM
UNPIVOT (AGE_TO FOR ITEM1 IN(AGE_TO01,AGE_TO02,AGE_TO03) AS AGE_TO
UNPIVOT (CESS_AGE_FRM FOR ITEM2 IN(CESS_AGE_FRM01,CESS_AGE_FRM02,CESS_AGE_FRM03) AS CESS_AGE_FRM
UNPIVOT (CESS_AGE_TO FOR ITEM3 IN(CESS_AGE_TO01,CESS_AGE_TO02,CESS_AGE_TO03) AS CESS_AGE_TO
WHERE RIGHT(ITEM,2)=RIGHT(ITEM1,2) AND
RIGHT(ITEM1,2)=RIGHT(ITEM2,2) AND
RIGHT(ITEM2,2)=RIGHT(ITEM3,2)

And got the result as i mentioned in my previous post.

Now please let me know , how can i consolidate my query to get the desired result

you need to use logic suggested in other thread for getting your final result. for that best thing would be to create a CTE or temp table out of above and then use it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources