PIVOT in sql 2005

13,595

Solution 1

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)

Solution 2

This related question should have the answer you need: SQL Server: Examples of PIVOTing String data

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.

Solution 3

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

How to create a SQL Server function to "join" multiple rows from a subquery into a single delimited field?

Solution 4

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

Share:
13,595
user2758501
Author by

user2758501

Updated on July 30, 2022

Comments

  • user2758501
    user2758501 over 1 year

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

  • user2758501
    user2758501 about 15 years
    Tom, I don't think this will work for me because each group could have different amount of values.
  • user2758501
    user2758501 about 15 years
    BB thanks for replying. but this does not work for me. i need my columns to be dynamic.
  • BoltBait
    BoltBait about 15 years
    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.)
  • Tom H
    Tom H about 15 years
    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.