Solved

SQL Transform ... In ()

Posted on 2012-03-26
3
327 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
3 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
0
 

Author Comment

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

Expert Comment

by:lludden
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

772 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now