column to rows
in [Programming SQL]
|
From: rodchar on 14 Apr 2010 15:24 hi all, i'd like to take my single row result and turn selected columns into rows. id, col1, col2, col3 --------------------- 1, 10,20,30 Desired result: new1, new2 ---------------- col1, 10 col2, 20 col3, 30 what's the easiest way to do this? thanks, rodchar
From: Tom Cooper on 14 Apr 2010 16:30 Declare @Foo Table (id int, col1 int, col2 int, col3 int); Insert @Foo(id, col1, col2, col3) Select 1, 10,20,30; Select new1, new2 From (Select col1, col2, col3 From @Foo) As p Unpivot (new2 For new1 In (col1, col2, col3)) As unpvt; Tom "rodchar" <rodchar(a)discussions.microsoft.com> wrote in message news:A751A994-8697-4E75-A88B-E647146FEDC6(a)microsoft.com... > hi all, > > i'd like to take my single row result and turn selected columns into rows. > > id, col1, col2, col3 > --------------------- > 1, 10,20,30 > > > Desired result: > > new1, new2 > ---------------- > col1, 10 > col2, 20 > col3, 30 > > what's the easiest way to do this? > > thanks, > rodchar
From: Plamen Ratchev on 14 Apr 2010 17:07 You can always use UNION: SELECT 'col1' AS new1, col1 AS new2 FROM Foo UNION ALL SELECT 'col2', col2 FROM Foo UNION ALL SELECT 'col3', col3 FROm Foo; -- Plamen Ratchev http://www.SQLStudio.com
From: Uri Dimant on 15 Apr 2010 02:53 Tom You do not need derived table in that case SELECT new1, new2 FROM @Foo UNPIVOT (new2 For new1 In (col1, col2, col3)) As unpvt; "Tom Cooper" <tomcooper(a)comcast.net> wrote in message news:eCa0cFB3KHA.5196(a)TK2MSFTNGP05.phx.gbl... > Declare @Foo Table (id int, col1 int, col2 int, col3 int); > Insert @Foo(id, col1, col2, col3) > Select 1, 10,20,30; > > Select new1, new2 > From (Select col1, col2, col3 > From @Foo) As p > Unpivot > (new2 For new1 In (col1, col2, col3)) As unpvt; > > Tom > > "rodchar" <rodchar(a)discussions.microsoft.com> wrote in message > news:A751A994-8697-4E75-A88B-E647146FEDC6(a)microsoft.com... >> hi all, >> >> i'd like to take my single row result and turn selected columns into >> rows. >> >> id, col1, col2, col3 >> --------------------- >> 1, 10,20,30 >> >> >> Desired result: >> >> new1, new2 >> ---------------- >> col1, 10 >> col2, 20 >> col3, 30 >> >> what's the easiest way to do this? >> >> thanks, >> rodchar >
From: bill on 15 Apr 2010 03:17
I may be misunderstanding what the OP wants, but if he wants to parse a comma delimited string and insert the delmited elements as column values into a table, there is a great example here: http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html This works like a champ, is very fast, and it all happens in a singel query. I really like this method. Thanks, Bill |