Again - Rows to Columns but null should be ignored

• Santhoshkumar KB

  SSCertifiable

  Points: 5066

  More actions

  February 16, 2010 at 9:01 am

  #156679

  Hi,

  create table #myTable1

  (

  id int

  , req1 varchar(10)

  , req2 varchar(10)

  , req3 varchar(10)

  , req4 varchar(10)

  , req5 varchar(10)

  )

  insert #myTable1

  select 1,'a1','b1','c1','d1','e1'

  union all

  select 2,'a2','b2','c2',null,null

  union all

  select 3,'a3',null,null,null,null

  union all

  select 4,null,null,null,null,null

  select * from #myTable1

  --u can imagine the output

  create table #myTable2

  (

  id int

  , requirement varchar(10)

  )

  SELECT INTO #myTable2 FROM #myTable1

  select * from #myTable2

  --output should be

  --null should not be considered

  1a1

  1b1

  1c1

  1d1

  2a2

  2b2

  2c2

  3a3

  drop table #myTable1

  drop table #myTable2

  Thanks,
  Santhosh
  

  ---

  Human Knowledge Belongs To The World !!

• matzer

  SSCrazy Eights

  Points: 9727

  More actions

  February 16, 2010 at 9:15 am

  #1119332

  select

  t1.id, t2.requirement

  into

  #myTable2

  from

  #myTable1 t1

  cross apply

  (

  select t1.req1 requirement

  union all

  select t1.req2

  union all

  select t1.req3

  union all

  select t1.req4

  union all

  select t1.req5

  ) t2

  where

  t2.requirement is not null

  Note: Works only if #myTable2 is not created explicitely

• Paul White

  SSC Guru

  Points: 150467

  More actions

  February 17, 2010 at 5:24 am

  #1119972

  SELECT U.id, U.requirement

  FROM #myTable1

  UNPIVOT (requirement FOR col IN (req1, req2, req3, req4, req5)) U;

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi