How to Show Columns as Rows
-
i have a table which i would like to transform its columns into rows
Thanks in Advns
-
------------
When you 've got a hammer, everything starts to look like a nail... -
Can you do lilke this move the data to excel sheet, copy all data and while pasting select paste special and select transpose..
-
select col1 from table1
union all
select col2 from table1
union all
select col3 from table1
-
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.
-
ty Marshall Smith. i have used C#.Net for this Problem. i have got the result.
Thankyou very much
parmesh
-
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) -
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?
-
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)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply