Dynamic sql

[747576]

Hi,
I have a normalised table called RESULTS, which contains water values.
I would like to pivot the values so there is a column for each type. I am able to do this with sql below and the result is:

ID Water_1 Water_2 Water_3
1A 0.01 0.01 0.01
1A 0.013 0.016 0.011
1A 0.012 0.015 0.017
1A 0.014 0.014 0.014

I would like to know how ro use dynamic sql to automatically create the min case statements as there could be any number of unique TYPE values i.e. at the moment the code will create min case statements for TYPE's 1 to 3, but theis could be 1 to 4 or more.

Thank you

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

INSERT INTO RESULT VALUES('1A','1',2,'Water','0.01')
INSERT INTO RESULT VALUES('1A','1',3,'Water','0.013')
INSERT INTO RESULT VALUES('1A','1',4,'Water','0.012')
INSERT INTO RESULT VALUES('1A','1',5,'Water','0.014')
INSERT INTO RESULT VALUES('1A','2',2,'Water','0.01')
INSERT INTO RESULT VALUES('1A','2',3,'Water','0.016')
INSERT INTO RESULT VALUES('1A','2',4,'Water','0.015')
INSERT INTO RESULT VALUES('1A','2',5,'Water','0.014')
INSERT INTO RESULT VALUES('1A','3',2,'Water','0.01')
INSERT INTO RESULT VALUES('1A','3',3,'Water','0.011')
INSERT INTO RESULT VALUES('1A','3',4,'Water','0.017')
INSERT INTO RESULT VALUES('1A','3',5,'Water','0.014')
INSERT INTO RESULT VALUES('1A','4',2,'Water','0.01')
INSERT INTO RESULT VALUES('1A','4',3,'Water','0.011')
INSERT INTO RESULT VALUES('1A','4',4,'Water','0.019')
INSERT INTO RESULT VALUES('1A','4',5,'Water','0.014')


GO



Declare @name VarChar(30)
Declare @SQL VarChar(MAX)

set @name = 'Water'

Select @SQL = '

SELECT
    A.ID'

Select @SQL = @SQL + ', min(CASE when [A].[NAME] = '''+@name+''' and [A].[TYPE] = ''1'' then [A].[VALUE] ELSE NULL END) as ['+ @name +'_1]'
Select @SQL = @SQL + ', min(CASE when [A].[NAME] = '''+@name+''' and [A].[TYPE] = ''2'' then [A].[VALUE] ELSE NULL END) as ['+ @name +'_2]'
Select @SQL = @SQL + ', min(CASE when [A].[NAME] = '''+@name+''' and [A].[TYPE] = ''3'' then [A].[VALUE] ELSE NULL END) as ['+ @name +'_3]'

Select @SQL = @SQL + '
FROM (
SELECT
    S.ID
    , S.TYPE
	, S.[DEPTH]
    , S.[NAME]
    , S.[VALUE]
    FROM RESULT S
WHERE S.[NAME] IN('''+@name+''')
)A
GROUP BY A.ID,A.[DEPTH]

'

exec ( @SQL)



DROP TABLE [RESULT]

 

[Jamfool]

You dont need dynamic sql to pivot. Best to use the pivot commands except in a few circumstances.

You may need to write some dyanmic sql if you have an unknown number of [type]'s to pivot. Although you can add in type 5,6,7 etc easily to the example below.

http://msdn.microsoft.com/en-us/library/ms177410.aspx

Below a quick example:

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

INSERT INTO RESULT VALUES('1A','1',2,'Water','0.01')
INSERT INTO RESULT VALUES('1A','1',3,'Water','0.013')
INSERT INTO RESULT VALUES('1A','1',4,'Water','0.012')
INSERT INTO RESULT VALUES('1A','1',5,'Water','0.014')
INSERT INTO RESULT VALUES('1A','2',2,'Water','0.01')
INSERT INTO RESULT VALUES('1A','2',3,'Water','0.016')
INSERT INTO RESULT VALUES('1A','2',4,'Water','0.015')
INSERT INTO RESULT VALUES('1A','2',5,'Water','0.014')
INSERT INTO RESULT VALUES('1A','3',2,'Water','0.01')
INSERT INTO RESULT VALUES('1A','3',3,'Water','0.011')
INSERT INTO RESULT VALUES('1A','3',4,'Water','0.017')
INSERT INTO RESULT VALUES('1A','3',5,'Water','0.014')
INSERT INTO RESULT VALUES('1A','4',2,'Water','0.01')
INSERT INTO RESULT VALUES('1A','4',3,'Water','0.011')
INSERT INTO RESULT VALUES('1A','4',4,'Water','0.019')
INSERT INTO RESULT VALUES('1A','4',5,'Water','0.014')


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

DROP TABLE [RESULT]