How to dynamically generate table column definitions in a SQL Server 2008 function
In SQL Server 2008 I am facing a situation where I need to return a dynamically generated table and the columns are dynamically generated as well.
Everything in this was created by queries, since I started with one id, then I get the names and types of the columns, so casting.
Below is the final query that will return the table I want in one situation, but this similar query can be used to return multiple tables.
This is currently implemented in C #, but I expect it to be doable in a stored procedure or function, but I'm not sure how I can make multiple queries to create this query so that I can then return the table.
Thanks.
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 a vehicle manufacturer in it and I want all the vehicle attributes to be done by GM. I would like to find information for cars, use this to get all car manufacturers that would be GM, then I want to get information about all cars made by GM, but since the information was dynamically generated, the columns are different. This is where I am, need to dynamically generate this table. I'm curious if I can just call a web service, get a dynamically generated request, and then execute a stored procedure or function.
Update 2: The next step after getting this table is that I will need to add all the options on the vehicle (in this case) to determine the actual price of the vehicle. This is why it must be in tabular format. At the moment I am looking at cars made by GM, but I could make another query on yachts made by SeaRay and the query should work the same even if the columns on boats are different from cars.
a source to share
You can do this with dynamic SQL. You need to build your two lists correctly with query and / or metadata and then insert them into 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 fill in @SELECT_COLUMN_LIST
and @PIVOT_COLUMN_LIST
, you have multiple parameters, but something like this avoids the need for cursors or awkward string operations:
SELECT @PIVOT_COLUMN_LIST = COALESCE(@PIVOT_COLUMN_LIST, '') + ',' + QUOTENAME(value)
FROM (SELECT DISTINCT value FROM table)
Obviously, @SELECT_COLUMN_LIST
you need to combine this information with some metadata.
I did things like this for maintainability because PIVOT and UNPIVOT do not allow lists to be dynamic SQL (because they cause a schema change in the output table). For use in a client-side report style, or use of a list where no columns are interpreted, this does not matter.
a source to share