cross tab report for the character data.

• sql_novice_2007

  Hall of Fame

  Points: 3307

  More actions

  January 10, 2012 at 2:11 pm

  #253138

  I have the below data.

  id colname colvalue

  1 name la

  1 number 1

  1 rank 3

  2 name fi

  2 number 2

  2 rank 2

  How can I transform the above data to the following format?

  id name number rank

  1 la 1 3

  2 fi 2 2

  I tried cross tab reports in the past for the integer data but not the character data.

  Thanks.

• Paul White

  SSC Guru

  Points: 150467

  More actions

  January 10, 2012 at 4:58 pm

  #1431503

  This is how to present sample data:

  DECLARE @data TABLE

  (

  Id integer NOT NULL,

  ColName varchar(20) NOT NULL,

  ColValue varchar(10) NOT NULL,

  PRIMARY KEY (Id, ColName)

  );

  INSERT @data

  (Id, ColName, ColValue)

  VALUES

  (1, 'name', 'la'),

  (1, 'number', '1'),

  (1, 'rank', '3'),

  (2, 'name', 'fi'),

  (2, 'number', '2'),

  (2, 'rank', '2');

  And this is one possible solution:

  SELECT

  p.id,

  p.name,

  p.number,

  p.[rank]

  FROM @data AS d

  PIVOT (MAX(ColValue) FOR ColName IN ([name], [number], [rank])) AS p;

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• sql_novice_2007

  Hall of Fame

  Points: 3307

  More actions

  January 11, 2012 at 9:54 am

  #1431935

  It worked for me.

  Thanks for your help.