Pivot with varchar column

• gosth98

  SSC Veteran

  Points: 281

  More actions

  October 31, 2012 at 5:31 pm

  #264107

  Hi

  I have a question regargin Pivot Tables using varchar columns...

  I have the following scenario...

  declare @sample table(col1 varchar(50), col2 varchar(50), col3 varchar(50))

  insert into @sample values ('A','B','X1')

  insert into @sample values ('A','C','X2')

  insert into @sample values ('A','D','X3')

  insert into @sample values ('B','B','Y1')

  insert into @sample values ('B','C','Y2')

  insert into @sample values ('B','D','Y3')

  insert into @sample values ('C','B','Z1')

  insert into @sample values ('C','C','Z2')

  insert into @sample values ('C','D','Z3')

  and the output i need is:

  AB C

  ---------------------

  B X1Y1Z1

  C X2Y2Z2

  D X3Y3Z3

  i appreciate your help/orientation...

• David Betteridge

  Hall of Fame

  Points: 3264

  More actions

  October 31, 2012 at 5:53 pm

  #1554813

  SELECT Col2,

  MAX(CASE Col1 WHEN 'A' THEN Col3 ELSE '' END ) AS A,

  MAX(CASE Col1 WHEN 'B' THEN Col3 ELSE '' END ) AS B,

  MAX(CASE Col1 WHEN 'C' THEN Col3 ELSE '' END ) AS C

  FROM @sample

  GROUP BY Col2

  regards

  david

• gosth98

  SSC Veteran

  Points: 281

  More actions

  October 31, 2012 at 9:08 pm

  #1554831

  Yeah that will work when you have a fixed number of columns or not to many columns...

• Jeff Moden

  SSC Guru

  Points: 1004318

  More actions

  November 1, 2012 at 12:41 am

  #1554866

  gosth98 (10/31/2012)

  ---

  Yeah that will work when you have a fixed number of columns or not to many columns...

  The number of columns aren't really going to matter here unless you have some other way to pivot them. If the number of columns are dynamic, that's a pretty easy fix with a little high performance SQL.

  --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)