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

converting Columns into Rows Expand / Collapse
Author
Message
Posted Tuesday, September 22, 2009 3:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 4, 2013 2:02 AM
Points: 3, Visits: 38
I need a query / procedure which would convert Columns into Rows without causing any damages to the original data.

Table#1

Job Name1_1 name1_2 name1_2 name1_4 city1_1 city1_2 city1_3 city1_4 phone1_1 phone1_2 phoen1_3 phone1_4

XYZ aaaa bbbb cccc dddd abcd bcde cdef defg 1111 2222 3333 4444

output table as:

Job name city phone
XYZ aaaa abcd 1111
XYZ bbbb bcde 2222
XYZ cccc cdef 3333
XYZ dddd defg 4444

Please help .
Post #791603
Posted Tuesday, September 22, 2009 3:58 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:57 AM
Points: 1,949, Visits: 8,304
Simple enough with a union

select Job ,Name1_1 ,city1_1 , phone1_1
from table
union all
select Job ,Name1_2 ,city1_2 , phone1_2
from table
union all
select Job ,Name1_3 ,city1_3 , phone1_3
from table
union all
select Job ,Name1_4 ,city1_4 , phone1_4
from table





Clear Sky SQL
My Blog
Kent user group
Post #791618
Posted Tuesday, September 22, 2009 4:41 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,231, Visits: 3,483
Dave Ballantyne (9/22/2009)
Simple enough with a union

select Job ,Name1_1 ,city1_1 , phone1_1
from table
union all
select Job ,Name1_2 ,city1_2 , phone1_2
from table
union all
select Job ,Name1_3 ,city1_3 , phone1_3
from table
union all
select Job ,Name1_4 ,city1_4 , phone1_4
from table


Hi,
Use the UNION to get correct result.
create table #temp
(
jobs varchar(10),
colA1 varchar(10),
colB1 varchar(10),
colC1 varchar(10),
colA2 varchar(10),
colB2 varchar(10),
colC2 varchar(10),
colA3 varchar(10),
colB3 varchar(10),
colC3 varchar(10)
)

insert into #temp
select 'XYZ','aaaa','abcd','1111','bbbb','bcda','2222','cccc','cdab','3333'
union all
select 'YZA','aaaa','abcd','1111','bbbb','bcda','2222','cccc','cdab','3333'
union all
select 'ZAB','','','','','','','','',''

select jobs ,colA1 ,colB1 , colC1
from #temp
union
select jobs ,colA2 ,colB2 , colC2
from #temp
union
select jobs ,colA3 ,colB3 , colC3
from #temp

RESULT
jobs colA1 colB1 colC1
XYZ aaaa abcd 1111
XYZ bbbb bcda 2222
XYZ cccc cdab 3333
YZA aaaa abcd 1111
YZA bbbb bcda 2222
YZA cccc cdab 3333
ZAB

select jobs ,colA1 ,colB1 , colC1
from #temp
union all
select jobs ,colA2 ,colB2 , colC2
from #temp
union all
select jobs ,colA3 ,colB3 , colC3
from #temp

RESULT
jobs colA1 colB1 colC1
XYZ aaaa abcd 1111
YZA aaaa abcd 1111
ZAB
XYZ bbbb bcda 2222
YZA bbbb bcda 2222
ZAB
XYZ cccc cdab 3333
YZA cccc cdab 3333
ZAB
Post #791656
Posted Tuesday, September 22, 2009 6:33 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 36,777, Visits: 31,233
arun.sas (9/22/2009)
Use the UNION to get correct result.


Ummmm... maybe not... UNION will get rid of any duplicates and the duplicates may be important.


--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 #792295
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse