converting Columns into Rows

• l_srinuu

  Valued Member

  Points: 67

  More actions

  September 22, 2009 at 3:25 am

  #385405

  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

  SSC-Dedicated

  Points: 33566

  More actions

  September 22, 2009 at 3:58 am

  #1056643

  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[/url]

• arun.sas

  SSChampion

  Points: 11831

  More actions

  September 22, 2009 at 4:41 am

  #1056672

  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

  jobscolA1colB1colC1

  XYZaaaaabcd1111

  XYZbbbbbcda2222

  XYZcccccdab3333

  YZAaaaaabcd1111

  YZAbbbbbcda2222

  YZAcccccdab3333

  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

  jobscolA1colB1colC1

  XYZaaaaabcd1111

  YZAaaaaabcd1111

  ZAB

  XYZbbbbbcda2222

  YZAbbbbbcda2222

  ZAB

  XYZcccccdab3333

  YZAcccccdab3333

  ZAB

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  September 22, 2009 at 6:33 pm

  #1057224

  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.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)