Greg,
You can build a select command as a string and than execute it using T-SQL command EXECUTE or system stored procedure sp_executesql.
>I have a situation where I want to generate a crosstab table, via a Select command, where the output will compose of a separate column for each item the selected Vendor supplies. Each vendor supplies a different number of items.
>
>The following example is the code I wrote, but it is not re-usable because it is specific to one individual vendor.
>
>SELECT
>[ORD_ID],
>(SUM(CASE [ITEM_NO] WHEN 'FRS001' THEN [LIN_QTY_ORD] ELSE 0 END) AS FR_1,
>(SUM(CASE [ITEM_NO] WHEN 'FRS002' THEN [LIN_QTY_ORD] ELSE 0 END) AS FR_2,
>(SUM(CASE [ITEM_NO] WHEN 'FRS003' THEN [LIN_QTY_ORD] ELSE 0 END) AS FR_3
>FROM vw_OpenDIPOs
>WHERE [VEND_NO] = 'DGNI06'
>GROUP BY [ORD_ID]
>
>
>I've been looking at solving this in two different ways, but can't seem to "break on through to the other side."
>
>First, I tried to set the "AS column" clause of the Select command to "AS --Select item_desc from table where item_no=table.item_no--)", but TSQL does not except my logic.
>
>Second, set up a cursor and create a temporary table dynamically via a cursor (generated from the a select against item file for the target vendor.) In BOL, I haven't seen a way to do this yet.
>
>Any suggestions?
--sb--