Transposing column to Row

Question

Post reply

Transposing column to Row

karthik M

One Orange Chip

Points: 29139
More actions
August 17, 2007 at 7:28 am

#120170

Hi ,
I need to transpose the column name to row( without using any function) ,can anybody help me to sort out this problem ?
Example :
select sum(ParAmt )ParAmt ,
sum(DenomQty )DenomQty ,
sum(convert(float,CInsNbr))CInsNbr,
sum(convert(float,HNbr))HNbr
from CHold
output :
ParAmt       DenomQty CInsNbr         HNbr
92376.9341 121.0         131852658.0 2281.0
But i like to display the output as
ParAmt      92376.9341
DenomQty 121.0
CInsNbr      131852658.0
HNbr          2281.0
( Note : I dont want to use any system or predefined functions.
I need a pure sql code to complete this issue )

Regards
Karthik
karthik

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

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

Ken McKelvey SSCoach Points: 18853 More actions · Answer 1

SELECT

CASE N.Number

WHEN 1 THEN 'ParAmt'

WHEN 2 THEN 'DenomQty'

WHEN 3 THEN 'CInsNbr'

WHEN 4 THEN 'HNbr'

END AS Col

,CASE N.Number

WHEN 1 THEN ParAmt -- cast to float if not already float

WHEN 2 THEN DenomQty -- cast to float if not already float

WHEN 3 THEN CInsNbr

WHEN 4 THEN HNbr

END AS Value

FROM (

SELECT SUM(ParAmt ) AS ParAmt

,SUM(DenomQty ) AS DenomQty

,SUM(CAST(CInsNbr AS float)) AS CInsNbr

,SUM(CAST(HNbr AS float)) AS HNbr

FROM CHold

) D

CROSS JOIN (

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4

) N (Number)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

>>I need a pure sql code to complete this issue

Why?

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

karthik M One Orange Chip Points: 29139 More actions · Answer 3

Because , the same sql query will be execute from both the sybase and sqlserver2000 server. That's why i am asking pure sql code (without using any specific sqlserver related functions).

Regards

Karthik

karthik

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

Do you think this is vital information?

If so, you should have posted this in your original post.

N 56°04'39.16"
E 12°55'05.25"