Using SQL Pivot table results in MS Access

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Using SQL Pivot table results in MS Access

Author

Topic

Angelflower
Starting Member

3 Posts

Posted - 2013-10-07 : 15:49:08

I have query that produces a pivot table (see below). I want to use those results that in a MS Access report. Normally I would use a view for this but I can't make it a view because of the #temp table part of the code. I tried to paste the code directly into a pass-through query but it errors out and tells me that "Pass-through query with ReturnsRecords property set to True did not return any records". When I run the code in SQL I get results.

Here is my pivot table:

Select
tbl_invoice_chart.ChartKey
, tbl_invoice_chart.start_of_coverage_date as Variable
, tbl_invoice_chart.SumOfqty_adj as VaribleValue

into #temp123

from tbl_invoice_chart
where tbl_invoice_chart.ChartKey not like '%EA%'
order by tbl_invoice_chart.start_of_coverage_date asc

DECLARE @columns VARCHAR(8000)
SELECT @columns = COALESCE(@columns + ',[' + cast(Variable as varchar) + ']',
'[' + cast(Variable as varchar)+ ']')

FROM #temp123
GROUP BY Variable
order by #temp123.variable asc

DECLARE @query VARCHAR(8000)
SET @query = '

SELECT *
FROM #temp123

PIVOT
(
MAX(VaribleValue)
FOR [Variable]
IN (' + @columns + ')
)
AS p'

EXECUTE(@query)

DROP TABLE #temp123

Sample output

Column headings:
ChartKey
Feb 1 2013 12:00AM
Mar 1 2013 12:00AM
Apr 1 2013 12:00AM
May 1 2013 12:00AM
Jun 1 2013 12:00AM

Row data:
5DZG03751Firm Gas951952
966.61
892.96
458.53
48144.40
0581.97
81

Live well... be happy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources