HOW TO TRANSPOSE COLUMNS TO ROWS USING T-SQL

Question

HOW TO TRANSPOSE COLUMNS TO ROWS USING T-SQL

allan audi

Right there with Babe

Points: 738
More actions
March 15, 2006 at 7:57 am

#189724

I am still new in t-SQL. i have a table which i would like to transform its columns into rows
dssid1 dssid2 dssid3 name1 name2 name3
001 003 007 allan john bill
111 010 024 john sam oti

into single columns dssid name
thanks,
Allan Audi.

Viewing 11 posts - 1 through 10 (of 10 total)

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

RyanRandall SSChampion Points: 13623 More actions · Answer 1

You'll be needing a UNION:

DECLARE @t TABLE (dssid1 VARCHAR(3), dssid2 VARCHAR(3), dssid3 VARCHAR(3), name1 VARCHAR(20), name2 VARCHAR(20), name3 VARCHAR(20))

INSERT INTO @t

SELECT '001', '003', '007', 'allan', 'john', 'bill'

UNION SELECT '111', '010', '024', 'john', 'sam', 'oti'

SELECT dssid1, name1 FROM @t

UNION SELECT dssid2, name2 FROM @t

UNION SELECT dssid3, name3 FROM @t

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

allan audi Right there with Babe Points: 738 More actions · Answer 2

Thanks it works so nicely though it didn't remove the NULL fields.

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

DECLARE @t TABLE (dssid1 VARCHAR(3), dssid2 VARCHAR(3), dssid3 VARCHAR(3), name1 VARCHAR(20), name2 VARCHAR(20), name3 VARCHAR(20))

INSERT INTO @t

SELECT '001', '003', '007', 'allan', 'john', 'bill' UNION ALL

SELECT '111', '010', '024', 'john', 'sam', 'oti'

SELECT dssid1, name1 FROM @t WHERE name1 IS NOT NULL UNION ALL

SELECT dssid2, name2 FROM @t WHERE name2 IS NOT NULL UNION ALL

SELECT dssid3, name3 FROM @t WHERE name3 IS NOT NULL

srarul Valued Member Points: 73 More actions · Answer 4

No where i found general logic if the rows/column are unknown, then i written the following and it works

Example :

Table 1 : PayEarnings

EarnCode EarnName

-------- --------------------------------------------------

001 BasicSalary

002 Overtime

003 OtherAllowances A

Table 2: PayDesignation

DesigCode DesigName

--------- --------------------------

01 CEO

02 Directors

03 Accountant

Table 3: PayBudget [Transaction table]

DesigCode EarnCode Yearly

--------- -------- ---------------------------------------

01 001 60

01 002 70

01 003 80

02 001 50

02 002 60

02 003 70

03 001 40

03 002 50

03 003 60

Store Procedure : user store procedure TransPose

Create Procedure uspTransPose

as

Declare @STR nvarchar(4000)

Declare @EarnName nvarchar(50)

DECLARE @NL AS CHAR(2)

set @NL= CHAR(13) + CHAR(10) ---- New line

--- Creating transpose table

Set @STR=N'Create table #tmp (DesigName Nvarchar(50)'

DECLARE TranposeCur CURSOR FAST_FORWARD FOR select EarnName from PayEarnings

OPEN TranposeCur

FETCH NEXT FROM TranposeCur INTO @EarnName

WHILE @@FETCH_STATUS = 0

BEGIN

---- Adding columns in runtime

Set @STR=@Str +',['+@EarnName+'] Numeric(18)'

FETCH NEXT FROM TranposeCur INTO @EarnName

End

Close TranposeCur

DeAllocate TranposeCur

Set @STR=@Str+')'

---- End of transpose table creation

--- Inserting the designation records

Set @STR=@Str +@NL+'Insert into #tmp (DesigName) select DesigName from PayDesignation'

--- to minimise the lenght of @STR, actual query with join is now stored in #tmp2 table

Select Designame,EarnName,Yearly

into #tmp2

From PayBudget PB

Left Join PayEarnings PE on PE.EarnCode=PB.EarnCode

Left Join PayDesignation PD on PD.DesigCode=PB.DesigCode

DECLARE TranposeCur CURSOR FAST_FORWARD FOR select EarnName from PayEarnings

OPEN TranposeCur

FETCH NEXT FROM TranposeCur INTO @EarnName

WHILE @@FETCH_STATUS = 0

BEGIN

--- update statement

Set @STR=@str+@NL+'Update #tmp set ['+@EarnName+']=(Select isnull(Yearly,0) From #tmp2 Where DesigName=#tmp.DesigName and EarnName='''+@EarnName+''')'

FETCH NEXT FROM TranposeCur INTO @EarnName

End

Close TranposeCur

DeAllocate TranposeCur

--- to get the output

Set @STR=@str+@NL+'Select * from #tmp'

-- to get actual query

select @STR

--- to get output

EXECUTE sp_executesql @STR

Output :

DesigName BasicSalary Overtime OtherAllowances A

-------------------- --------------- ---------- -----------------

CEO 60 70 80

Directors 50 60 70

Accountant 40 50 60

Thanks

R.Arul Murugan

srarul@yahoo.com

+91 98403 40969

+971 50 164 7438

16/07/2010

dalexmailbox Old Hand Points: 378 More actions · Answer 5

To easily transpose columns into rows with its names you should use XML. In my blog I was described this solution with example:

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

dalexmailbox (4/8/2011)
To easily transpose columns into rows with its names you should use XML. In my blog I was described this solution with example:

It doesn't appear to be the XML that does the trick there. It's the dynamic SQL that does it. Why is that any better than just plain old dynamic SQL driving a Cross Apply?

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

dalexmailbox Old Hand Points: 378 More actions · Answer 7

Dynamic solution is better than based on cursor one. Tat is the reason of my solution.

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

dalexmailbox (12/14/2011)
Dynamic solution is better than based on cursor one. Tat is the reason of my solution.

Agreed that just about any solution is better than a cursor and that, yes, the dynamic solution is the way to go. I just don't understand why you used XML for the solution.

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

dalexmailbox Old Hand Points: 378 More actions · Answer 9

Main reason to do this is to do not:

1. Hardcore column names in case of using PIVOT clause.

2. Query metadata in case of dynamic SQL building.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 10

Not exactly the answer I was looking for. 😉

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