November 24, 2008 at 2:19 am
Hi,
How will I convert column into rows in SQL 2000? I want to convert these columns into rows like this:
Currently I have this format:
Item#: Code: Value: Code0020: Code0030: Code0040: Code0060:
731Z52920020.6100000NULL NULL NULL NULL
731Z52920030.0400000NULL NULL NULL NULL
731Z52920040.4000000NULL NULL NULL NULL
731Z52920060.8000000NULL NULL NULL NULL
Expected format:
Item#: Code: Value: Code0020: Code0030: Code0040: Code0060:
731Z52920020.6100000 .6100000 .0400000 .4000000 .8000000
I discourage using a "hard - coded " scripts like this:
SELECT Item#,
sum(case when Code= '0020' then Value end) as Code0020
sum(case when Code= '0030' then Value end) as Code0030
sum(case when Code= '0040' then Value end) as Code0040
sum(case when Code= '0060' then Value end) as Code0060
FROM myTable
Thanks in advance.
November 24, 2008 at 4:06 am
I'm afraid there isn't a real sweet piece of code... You can get the data from the syscolumns and sysobjects tables and from this make a cursor or similar loop. Though, if you're planning to upgrade to 2005/2008 any time soon, you need to rewrite this. Also in 2005 you can use PIVOT, but that's not at all easier and less hard coded than the example you gave here.
I guess, if you want an example of how to write the loop thing, script your tables like the example in Jeff Moden's post (in my signature if it's going well 🙂 )
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply