dynamic sql

[747576]

Hi,

I am using sql 2005.

I would like some help with select data from 3 normalised tables, but dynamically.

I have 3 tables HEADER, RESULT and TYPE. The TYPE table is like a reference table for the NAME field in RESULT.

I want to select the results from the HEADER and RESULT tables but pivot the data to a flat view.

Below is the sql to create the test data:

CREATE TABLE [HEADER](
	[ID] [varchar](30) NOT NULL,
	[IDTYPE] [varchar](10) NULL
)
GO

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

CREATE TABLE [TYPE](
	[NAME] [varchar](30) NOT NULL
)
GO

INSERT INTO [TYPE] VALUES('type1')
INSERT INTO [TYPE] VALUES('type2')
INSERT INTO [TYPE] VALUES('type3')
GO

INSERT INTO HEADER VALUES('1A','FIRST')
INSERT INTO HEADER VALUES('1B','SECOND')
INSERT INTO HEADER VALUES('1C','THIRD')
INSERT INTO HEADER VALUES('2A','FIRST')
INSERT INTO HEADER VALUES('2B','SECOND')
INSERT INTO HEADER VALUES('2C','THIRD')
INSERT INTO HEADER VALUES('3A','FIRST')
INSERT INTO HEADER VALUES('3B','SECOND')
INSERT INTO HEADER VALUES('3C','THIRD')
GO

INSERT INTO RESULT VALUES('1A','type1','0.01')
INSERT INTO RESULT VALUES('1A','type2','0.06')
INSERT INTO RESULT VALUES('1A','type3','0.04')
INSERT INTO RESULT VALUES('1B','type1','0.56')
INSERT INTO RESULT VALUES('1B','type2','0.06')
INSERT INTO RESULT VALUES('1B','type3','0.61')
INSERT INTO RESULT VALUES('1C','type1','0.01')
INSERT INTO RESULT VALUES('1C','type2','0.56')
INSERT INTO RESULT VALUES('1C','type3','0.01')
INSERT INTO RESULT VALUES('2A','type1','0.01')
INSERT INTO RESULT VALUES('2A','type2','0.06')
INSERT INTO RESULT VALUES('2A','type3','0.31')
INSERT INTO RESULT VALUES('2B','type1','0.01')
INSERT INTO RESULT VALUES('2B','type2','0.46')
INSERT INTO RESULT VALUES('2B','type3','0.01')
INSERT INTO RESULT VALUES('2C','type1','1.01')
INSERT INTO RESULT VALUES('2C','type2','0.06')
INSERT INTO RESULT VALUES('2C','type3','2.01')
GO

This code will produec the view I am looking for, where data is pivoted:

SELECT
	A.ID
	, min(CASE when [A].[NAME] = 'type1' and [A].[IDTYPE] = 'FIRST' then [A].[VALUE] ELSE NULL END) as [type1_FIRST]
	, min(CASE when [A].[NAME] = 'type1' and [A].[IDTYPE] = 'SECOND' then [A].[VALUE] ELSE NULL END) as [type1_SECOND]
	, min(CASE when [A].[NAME] = 'type1' and [A].[IDTYPE] = 'THIRD' then [A].[VALUE] ELSE NULL END) as [type1_THIRD]

FROM (
SELECT 
	LEFT(S.ID,LEN(S.ID)-1) AS ID
	, S.IDTYPE
	, CS.[NAME]
	, CS.[VALUE] 
	FROM HEADER S LEFT JOIN RESULT CS 
		ON S.ID = CS.ID
WHERE CS.[NAME] IN( 'type1') 
)A
GROUP BY A.ID

below is the same query but with another set of 'min' statements for the 'type2' NAME's (stored in the reference table TYPE),
I'd like to dynamically look at the TYPE table and build the select statement (for all type1, type2, type3 etc)

SELECT
	A.ID
	, min(CASE when [A].[NAME] = 'type1' and [A].[IDTYPE] = 'FIRST' then [A].[VALUE] ELSE NULL END) as [type1_FIRST]
	, min(CASE when [A].[NAME] = 'type1' and [A].[IDTYPE] = 'SECOND' then [A].[VALUE] ELSE NULL END) as [type1_SECOND]
	, min(CASE when [A].[NAME] = 'type1' and [A].[IDTYPE] = 'THIRD' then [A].[VALUE] ELSE NULL END) as [type1_THIRD]

	, min(CASE when [A].[NAME] = 'type2' and [A].[IDTYPE] = 'FIRST' then [A].[VALUE] ELSE NULL END) as [type2_FIRST]
	, min(CASE when [A].[NAME] = 'type2' and [A].[IDTYPE] = 'SECOND' then [A].[VALUE] ELSE NULL END) as [type2_SECOND]
	, min(CASE when [A].[NAME] = 'type2' and [A].[IDTYPE] = 'THIRD' then [A].[VALUE] ELSE NULL END) as [type2_THIRD]


FROM (
SELECT 
	LEFT(S.ID,LEN(S.ID)-1) AS ID
	, S.IDTYPE
	, CS.[NAME]
	, CS.[VALUE] 
	FROM HEADER S LEFT JOIN RESULT CS 
		ON S.ID = CS.ID
WHERE CS.[NAME] IN( 'type1','type2') 
)A
GROUP BY A.ID

 

[747576]

Hi,
I was able to build a dynamic sql script to query the data, I am passing the variable @type.
This is fine, but is it possible to use the script in a table function, I tried it and recieved the following error:

"An INSERT statement cannot contain a SELECT statement that assigns values to a variable."

Thank you

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

set @type = 'type1'

Select @SQL = '

SELECT
	A.ID'

Select @SQL = @SQL + ', min(CASE when [A].[NAME] = '''+@type+''' and [A].[IDTYPE] = ''FIRST'' then [A].[VALUE] ELSE NULL END) as ['+ @type +'_FIRST]'
Select @SQL = @SQL + ', min(CASE when [A].[NAME] = '''+@type+''' and [A].[IDTYPE] = ''SECOND'' then [A].[VALUE] ELSE NULL END) as ['+ @type +'_SECOND]'
Select @SQL = @SQL + ', min(CASE when [A].[NAME] = '''+@type+''' and [A].[IDTYPE] = ''THIRD'' then [A].[VALUE] ELSE NULL END) as ['+ @type +'_THIRD]'

Select @SQL = @SQL + '
FROM (
SELECT 
	LEFT(S.ID,LEN(S.ID)-1) AS ID
	, S.IDTYPE
	, CS.[NAME]
	, CS.[VALUE] 
	FROM HEADER S LEFT JOIN RESULT CS 
		ON S.ID = CS.ID
WHERE CS.[NAME] IN('''+@type+''') 
)A
GROUP BY A.ID

'

exec ( @SQL)