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


converting Columns into Rows


converting Columns into Rows

Author
Message
l_srinuu
l_srinuu
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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 .
Dave Ballantyne
Dave Ballantyne
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7402 Visits: 8370
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
arun.sas
arun.sas
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3129 Visits: 3493
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222068 Visits: 42003
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.
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
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