Dynamic sql - Pivot

[747576]

Hi,
I am using SQL Server 2005, I would like to know the best way to dynamically pivot data from a table using a table valued function.

In the code below I have an ID, TYPE, SerialNumber and SEQ column, I would like to pivot the data to give the following example:

ID 4145_1 4143_1 4145_2 4143_2 4145_3
1A 9.15209 17.7517 9.15209 12.7139 22.0298

so the resulting view would include the ID and a column for each SerialNumber + '_' + SEQ

Thanks

CREATE TABLE [RESULT](
    [ID] [varchar](30) NOT NULL,
        [TYPE] [varchar](30) NOT NULL,
        [SerialNumber] [varchar](30) NOT NULL,
        [SEQ] [varchar](30) NOT NULL,
    [NAME] [varchar](30) NOT NULL,
    [VALUE] float
)
GO

INSERT INTO RESULT VALUES('1A','1',4145,'1','Water','9.15209')
INSERT INTO RESULT VALUES('1A','2',4143,'1','Water','17.7517')
INSERT INTO RESULT VALUES('1A','1',4145,'2','Water','9.15209')
INSERT INTO RESULT VALUES('1A','2',4143,'2','Water','12.7139')
INSERT INTO RESULT VALUES('1A','1',4145,'3','Water','22.0298')



GO

SELECT [ID],[1],[2],[3],[4]
FROM
(SELECT [ID], [TYPE],[VALUE]
FROM RESULT) p
PIVOT
(
MIN ([VALUE])
FOR [TYPE] IN
( [1],[2],[3],[4])
) AS pvt

DROP TABLE [RESULT]

 

[markros]

Try

use tempdb 
drop table result
CREATE TABLE [RESULT](
    [ID] [varchar](30) NOT NULL,
        [TYPE] [varchar](30) NOT NULL,
        [SerialNumber] [varchar](30) NOT NULL,
        [SEQ] [varchar](30) NOT NULL,
    [NAME] [varchar](30) NOT NULL,
    [VALUE] float
)
GO

INSERT INTO RESULT VALUES('1A','1',4145,'1','Water','9.15209')
INSERT INTO RESULT VALUES('1A','2',4143,'1','Water','17.7517')
INSERT INTO RESULT VALUES('1A','1',4145,'2','Water','9.15209')
INSERT INTO RESULT VALUES('1A','2',4143,'2','Water','12.7139')
INSERT INTO RESULT VALUES('1A','1',4145,'3','Water','22.0298')

DECLARE @columns nvarchar(MAX);
DECLARE @sql nvarchar(MAX);

SET @columns = STUFF(
(
SELECT CAST(',' AS varchar(MAX)) + QUOTENAME(NewSeq)
FROM (SELECT top 100 percent LTRIM(SerialNumber)+'_' + LTRIM(Seq) as NewSeq FROM Result order by TYPE, SEQ, SerialNumber) AS T
FOR XML PATH('')
), 1, 1, '');

SET @sql = N'
SELECT
	*
FROM
	(SELECT ID, Name, LTRIM(SerialNumber)+ ''_'' + LTRIM(Seq) as NewSeq, Value FROM Result) AS T
	PIVOT
	(
	SUM(Value)
	FOR NewSeq IN (' + @columns + N')
	) AS PVT;';

EXEC sp_executesql @sql;

PluralSight Learning Library