How to store output of PIVOT command into a Table.

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

SQL Server 2005 Forums

Transact-SQL (2005)

How to store output of PIVOT command into a Table.

Author

Topic

mylovelyfriend
Starting Member

4 Posts

Posted - 2010-07-25 : 00:19:30

Hi
I am using SQL2005 and I am dynamically generating PIVOT SQL Statement and then using Execute command. e.g (as below)

What i want is to be able to store the output into a Dynamic Table to be able to further write querys on it.
Even a View will do, but the Question is how to create View from
EXECUTE(@PivotTableSQL) command.... Possible ??


DECLARE @List nvarchar(max)
Select @List = Stuff(  ( Select Distinct ',[' + S.QID + ']'  From ReportResponsesView S  for xml path('') ) , 1,1,'')
Print @List

DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'

Select * From (
Select QID, ChoiceTitle, Count(ChoiceTitle)*100  / (Select Count(*) From ReportResponsesView R Where R.Qid=A.Qid) as XX,
Process, SubProcess
From ReportResponsesView A Group by QID, ChoiceTitle, Process , SubProcess  )  T1
Pivot( Sum(XX) for Qid in (' +  @List + ')  ) as PVT
Order by ChoiceTitle, Process, SubProcess '

EXECUTE(@PivotTableSQL)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-25 : 04:05:51

you can insert the output into a temp table


select . .  
into   #temptable

KH
[spoiler]Time is always against us[/spoiler]

mylovelyfriend
Starting Member

4 Posts

Posted - 2010-07-25 : 10:14:13

Sorry Khtan, I could not understand your clue. do you mean like

"Select EXECUTE(@PivotTableSQL)
into #temptable " ??

Regards

quote:
Originally posted by khtan

you can insert the output into a temp table

select . .
into #temptable

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-25 : 10:18:57

you will need to use global temp table, unless you can pre-create the temp table


SET @PivotTableSQL = N'

Select * INRTO ##temptable From (
Select QID, ChoiceTitle, Count(ChoiceTitle)*100  / (Select Count(*) From ReportResponsesView R Where R.Qid=A.Qid) as XX,
Process, SubProcess
From ReportResponsesView A Group by QID, ChoiceTitle, Process , SubProcess  )  T1
Pivot( Sum(XX) for Qid in (' +  @List + ')  ) as PVT
Order by ChoiceTitle, Process, SubProcess '

EXECUTE(@PivotTableSQL)

KH
[spoiler]Time is always against us[/spoiler]

Sachin.Nand

2937 Posts

Posted - 2010-07-25 : 11:30:28

But Khtan solution uses a global temporary table which will be common for all user instances.
One way is you can create a linked server


exec sp_addlinkedserver @server = N'test',
     @srvproduct = N'',
     @provider = N'SQLOLEDB', 
     @datasrc = @@servername

and then use a OPENQUERY to


SELECT * into #temp  
FROM OPENQUERY (test,'SET FMTONLY OFF;EXECUTE [YourDB].[dbo].[YourSp]'YourParamters')

Make sure that RPC is set true for the linked server.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

mylovelyfriend
Starting Member

4 Posts

Posted - 2010-07-25 : 12:36:00

Hi Idera,

I liked Select * into #Temp from OpenQuery.... but can i have such statement without need of linked server?

ELSE

Khtan suggestion is nice, but global temporary table might not fit my requirement, but if its the only thing the i have no choice then to live with it.

Also i am Quite surprise with the limitation of PIVOT statement
"Pivot( Sum(XX) for Qid in (' + @List + ') ) as PVT"
that For Qid in .. does not accept a subquery and it expects a static list of values. Is this a limitation or there is a better workaround for it ?

Regards

Sachin.Nand

2937 Posts

Posted - 2010-07-25 : 13:24:05

quote:
Originally posted by mylovelyfriend

I liked Select * into #Temp from OpenQuery.... but can i have such statement without need of linked server?

I dont think so that is possible not that I know of.Any particular reason you cannot use a linked server?

quote:

Also i am Quite surprise with the limitation of PIVOT statement
"Pivot( Sum(XX) for Qid in (' + @List + ') ) as PVT"
that For Qid in .. does not accept a subquery and it expects a static list of values. Is this a limitation or there is a better workaround for it ?

Didn't Quite understand what you are trying to say.Please post some sample data & expected o/p .

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-26 : 09:09:08

<<
that For Qid in .. does not accept a subquery and it expects a static list of values. Is this a limitation or there is a better workaround for it ?
>>

Yes. It is a limitation

Madhivanan

Failing to plan is Planning to fail

mylovelyfriend
Starting Member

4 Posts

Posted - 2010-07-26 : 13:12:56

Just that client may not allow to create any object in their sqlserver without intent and explaination.
However temp table i can create. Anyways ,Thanks for nice clues and help.

A Note:
MSFT really need to make improvements on their PIV

T command.
The command remains unmature, If it forces one to give static list of values and does not allow one to write 'subquery'.
Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources