SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


HOW TO TRANSPOSE COLUMNS TO ROWS USING T-SQL


HOW TO TRANSPOSE COLUMNS TO ROWS USING T-SQL

Author
Message
allan audi
allan audi
SSC-Enthusiastic
SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)

Group: General Forum Members
Points: 164 Visits: 8

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.


RyanRandall
RyanRandall
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4147 Visits: 4652

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
allan audi
SSC-Enthusiastic
SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)

Group: General Forum Members
Points: 164 Visits: 8
Thanks it works so nicely though it didn't remove the NULL fields.
[KH]
[KH]
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 40
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
srarul
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 16
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
dalexmailbox
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 387
To easily transpose columns into rows with its names you should use XML. In my blog I was described this solution with example: [url=http://sql-tricks.blogspot.com/2011/04/sql-server-rows-transpose.html][/url]
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210109 Visits: 41973
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: [url=http://sql-tricks.blogspot.com/2011/04/sql-server-rows-transpose.html][/url]


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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dalexmailbox
dalexmailbox
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 387
Dynamic solution is better than based on cursor one. Tat is the reason of my solution.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210109 Visits: 41973
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dalexmailbox
dalexmailbox
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 387
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search