Converting Rows into Columns

Question

Converting Rows into Columns

Chand00

SSCertifiable

Points: 6466
More actions
March 18, 2008 at 6:52 pm

#70891

Dear All,
Following is the description of the problem.
Ex: My table mtab holds the data in the following format
AccountID Account_ServiceNo Description
IND_1 1 This is Desc1
IND_1 5
IND_1 8 This is Desc3
IND_1 10 This is Desc4
Now I want output in following format
AccountID Col1 Col2 Col3 Col4
IND_1 This is Desc1 This is Desc3 This is Desc4
In my table there for a single accountid there may be max of 72 account_serviceno(ie. most of the cases have upto 6 only) and out of these cases some does not have any description. So i want to eliminate all these rows and display in the above format.
Regards
Chandra Mohan N
[font="Verdana"]Thanks
Chandra Mohan[/font]

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 1

In my table there for a single accountid there may be max of 72 account_serviceno(ie. most of the cases have upto 6 only) and out of these cases some does not have any description. So i want to eliminate all these rows and display in the above format.

You'll find there's about a million of us that are gonna recommend that you don't do that for way too many reasons to post here. But, if you insist, you'll need to do a dynamic cross-tab. See Books Online for how to do a Cross-Tab and convert it to Dynamic SQL to achieve your goal.

If you actually want tested code that might actually work, please see the URL in my signature for how to present your table structure and some sample data.

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

Chand00 SSCertifiable Points: 6466 More actions · Answer 2

Thanks. My next post will be in readable format as specified. But please suggest the solution for the problem

[font="Verdana"]Thanks
Chandra Mohan[/font]

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

I already did suggest a solution... lookup Crosstab in BOL and convert it to dynamic SQL.

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