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?