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.