views:

659

answers:

1

IN SQL Server 2008 I have a situation where I need to return a dynamically generated table, and the columns are also dynamically generated.

Everything in this was generated by queries, as I started with one id, then I get the column names and types, which is why the casting.

Following is the final query, that will return the table I want, in one situation, but this similar query can be used to return several tables.

This is currently implemented in C# but I expect it should be doable in a stored procedure or function, but, I am not certain how I can make several queries to build up this query, to then return the table.

Thank you.

SELECT ResultID, ResultName, ResultDescription, 
CAST([233] AS Real) as [ResultColA], 
CAST([234] AS Int) as [ResultColB], 
CAST([236] AS NVarChar) as [ResultColC], 
CAST([237] AS Int) as [ResultColD]
FROM (
    SELECT st.*, avt.ResultID as avtID, avt.SomeAttrID, avt.Value 
    FROM Result_AV avt 
    JOIN Result st ON avt.ResultID = st.ResultID) as p 
    PIVOT ( 
     MAX(Value) FOR AttributeID IN ([233], [234], [236], [237])) as pvt

Update: If I have a table that has manufacturer of vehicles, and I want all the attributes of cars made by GM. I would look up the information for cars, use that to get all the manufacturers of cars, which would be GM, then I want to get the information of all the cars made by GM, but, since the information has been dynamically generated the columns are different. That is the place I am at, needing to dynamically generate this table. I am curious if I can perhaps just call out to a webservice, get the dynamically generated query and then have the stored procedure or function execute that query.

Update 2: The next step after I get this table is that I will need to add up all the options on the car (in this case), to determine the actual price of the car. That is why it needs to be in a tabular format. At this point I am looking at cars made by GM, but I could do another query on boats made by SeaRay and the query should work the same even though the table columns on the boats are different than the cars were.

A: 

You can do this with dynamic SQL. You need to build your two lists appropriately with a query and/or metadata and then insert them into the SQL:

DECLARE @template AS varchar(MAX)

SET @template = '
SELECT ResultID, ResultName, ResultDescription
{@SELECT_COLUMN_LIST}
FROM (
    SELECT st.*, avt.ResultID as avtID, avt.SomeAttrID, avt.Value 
    FROM Result_AV avt 
    JOIN Result st ON avt.ResultID = st.ResultID) as p 
    PIVOT ( 
        MAX(Value) FOR AttributeID IN ({@PIVOT_COLUMN_LIST})) as pvt'

DECLARE @sql AS varchar(MAX)

SET @sql = REPLACE(REPLACE(@template, '{@SELECT_COLUMN_LIST}', @SELECT_COLUMN_LIST), '{@PIVOT_COLUMN_LIST}', @PIVOT_COLUMN_LIST)

EXEC (@sql)

To populate the @SELECT_COLUMN_LIST and @PIVOT_COLUMN_LIST, you've got several options, but something like this avoids any need for a cursor or awkward string operations:

SELECT @PIVOT_COLUMN_LIST = COALESCE(@PIVOT_COLUMN_LIST, '') + ',' + QUOTENAME(value)
FROM (SELECT DISTINCT value FROM table)

Obviously for @SELECT_COLUMN_LIST you need to combine this information with some metadata.

I have done things like that for ease of maintenance because PIVOT and UNPIVOT do not allow lists to be dynamic SQL (because they result in a schema change on the output table). For for client-side report-style or listbox usage where the columns aren't interpreted, it doesn't matter.

Cade Roux
Thank you very much. I am struggling with understanding how to use such a dynamic database. :)
James Black
Generally, it's a bad idea to try to build a database within the database, but dynamic SQL has its places - typically a very small portion of any system, if at all.
Cade Roux