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

General SQL Server Forums

New to SQL Server Programming

Unpivoting

Author

Topic

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-15 : 05:49:05

Hi All i have data like this..

ID Name1 Price1 Name2 Price2 Name3 Price3
1 ABC 10.00 DEF 20.00 GHI 30.00

I need output like..

ID Name Price
1 ABC 10.00
1 DEF 20.00
1 GHI 30.00

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 05:53:18

[code]select ID,Name1,Price1
from table
union all
select ID,Name2,Price2
from table
union all
select ID,Name3,Price3
from table
[/code]

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

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-15 : 06:00:44

We cant use union all in it.
Can i do this using UNPIVOT or something using crosstab queries

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-15 : 07:26:51

IS there any way..??

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 07:31:14

quote:
Originally posted by ashishashish

We cant use union all in it.
Can i do this using UNPIVOT or something using crosstab queries

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

why so?

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

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-15 : 07:32:42

because this is a bad practice i think.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 07:36:55

here's another way


SELECT m.ID,m.Val AS Name,n.Val AS Price
FROM
(SELECT ID,Val FROM (SELECT ID,Name1,Name2,Name3 FROM table)t1
UNPIVOT(Val FOR Name IN ([Name1],[Name2],[Name3]))u1)m
INNER JOIN (SELECT ID,Val  FROM (SELECT ID,Price1,Price2,Price3 FROM table)t2
UNPIVOT(Val FOR Price IN ([Price1],[Price2],[Price3]))u2)n
ON n.ID = m.ID

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

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-15 : 07:44:35

Its not giving the right results. As if we join on ID than all names with all prices get included in the query result the partial result set from your query is...

1 ABC 10.00
1 DEF 10.00
1 GHI 10.00
1 ABC 20.00
1 DEF 20.00
1 GHI 20.00
1 ABC 30.00
1 DEF 30.00
1 GHI 30.00

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 07:46:51

do you have multiple records for same ID value in your original table?

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

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-15 : 07:49:36

No i dont have multiple records in my original table but when we unpivot this than we will get multiple records and on these multiple records we are joining i think.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 07:52:19

[code]SELECT m.ID,m.Val AS Name,n.Val AS Price
FROM
(SELECT ROW_NUMBER() OVER (PRTITION BY ID ORDER BY ID) AS Seq,ID,Val FROM (SELECT ID,Name1,Name2,Name3 FROM table)t1
UNPIVOT(Val FOR Name IN ([Name1],[Name2],[Name3]))u1)m
INNER JOIN (SELECT ROW_NUMBER() OVER (PRTITION BY ID ORDER BY ID) AS Seq,ID,Val FROM (SELECT ID,Price1,Price2,Price3 FROM table)t2
UNPIVOT(Val FOR Price IN ([Price1],[Price2],[Price3]))u2)n
ON n.ID = m.ID
AND n.Seq=m.Seq

[/code]

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

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-15 : 07:59:24

Thanks... it helped a lot.
Thanks for your support and patience.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2011-09-15 : 10:21:12

quote:
Originally posted by ashishashish

because this is a bad practice i think.

...and that's the point where I would have stopped trying to help.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-09-15 : 13:21:24

SELECT m.ID,m.Val AS Name,n.Val AS Price
FROM
(SELECT ROW_NUMBER() OVER (PRTITION BY ID ORDER BY ID) AS Seq,ID,Val FROM (SELECT ID,Name1,Name2,Name3 FROM table)t1
UNPIVOT(Val FOR Name IN ([Name1],[Name2],[Name3]))u1)m
INNER JOIN (SELECT ROW_NUMBER() OVER (PRTITION BY ID ORDER BY ID) AS Seq,ID,Val FROM (SELECT ID,Price1,Price2,Price3 FROM table)t2
UNPIVOT(Val FOR Price IN ([Price1],[Price2],[Price3]))u2)n
ON n.ID = m.ID
AND n.Seq=m.Seq

Using cross join for better performance.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-16 : 01:35:28

quote:
Originally posted by blindman

quote:
Originally posted by ashishashish

because this is a bad practice i think.

...and that's the point where I would have stopped trying to help.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Sir,
I think you are there to help others and everybody knows that using UNION ALL method is not preferable, if you think that u should stop helping others others because somebody wants or like to have some other approach to solve the problem than it is definitely your choice you can stop sharing or helping peoples anytime you want.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2011-09-16 : 11:43:37

quote:
Originally posted by ashishashish
...everybody knows that using UNION ALL method is not preferable

Sir, you are completely mistaken in this. Which was the point of my post.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources