How to Show Columns as Rows

Question

Post reply

How to Show Columns as Rows

Parmesh Althi

Old Hand

Points: 315
More actions
April 7, 2006 at 12:21 am

#113667

i have a table which i would like to transform its columns into rows
Thanks in Advns

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

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

Andreas AJV SSC Eights! Points: 800 More actions · Answer 1

------------
When you 've got a hammer, everything starts to look like a nail...

Vee Hall of Fame Points: 3028 More actions · Answer 2

Can you do lilke this move the data to excel sheet, copy all data and while pasting select paste special and select transpose..

[KH] SSCommitted Points: 1908 More actions · Answer 3

select col1 from table1

union all

select col2 from table1

union all

select col3 from table1

Marshall Smith SSCrazy Points: 2041 More actions · Answer 4

KH, I don't think that will quite do what Parmesh wants. That union won't transpose the table. It will just concatenate all the information into one long column.

Parmesh, your problem is, essentially, not solvable in T-SQL, simply because rows and columns are radically different things, that obey very different rules. However, there are some specific cases where it is needed. But, the solution depends on the case.

If this is an occasional need, exporting it to Excel and transposing is the easiest solution.

If you need a cross-tab style query to do fairly simple analysis, you can accomplish it with CASE statements. I can elaborate if that fits your needs.

If you want to do more complicated analysis, look into OLAP cubes.

If none of these fit your needs, then you are probably either looking at the original problem from the wrong angle, or you are working with poor table design.

Parmesh Althi Old Hand Points: 315 More actions · Answer 5

ty Marshall Smith. i have used C#.Net for this Problem. i have got the result.

Thankyou very much

parmesh

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

KH did it right... if you expand upon the example KH provided, it works great, is nasty fast, and does not require a trip to the app.

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

Marshall Smith SSCrazy Points: 2041 More actions · Answer 7

OK, Jeff, how did KH do it right?

Suppose the table is:

A 1 *

B 2 @

C 3 #

As I read it, Parmesh wanted:

A B C

1 2 3

* @ #

KH's solution produces:

A

B

C

1

2

3

*

@

#

Which is not the same thing at all. Even when expanded upon. Is there something I'm missing?

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

Guess I read it differently, Marshall... I didn't read the original request as a full transposition based on what some folks have asked for in the past... Not enough coffee

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