converting Columns into Rows

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » converting Columns into Rows

converting Columns into Rows

Rate Topic

Display Mode

Topic Options

Author

Message

l_srinuu

Posted Tuesday, September 22, 2009 3:25 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 08, 2011 6:08 AM
Points: 3, Visits: 37

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

Dave Ballantyne

Posted Tuesday, September 22, 2009 3:58 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 3:05 AM
Points: 1,943, Visits: 8,229

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

arun.sas

Posted Tuesday, September 22, 2009 4:41 AM

Ten 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

Jeff Moden

Posted Tuesday, September 22, 2009 6:33 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112, Visits: 27,038

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #792295

« Prev Topic | Next Topic »

Permissions