Columns to rows

Question

Post reply

Columns to rows

pzmrcd

Default port

Points: 1419
More actions
October 5, 2010 at 3:02 pm

#229982

Hi
I have a table (T1) like this
T1:
Column1 column2 column3
row11 row12 row13
I want to get the result set as
Result:
T2:
Column1 row11
Column2 row12
Column3 row13
How canthis be done ?
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Derrick Smith SSChampion Points: 10833 More actions · Answer 1

That's called a PIVOT. Usually pivot operations are better done in excel or SSRS, but they are doable in t-sql.

See here for the official doc: http://msdn.microsoft.com/en-us/library/ms177410.aspx

LutzM SSC Guru Points: 107049 More actions · Answer 2

You might want to have a look at the CrossTab article referenced in my signature to see an alternative to PIVOT.

I find it easier to remember and it seems to perform better.

It's also easier to change it into DynamicCrossTab (see the related link in my sig).

As a side note: I'm not the author of either of the articles I mentioned. I just find them most useful. 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Hey folks... look carefully. This is NOT a Pivot or CrossTab request. It's an UNpivot request.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 4

Here we go, Jeff:

It is the CROSSTAB of UNPIVOTS

Is this what we're looking for?

declare @sample table (ID int identity(1,1), Column1 varchar(10), column2 varchar(10), column3 varchar(10))

insert into @sample

select 'row11', 'row12', 'row13' union all

select 'row21', 'row22', 'row23'

select * from @sample

select ID,ca.*

from @sample

cross apply

( values

('column1', column1)

,('column2', column2)

,('column3', column3)

) CA ([column],[row])

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

The Dixie Flatline (10/5/2010)
Here we go, Jeff:
It is the CROSSTAB of UNPIVOTS
Is this what we're looking for?
declare @sample table (ID int identity(1,1), Column1 varchar(10), column2 varchar(10), column3 varchar(10))
insert into @sample
select 'row11', 'row12', 'row13' union all
select 'row21', 'row22', 'row23'
select * from @sample
select ID,ca.*
from @sample
cross apply
( values
('column1', column1)
,('column2', column2)
,('column3', column3)
) CA ([column],[row])

That's pretty clever. I'll have to convert it to run in 2k5 just because I'm curious. Thanks, Bob.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

The Dixie Flatline (10/5/2010)
Here we go, Jeff:
It is the CROSSTAB of UNPIVOTS
Is this what we're looking for?
declare @sample table (ID int identity(1,1), Column1 varchar(10), column2 varchar(10), column3 varchar(10))
insert into @sample
select 'row11', 'row12', 'row13' union all
select 'row21', 'row22', 'row23'
select * from @sample
select ID,ca.*
from @sample
cross apply
( values
('column1', column1)
,('column2', column2)
,('column3', column3)
) CA ([column],[row])

Here's the 2K5 version of the unpivotting "cross tab" which I may use in 2K8 just so I only need to remember one way :-P. And, it looks like I have a new "toy" to play with. You can bet I'm going to do some testing for performance because this looks a WHOLE lot easier than an UnPivot to remember.

SELECT orig.RowNum, unpvt.ColumnName, unpvt.Data

FROM @Sample orig

CROSS APPLY

(

SELECT 'Column1', Column1 UNION ALL

SELECT 'Column2', Column2 UNION ALL

SELECT 'Column3', Column3

) unpvt (ColumnName,Data)

Thanks, Bob.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 7

It not only reads easier, it should be child's play to generate dynamically as well.

Jeff, you should check out this thread. It's about a year old, but it was one one of those great collaborations that pop up at SSC.

http://qa.sqlservercentral.com/Forums/Topic809640-338-1.aspx

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

The Dixie Flatline (10/11/2010)

It not only reads easier, it should be child's play to generate dynamically as well.

Jeff, you should check out this thread. It's about a year old, but it was one one of those great collaborations that pop up at SSC.

http://qa.sqlservercentral.com/Forums/Topic809640-338-1.aspx[/quote]

Heh... yeah... "new toy". Not sure how I missed a thread like that one especially over the last year. At least great minds think alike on the 2k5 version. 😛

I am going to practice what I preach, though... I'm going to do my own testing. First, that will help me memorize the method (although this one is incredibly easy). Second, it'll be fun for me. Third, I'll know for sure. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

WayneS SSC Guru Points: 95405 More actions · Answer 9

Geez Bob, I had forgotten all about that one. Thanks for the reminder, and for the opportunity to re-read "a great collaboration" again!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2