PIVOT in sql 2005
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.
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
user2758501
Updated on July 30, 2022Comments
-
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 about 15 yearsTom, I don't think this will work for me because each group could have different amount of values.
-
user2758501 about 15 yearsBB thanks for replying. but this does not work for me. i need my columns to be dynamic.
-
BoltBait about 15 yearsYou 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 about 15 yearsI 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.