x

Convert rows to columns

Hi SSC,

Can we convert rows into columns in the result set. Please advice.

Thanks, Saurabh Shrivastava

more ▼

asked Jun 10, 2010 at 05:34 AM in Default

avatar image

Saurabh Shrivastava
6 1 1 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Yes, you can, investigate PIVOT.

more ▼

answered Jun 10, 2010 at 05:55 AM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

I was going to put something like that, and then bottled out...

Jun 10, 2010 at 06:04 AM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

the PIVOT command is, indeed, what you're looking for. Some documentation for it is here: http://msdn.microsoft.com/en-us/library/ms177410.aspx

If you want a more helpful answer than "PIVOT", you need to provide us with more information about what you are trying to do.

Include the table schema, some sample data, and what you're trying to achieve, then we will be able to be more helpful.

more ▼

answered Jun 10, 2010 at 07:46 AM

avatar image

ThomasRushton ♦♦
42k 20 50 53

(comments are locked)
10|1200 characters needed characters left

I haven't taken a liking to PIVOT (yet), so often I rely on the CASE statement, which can be your friend.

SELECT SUM(CASE WHEN fiscalYear = 2010 THEN Amount ELSE 0 END) AS [Year1],
       SUM(CASE WHEN fiscalYear = 2009 THEN Amount ELSE 0 END) AS [Year2], [etc.]
more ▼

answered Jun 10, 2010 at 05:01 PM

avatar image

Mark
2.6k 24 27 31

I know what you mean - I still need peace and quiet and a large mug of coffee before I can contemplate writing pivot statements... but when I'm in the groove, they are so powerful and so much easier than remembering what you were up to when writing a select loaded with Case statements - particularly when you have to redo all that stuff for the aggregates.

Jun 10, 2010 at 05:06 PM ThomasRushton ♦♦

Totally agreed - and +1 - the pivot syntax is pretty un-natural. I've just added semantic parsing support for pivot and unpivot to my editor - and I think I finally get it now :)

Jun 10, 2010 at 05:13 PM Matt Whitfield ♦♦

I agree. And it turns out that the classic cross tab is also faster than a pivot.

Jun 12, 2010 at 02:25 AM Jeff Moden
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2030
x114

asked: Jun 10, 2010 at 05:34 AM

Seen: 1895 times

Last Updated: Jun 10, 2010 at 03:58 PM

Copyright 2017 Redgate Software. Privacy Policy