Transpose data

• Old McDonald

  SSCertifiable

  Points: 5768

  More actions

  February 27, 2009 at 1:16 am

  #126733

  I have following table

  if object_id('tempdb..#TEST') is not null

  drop table #TEST;

  CREATE TABLE #TEST(

  [DealNo] [int] NOT NULL,

  [Comment1] [varchar](512) NULL,

  [Comment2] [varchar](512) NULL,

  [Comment3] [varchar](512) NULL,

  ) ON [PRIMARY]

  GO

  INSERT INTO #TEST

  SELECT 1, '1Comment1','1Comment2','1Comment3'

  UNION ALL

  SELECT 2, '2Comment1',NULL,'2Comment3'

  UNION ALL

  SELECT 3, NULL,'3Comment2','3Comment3'

  SELECT * FROM #TEST

  I want the result in following format:

  DealNoCommentTypeComment

  1 Comment1 1Comment1

  1 Comment2 1Comment2

  1 Comment3 1Comment3

  2 Comment1 2Comment1

  2 Comment3 2Comment3

  3 Comment2 3Comment2

  3 Comment3 3Comment3

  Can you please help me?

  -Vikas Bindra

• Mark Cowne

  One Orange Chip

  Points: 26954

  More actions

  February 27, 2009 at 2:13 am

  #951029

  SELECT DealNo,'Comment1' AS CommentType,Comment1 AS Comment

  FROM #TEST

  WHERE Comment1 IS NOT NULL

  UNION ALL

  SELECT DealNo,'Comment2' AS CommentType,Comment2 AS Comment

  FROM #TEST

  WHERE Comment2 IS NOT NULL

  UNION ALL

  SELECT DealNo,'Comment3' AS CommentType,Comment3 AS Comment

  FROM #TEST

  WHERE Comment3 IS NOT NULL

  ORDER BY DealNo,CommentType,Comment

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Old McDonald

  SSCertifiable

  Points: 5768

  More actions

  February 27, 2009 at 3:30 am

  #951042

  Thanks Mark!!

  -Vikas Bindra