Use dynamic Pivot table as part of another query

Question

Post reply

Use dynamic Pivot table as part of another query

Luckbox72

SSC Enthusiast

Points: 129
More actions
February 12, 2010 at 6:49 am

#229941

Ok here is the question. I have written a stored procedure that will create a dynamic pivot table. I have also written a query that uses dynamic sql to create a pivot table. They both work but the issue is I now need to use the results as a table in another query. How can I do this.
here is the dynamic sql
DECLARE @listCol VarChar(2000)
DECLARE @query VarChar(max)
SELECT @listCol = Stuff((SELECT DISTINCT
'],[' + StateValue
FROM States
WHERE StateValue <> ''
ORDER BY '],['+ StateValue
FOR XML PATH('')
), 1, 2, '') + ']'
SET @query=
'SELECT *
FROM
(SELECT RepID, State
FROM RepBDState) src
PIVOT (count(State) FOR State IN ('+@listCol+')) AS pvt
Execute (@query)
and the stored proc that creates the same table
CREATE procedure [dbo].[CreateDynamicPivot]
(
@select varchar(2000),
@PivotCol varchar(100),
@Summaries varchar(100)
) as
declare @pivot varchar(max), @sql varchar(max)
select @select =replace(@select,'select ','select '+@PivotCol+' as pivot_col,')
create table #pivot_columns (pivot_column varchar(100))
Select @sql='select distinct pivot_col from ('+@select+') as t'
insert into #pivot_columns
exec(@sql)
select @pivot=coalesce(@pivot+',','')+'['+pivot_column+']'from #pivot_columns
select @sql=
'
select * from
(
'+@select+'
) as t
pivot
(
'+@Summaries+' for pivot_col in ('+@pivot+')
) as p
'
exec(@sql)
so I can get this data but how do I put this in a temp table or use as part of the From statment for another procedure?
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 1

You could change

select @sql=

'

select * from

to

select @sql=

'

select * INTO #TargetTable

from

But make sure to include the existance check including a conditional drop table ...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Luckbox72 SSC Enthusiast Points: 129 More actions · Answer 2

I tried

SET @query=

'SELECT * Into #tempTable FROM

(SELECT RepID, State

FROM RepBDState) src

PIVOT (count(State) FOR State IN ('+@listCol+')) AS pvt'

EXECUTE (@Query)

select * from #tempTable

and when run I get the following

(236 row(s) affected)

Msg 208, Level 16, State 0, Line 21

Invalid object name '#tempTable'.

it's like the #tempTable only exists during the execution.

Craig@Work SSCrazy Points: 2346 More actions · Answer 3

.... just an idea (if I understand the problem correctly?)

Could you ...

- create a table with an uniqueidentifier key column and xml column

- create a new guid and assign to a variable

- pass the guid into your dynamic query

- Use FOR XML to serialize your query results and insert this with your Guid into the above table

- In the non-dynamic query, using your guid varaible, select the xml out of the table and shred it into a result set and return this

..?

LutzM SSC Guru Points: 107049 More actions · Answer 4

Arrgghhh!

My fault!

I totally forgot/overlooked/ignored that the EXEC() command will run under a separate session.. I'M SORRY!!!

One option would be to use a permanent table instead of a temp table. But for sure that's not really an elegant solution...

Another solution might be to use the table data type (new in 2008) as an output parameter. Never used it though... You are using SQL2008, right? (Just to make sure...)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Craig@Work SSCrazy Points: 2346 More actions · Answer 5

Craig@Work

SSCrazy

Points: 2346

February 12, 2010 at 1:47 pm

#1118291

We all have mad moments 😀

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 6

Luckbox72 (2/12/2010)
I tried
SET @query=
'SELECT * Into #tempTable FROM
(SELECT RepID, State
FROM RepBDState) src
PIVOT (count(State) FOR State IN ('+@listCol+')) AS pvt'
EXECUTE (@Query)
select * from #tempTable
and when run I get the following
(236 row(s) affected)
Msg 208, Level 16, State 0, Line 21
Invalid object name '#tempTable'.
it's like the #tempTable only exists during the execution.

I think it works with

INSERT INTO #Temptable EXECUTE (@Query)

but of course you have to create #Temptable beforehand.

@Lutz - that was too funny, man! 😀 Go home, have beer!

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 7

ChrisM@home (2/12/2010)
...
I think it works with
INSERT INTO #Temptable EXECUTE (@Query)
but of course you have to create #Temptable beforehand.
@Lutz - that was too funny, man! 😀 Go home, have beer!

The problem with creating #Temptable up front is that most probably number of columns and column names will be unknown... That's the tricky part...

Btw: I figured the reason for messing up that bad: I'm at home and I had a beer already. Or two...:hehe:

Edit: But according to law it still would be CUI (coding under influence). Not CWI. Yet.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 8

Luckbox72 (2/12/2010)
I tried
SET @query=
'SELECT * Into #tempTable FROM
(SELECT RepID, State
FROM RepBDState) src
PIVOT (count(State) FOR State IN ('+@listCol+')) AS pvt'
EXECUTE (@Query)
select * from #tempTable
and when run I get the following
(236 row(s) affected)
Msg 208, Level 16, State 0, Line 21
Invalid object name '#tempTable'.
it's like the #tempTable only exists during the execution.

Nearly there, just double the hash

SET @query='if object_id(''tempdb..##tempTable'') is not null drop table ##tempTable ;

SELECT * Into ##tempTable FROM

(SELECT RepID, State

FROM RepBDState) src

PIVOT (count(State) FOR State IN ('+@listCol+')) AS pvt'

EXECUTE (@Query)

select * from ##tempTable

You might want to be careful though as this is a global temp table and would be a problem if more than 1 instance of the query was run concurrently.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);