APD Toronto
asked on
SQL Transform ... In ()
Hello Experts,
would it possible to fix the following syntax to get the IN Values from a table (my last line)?
TRANSFORM Nz(Sum([fldSales]),0) AS ProdSales
SELECT BBSales.SalesLocation, BBSales.SalesDate
FROM tblProducts INNER JOIN (BBSales INNER JOIN tblProdSales ON BBSales.SalesID = tblProdSales.fldSalesID) ON tblProducts.fldProdID = tblProdSales.fldProdID
GROUP BY BBSales.SalesLocation, BBSales.SalesDate
PIVOT tblProducts.fldName
IN (SELECT fldName FROM tblProducts WHERE fldArchived = False);
with this syntax I'm getting "SELECT fldName FROM tblProducts WHERE fldArchived = False" as the column heading, which isnt obviosly what I'm after.
would it possible to fix the following syntax to get the IN Values from a table (my last line)?
TRANSFORM Nz(Sum([fldSales]),0) AS ProdSales
SELECT BBSales.SalesLocation, BBSales.SalesDate
FROM tblProducts INNER JOIN (BBSales INNER JOIN tblProdSales ON BBSales.SalesID = tblProdSales.fldSalesID) ON tblProducts.fldProdID = tblProdSales.fldProdID
GROUP BY BBSales.SalesLocation, BBSales.SalesDate
PIVOT tblProducts.fldName
IN (SELECT fldName FROM tblProducts WHERE fldArchived = False);
with this syntax I'm getting "SELECT fldName FROM tblProducts WHERE fldArchived = False" as the column heading, which isnt obviosly what I'm after.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can do it all in SQL using dynamic SQL
Here is template code for an example.
Here is template code for an example.
CREATE TABLE #T1 (ID int, Event char(1), Value int)
INSERT INTO #T1 VALUES (1,'A',7)
INSERT INTO #T1 VALUES (1,'M',5)
INSERT INTO #T1 VALUES (1,'T',6)
INSERT INTO #T1 VALUES (2,'A',2)
INSERT INTO #T1 VALUES (2,'M',1)
INSERT INTO #T1 VALUES (3,'T',4)
INSERT INTO #T1 VALUES (3,'R',1)
DECLARE @PivotColumnHeaders varchar(MAX)
SELECT @PivotColumnHeaders =
COALESCE(
@PivotColumnHeaders + ',[' + UC.Event + ']',
'[' + Event + ']'
)
FROM (SELECT Event FROM #T1 GROUP BY Event) UC
DECLARE @PQuery varchar(MAX) = '
SELECT * FROM (SELECT ID, Event, Value FROM #T1 T0) T1
PIVOT (SUM([value]) FOR Event IN (' + @PivotColumnHeaders + ') ) AS P'
EXECUTE (@PQuery)
DROP TABLE #T1
ASKER