PIVOT in sql 2005

By : IZar
Source: Stackoverflow.com
Question!

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

By : IZar


Answers

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)
By : Cade Roux


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.

By : Booji Boy


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.



This video can help you solving your question :)
By: admin