PIVOT in sql 2005

I need to pivot one column (Numbers column). example need this data:

a 1
a 2
b 3
b 4
c 5
d 6
d 7
d 8
d 9
e 10
e 11
e 12
e 13
e 14

Look like this

a 1 2
b 3 4
c 5
d 6 7 8 9
e 10 11 12 13 14

any help would be greatly appreciated...

From stackoverflow IZar

• This related question should have the answer you need: http://stackoverflow.com/questions/24470/sql-server-pivot-examples

  A Matrix control in SSRS has dynamic columns, if this data is bound for a report anyways then you could use that. Otherwise you'll have to create a sql sproc that generates the sql like in the exaamples dynamicly and then executes it.

  IZar : BB thanks for replying. but this does not work for me. i need my columns to be dynamic.

  BoltBait : You can overcome that problem by using dynamic SQL. Dynamic problem use dynamic SQL! :) (I've done it before with dates and it worked well.)
  From Booji Boy

• Just because I wanted to get some more experience with CTEs, I came up with the following:

  WITH CTE(CTEstring, CTEids, CTElast_id)
  AS
  (
      SELECT string, CAST(id AS VARCHAR(1000)), id
      FROM dbo.Test_Pivot TP1
      WHERE NOT EXISTS (SELECT * FROM dbo.Test_Pivot TP2 WHERE TP2.string = TP1.string AND TP2.id < TP1.id)
      UNION ALL
      SELECT CTEstring, CAST(CTEids + ' ' + CAST(TP.id AS VARCHAR) AS VARCHAR(1000)), TP.id
      FROM dbo.Test_Pivot TP
      INNER JOIN CTE ON
       CTE.CTEstring = TP.string
      WHERE
       TP.id > CTE.CTElast_id AND
       NOT EXISTS (SELECT * FROM dbo.Test_Pivot WHERE string = CTE.CTEstring AND id > CTE.CTElast_id AND id < TP.id)
  )
  SELECT
      t1.CTEstring, t1.CTEids
  FROM CTE t1
  INNER JOIN (SELECT CTEstring, MAX(LEN(CTEids)) AS max_len_ids FROM CTE GROUP BY CTEstring) SQ ON SQ.CTEstring = t1.CTEstring AND SQ.max_len_ids = LEN(t1.CTEids)
  ORDER BY CTEstring
  GO
  

  It might need some tweaking, but it worked with your example

  IZar : Tom, I don't think this will work for me because each group could have different amount of values.

  Tom H. : I don't see how that would affect things, unless the length of the string went over 1000, which you can handle by just increasing the size of the VARCHAR. The CTE is recursive, not really limited by a number.
  From Tom H.

• The coalesce function could also be used here, similar to other questions that have been asked about concatenating data.

  http://stackoverflow.com/questions/6899/is-there-a-way-to-create-a-mssql-function-to-join-multiple-rows-from-a-subquery#6924

  From Eric Z Beard

• I'm not sure that what you're doing is really possible (or at least practical) in SQL - I'm not sure, because I'm still not exactly sure what you want to do.

  You could build that pivot table in your client application, for example with:

  select distinct Letter from MyTable
  

  to get the list of letters, and then use a parameterized query inside a loop:

  select Number from MyTable where Letter=:letter
  

  to get the list of numbers for each letter.

  From Blorgbeard

• Using ROW_NUMBER(), PIVOT and some dynamic SQL (but no cursor necessary) :

  CREATE TABLE [dbo].[stackoverflow_198716](
      [code] [varchar](1) NOT NULL,
      [number] [int] NOT NULL
  ) ON [PRIMARY]
  
  DECLARE @sql AS varchar(max)
  DECLARE @pivot_list AS varchar(max) -- Leave NULL for COALESCE technique
  DECLARE @select_list AS varchar(max) -- Leave NULL for COALESCE technique
  
  SELECT @pivot_list = COALESCE(@pivot_list + ', ', '') + '[' + CONVERT(varchar, PIVOT_CODE) + ']'
       ,@select_list = COALESCE(@select_list + ', ', '') + '[' + CONVERT(varchar, PIVOT_CODE) + '] AS [col_' + CONVERT(varchar, PIVOT_CODE) + ']'
  FROM (
      SELECT DISTINCT PIVOT_CODE
      FROM (
       SELECT code, number, ROW_NUMBER() OVER (PARTITION BY code ORDER BY number) AS PIVOT_CODE
       FROM stackoverflow_198716
      ) AS rows
  ) AS PIVOT_CODES
  
  SET @sql = '
  ;WITH p AS (
      SELECT code, number, ROW_NUMBER() OVER (PARTITION BY code ORDER BY number) AS PIVOT_CODE
      FROM stackoverflow_198716
  )
  SELECT code, ' + @select_list + '
  FROM p
  PIVOT (
      MIN(number)
      FOR PIVOT_CODE IN (
          ' + @pivot_list + '
      )
  ) AS pvt
  '
  
  PRINT @sql
  
  EXEC (@sql)
  

  From Cade Roux