January 9, 2008 at 12:08 pm
Hi,
I have a script that bring output like this
Name Code T1 T2 T3
-------------------------------
xyz 101 19 20 21
I need my output to look like this
Name Code Amt Type
------------------------
xyz 101 19 T1
xyz 101 20 T2
xyz 101 21 T3
How to implement that?
My script looks like this
Select
Name,
Code,
T1,
T2,
T3
From
.....................
January 9, 2008 at 12:14 pm
Read up on PIVOT and UNPIVOT in BOL. The answer is in there. 🙂
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 9, 2008 at 1:18 pm
Hi Jason,
Does this Unpivot work for SQL 2000?
January 9, 2008 at 1:35 pm
No, Pivot, and Unpivot are new in sql2k5.
Since you posted in 2k5 forum we assumed that is what u were using.
January 9, 2008 at 1:40 pm
oops..i did not notice.
But this script is for Sql 2000. Is there any alternative to perform this in 2000?
There is a PIVOT table option in books online..but it does exactly the opposite of what i want
January 9, 2008 at 1:44 pm
Then unfortunately your choices get a bit harder.
The manual way to do that would be something like:
select name, code, T1 as Amount, 'T1' as type
from mytable
UNION ALL
select name, code, T2 as Amount, 'T2' as type
from mytable
UNION ALL
select name, code, T3 as Amount, 'T3' as type
from mytable
Of course - if you have a LOT of columns - that will get "old" quick, so you might care to investigate dynamic SQL to make that happen.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 10, 2008 at 7:21 am
Better than that, correctly designing a normalized table would go a long way 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply