Columns to Row?

Question

Post reply

Columns to Row?

Ulysses_Infinity

SSCrazy

Points: 2588
More actions
March 12, 2011 at 1:47 am

#233833

Hi all,
Due to the complexities of the data I have framed this question as 'theoretical', hence no DDL code.
I have a query which returns two fields, 'description' and 'category' via a straightforward join. The 'description' to 'category' can be one-to-many, so in these cases my query returns one row for each category. If I wanted to have just one record per 'description' and have the categories returned in columns (i.e. category 1, category 2, category 3 . . .) can anyone recommend me the best theoretical approach. (In this example the maximum number of categories is ten).
Any help greatly appreciated.
Regards . . . Jason

Viewing 15 posts - 1 through 15 (of 22 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please have a look at the CrossTab article referenced in my signature.

You might also continue with the DynamicCrossTab concept if you need even more flexibility.

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]

Ulysses_Infinity SSCrazy Points: 2588 More actions · Answer 2

Hi Lutz,

I get the concept of using CASE for pivotting aggregations (and use it regularly), but my query isn't an aggregate query, just a simple select. So I can't see how the concept applies if I'm not grouping. I'm probably just being a bit stupid and missing the point. Let's say I have the following returned by a non-aggregate select:

Description | Category

-----------------------

A apple

A aardvark

A anteater

A antelope

How to get one row for A and the 4 'categories' in four columns?

Rgds . . .Jason

LutzM SSC Guru Points: 107049 More actions · Answer 3

The concept still applies:

DECLARE @temp TABLE

(

Description_ CHAR(1),

Category VARCHAR(30)

)

INSERT INTO @temp

SELECT 'A','apple' UNION ALL

SELECT 'A','aardvark' UNION ALL

SELECT 'A','anteater' UNION ALL

SELECT 'A','antelope'

SELECT

MAX(CASE WHEN Category = 'apple' THEN Description_ ELSE NULL END ) AS apple,

MAX(CASE WHEN Category = 'aardvark' THEN Description_ ELSE NULL END ) AS aardvark,

MAX(CASE WHEN Category = 'anteater' THEN Description_ ELSE NULL END ) AS anteater,

MAX(CASE WHEN Category = 'antelope' THEN Description_ ELSE NULL END ) AS antelope

FROM @temp

/*

appleaardvarkanteaterantelope

AAAA

*/

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 4

jason-772533 (3/12/2011)
I'm probably just being a bit stupid and missing the point.

Nope. You're alright, Jason. 🙂 It's a bit difficult to understand the concept when it comes to using MAX(CASE) to do a pivot with character base values. Lutz's example is spot on for this.

--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

Ulysses_Infinity SSCrazy Points: 2588 More actions · Answer 5

Thanks Jeff . . . it was puzzling me a bit, but makes sense now - the technique isn't using MAX in its classic sense.

However, I still don't think this technique fits with my scenario, because although the maximum number of 'category' values per 'description' is 10, across the full dataset there may be over a hundred distinct 'category' values.

Can you think of any technique that can accommodate this, so that even if two 'descriptions' have two different 'categories' they are still returned in columns 2 and 3, i.e.

A Apple Aardvark

B Banana Baboon

Any help greatly appreciated.

Can I just take this opportunity to thank you personally Jeff for all your articles on SSC. They have become an invaluable reference source for me as I have taught myself SQL over the past 18 months, and have pride of place as .doc files in my 'SQL Learning' folder.

Regards . . . Jason

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

Hi Jason. Thank you very much for the high praise. :blush: I do my best and the good folks who engage in the discussions on any article are the best.

To demonstrate the use of MAX in Cross Tabs in the manner you seek, could you build some readily consumable data and provide a "print out" of the desired results so that I can give it a try for you? Thanks.

--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

drew.allen SSC Guru Points: 76776 More actions · Answer 7

You'll probably want to use the ROW_NUMBER() partitioned by the Category for determining the columns. Before pivoting you'll have

A Aardvark 1

A Apple 2

B Baboon 1

B Banana 2

Then your pivot statement will look something like

CASE Max(CASE WHEN rn = 1 THEN Description END) AS Description1

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Ulysses_Infinity SSCrazy Points: 2588 More actions · Answer 8

Hi Drew, Therein lies a small problem. I'm using a 2000 server, so can't use ROW_NUMBER as it's 2005 syntax!

. . . Jason

drew.allen SSC Guru Points: 76776 More actions · Answer 9

jason-772533 (3/14/2011)
Hi Drew, Therein lies a small problem. I'm using a 2000 server, so can't use ROW_NUMBER as it's 2005 syntax!

There is a separate forum for SQL 7.0/2000 questions http://qa.sqlservercentral.com/Forums/Forum8-1.aspx. You'd be less likely to get 2005 only responses if you post in that forum.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Ulysses_Infinity SSCrazy Points: 2588 More actions · Answer 10

I checked the 'Forums' list before I posted and couldn't see a 2K forum. I've just checked again and there isn't one listed. There is an 'Older versions' forum but this doesn't stipulate 2K

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

The forums are somewhat out of the order one might expect and you may have simply missed it. Here's the link to the 7/2000 forum.

http://qa.sqlservercentral.com/Forums/Group406.aspx

--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 12

drew.allen (3/14/2011)
jason-772533 (3/14/2011)
Hi Drew, Therein lies a small problem. I'm using a 2000 server, so can't use ROW_NUMBER as it's 2005 syntax!
There is a separate forum for SQL 7.0/2000 questions http://qa.sqlservercentral.com/Forums/Forum8-1.aspx. You'd be less likely to get 2005 only responses if you post in that forum.
Drew

Above the "Older Versions" is a forum for "SQL Server 7,2000"

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

LutzM SSC Guru Points: 107049 More actions · Answer 13

jason-772533 (3/14/2011)
I checked the 'Forums' list before I posted and couldn't see a 2K forum. I've just checked again and there isn't one listed. There is an 'Older versions' forum but this doesn't stipulate 2K

I don't think it's required to repost the question over there... Maybe Steve can move the thread?

Regarding the issue you're struggling with: I would look into the quirky update method.

An example can be found in Jeffs article[/url]. I don't think there's an easier way to simulate ROW_NUMBER() in SS2K.

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 14

I agree. Maybe there's not even the need to move the post now that we know which version it's for unless someone else may be looking for such a solution in 2k.

Yeah... especially now that we know it's for 2k, some additional test data in a readily consumable format is strongly encouraged.

--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