Solved

SQL Transform ... In ()

Posted on 2012-03-26
3
333 Views
Last Modified: 2012-03-26
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.
0
Comment
Question by:APD_Toronto
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 37767082
0
 

Author Comment

by:APD_Toronto
ID: 37767117
so, you're saying build my query string in vba?  interesting.
0
 
LVL 18

Expert Comment

by:lludden
ID: 37767397
You can do it all in SQL using dynamic SQL

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 

Open in new window

0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question