Row to Column conversion

Question

Row to Column conversion

dapen

SSC Rookie

Points: 27
More actions
September 18, 2008 at 2:30 pm

#382728

I have a table that is in this format:
enc_id, code
1 a
1 b
1 c
2 d
2 a
2 b
2 z
3 a
3 m
etc...
Would like to convert to:
enc_id, code0, code1, code2, code3, code4, etc...
1 a b c
2 d a b z
3 a m
Note - there are a random number of codes per enc_id.
Column names for output don't matter.

Viewing 8 posts - 1 through 8 (of 8 total)

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

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

It looks like your best bet is going to be having a subquery which assigns an "order" to the CODE column somehow, like using:

SELECT enc_id,

MAX(CASE WHEN codenum = 1 THEN code ELSE NULL END) AS code1,

MAX(CASE WHEN codenum = 2 THEN code ELSE NULL END) AS code2,

MAX(CASE WHEN codenum = 3 THEN code ELSE NULL END) AS code3,

MAX(CASE WHEN codenum = 4 THEN code ELSE NULL END) AS code4,

MAX(CASE WHEN codenum = 5 THEN code ELSE NULL END) AS code5

FROM

(SELECT enc_id, code, ROW_NUMBER() OVER (PARTITION BY enc_id ORDER BY code) AS codenum

FROM sometablename) sq

GROUP BY enc_id

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Chris' code will work if the max number of columns per encid is known. If not - you're going to have to build something that dynamically builds a query based around that same idea.

Why is it you need to do this? Just asking, since this is a fair chunk of work to impose on a server, so just trying to figure out why we're putting it through this kind of exercise.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

dapen SSC Rookie Points: 27 More actions · Answer 3

Sorry should have mentioned that I am not outputting this to a table. I need it for a report. This will be part of a much larger SELECT statement that will be run monthly to produce a report.

dapen SSC Rookie Points: 27 More actions · Answer 4

Chris,

Thanks for the code! It has sent me in the right direction. I now have to figure out how to dynamically create the SQL based on the max number of code per enc_id. Any ideas??

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

The PIVOT operator rotates rows into columns, optionally performing aggregations or other mathematical calculations along the way.

You might want to read this:

http://www.sqlservercentral.com/articles/Development/2734/

A partial extract from the above article.

PIVOT FUNCTION:

It widens the input table expression based on a given pivot column and generates an output table with a column for each unique value in the pivot column. The PIVOT operator is useful for handling open-schema scenarios and for generating cross tab reports.

The syntax for the PIVOT relational operator is:

FROMtable_source

PIVOT ( aggregate_function ( value_column )

FOR pivot_column

IN ( )

) table_alias

The article continues with examples of the Pivot Function and the results that can be achieved.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6