Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

HOW TO TRANSPOSE COLUMNS TO ROWS USING T-SQL Expand / Collapse
Author
Message
Posted Wednesday, March 15, 2006 7:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 12, 2009 10:11 PM
Points: 24, 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. 

 

Post #265882
Posted Wednesday, March 15, 2006 8:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652

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.
Post #265890
Posted Wednesday, March 15, 2006 10:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 12, 2009 10:11 PM
Points: 24, Visits: 8
Thanks it works so nicely though it didn't remove the NULL fields.
Post #266101
Posted Thursday, March 16, 2006 1:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, November 1, 2009 11:37 PM
Points: 158, 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
Post #266119
Posted Thursday, July 15, 2010 4:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, August 8, 2010 3:32 AM
Points: 1, 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
Post #953524
Posted Friday, April 8, 2011 4:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 12:38 AM
Points: 7, Visits: 377
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]
Post #1090477
Posted Wednesday, December 14, 2011 5:48 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1221585
Posted Wednesday, December 14, 2011 10:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 12:38 AM
Points: 7, Visits: 377
Dynamic solution is better than based on cursor one. Tat is the reason of my solution.
Post #1221868
Posted Wednesday, December 14, 2011 12:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1221944
Posted Wednesday, December 14, 2011 2:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 12:38 AM
Points: 7, Visits: 377
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.
Post #1222035
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse