Transform rows into columns in sql server 2005 ...

• sachingovind-742691

  SSC Rookie

  Points: 46

  More actions

  April 17, 2011 at 1:04 pm

  #389303

  Hi All ,

  I Have a table Like Below

  ID Date Rate

  1 2011-01-01 100.00

  2 2011-02-02 200.00

  3 2011-03-03 300.00

  4 2011-04-04 400.00

  I want the OutPut as Follows

  1 2 3 4

  2011-01-01 2011-02-02 2011-03-03 2011-04-04

  100.00 200.00 300.00 400.00

  Thanks In Advance

  Sachin

• Jeff Moden

  SSC Guru

  Points: 1004683

  More actions

  April 17, 2011 at 4:52 pm

  #1312563

  First, especially since you're relatively new to this forum, please study the article at the first link in my signature line below for how to post data in a readily consumable format. People on this forum tend to want to test their own code solutions before posting and anything you can do to make those tests easier will only benefit you.

  Here's a different way you could have posted your data in a "readily consumable format":

  --===== Conditionally drop, recreate, and populate the test table on the fly.

  -- This is NOT a part of the solution.

  IF OBJECT_ID('tempdb..#TestData','U') IS NOT NULL DROP TABLE #TestData

  ;

  SELECT ID = ID,

  Date = CAST(Date AS DATETIME),

  Rate = Rate

  INTO #TestData

  FROM (

  SELECT 1,'2011-01-01',100.00 UNION ALL

  SELECT 2,'2011-02-02',200.00 UNION ALL

  SELECT 3,'2011-03-03',300.00 UNION ALL

  SELECT 4,'2011-04-04',400.00 UNION ALL

  SELECT 6,'2011-06-06',600.00

  ) d (ID,Date,Rate)

  ;

  With that in mind, I just have to assume that anything hardcoded will be met with a "It's just sample data... I need it to handle any number of dates and ID's". You should say things like that right up front just to keep from ticking people off when you do finally add that requirement. 😉

  So, here's the dynamic SQL to do with the readily consumable data you made above:

  DECLARE @SQL VARCHAR(MAX);

  SELECT @SQL = '

  WITH

  cteSingleGroup AS

  (

  SELECT GroupBy = 1, ID, Date, Rate

  FROM #TestData

  )

  SELECT

  '

  + STUFF(CAST(

  (

  SELECT ','

  + QUOTENAME(ID)+'=MAX(CASE WHEN ID = '+CAST(ID AS VARCHAR(10))

  + ' THEN CONVERT(VARCHAR(10),Date,120) ELSE '''' END)'+CHAR(10)

  FROM #TestData

  ORDER BY ID

  FOR XML PATH(''),TYPE

  )

  AS VARCHAR(MAX)),1,1,' ')

  + ' FROM cteSingleGroup

  GROUP BY GroupBy

  UNION ALL

  SELECT

  '

  + STUFF(CAST(

  (

  SELECT ','

  + QUOTENAME(ID)+'=MAX(CASE WHEN ID = '+CAST(ID AS VARCHAR(10))

  + ' THEN CAST(RATE AS VARCHAR(10)) ELSE '''' END)'+CHAR(10)

  FROM #TestData

  ORDER BY ID

  FOR XML PATH(''),TYPE

  )

  AS VARCHAR(MAX)),1,1,' ')

  + ' FROM cteSingleGroup

  GROUP BY GroupBy'

  ;

  PRINT @SQL

  ;

  EXEC (@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)
  

• sachingovind-742691

  SSC Rookie

  Points: 46

  More actions

  April 18, 2011 at 10:53 am

  #1312959

  Thanks for solution

  sachin

• Jeff Moden

  SSC Guru

  Points: 1004683

  More actions

  April 20, 2011 at 8:45 pm

  #1314285

  sachingovind-742691 (4/18/2011)

  ---

  Thanks for solution

  sachin

  You bet. Thank you for the feedback and, again, welcome aboard.

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