Information générale
Forum:
Microsoft SQL Server
Titre:
Dynamic Select Column Creation
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?
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement