SQL Query needed for PIVOTing - SQL Server Forums

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)

SQL Query needed for PIVOTing

Author

Topic

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-10-27 : 10:09:19

Hello All
I have an intermediate table result which looks like this

ID                  Header                     CONTENT  

GUID 1              Header1                    123456   
GUID 1              Header2                    Phone  
GUID 1              Header3                    Main Street  
GUID 2              Header1                    654321  
GUID 2              Header2                    Phone  
GUID 3              Header3                    Some Street

I need to transform this result set into something like this


ID        Header1    Header2     Header3                      

GUID 1    123456     Phone       Main Street   
GUID 2    654321     Phone       Some Street

I guess i have to use PIVOTing for the same.
But im stuck in the point where i cannot use the COUNT/SUM operator inorder to get the CONTENT column.Can anyone help me with the query for achieving the result ???

Thanks in advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-27 : 10:20:34

[code]
declare @t table(ID varchar(100),Header varchar(100),CONTENT varchar(100))
insert into @t
select 'GUID 1', 'Header1','123456' union all
select 'GUID 1', 'Header2','Phone' union all
select 'GUID 1', 'Header3','Main Street' union all
select 'GUID 2', 'Header1','654321' union all
select 'GUID 2', 'Header2','Phone' union all
select 'GUID 3', 'Header3','Some Street'

select * from
(
select id,header,content from @t
) as t
pivot
(
max(Content) for Header in ([Header1],[Header2],[Header3])
) as p

[/code]

Madhivanan

Failing to plan is Planning to fail

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-10-27 : 10:34:25

Hello Madhavan
i got the hint from your query
i just had to use MAX keyword ...
Thanks a lot ..
Srivatsa

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-10-27 : 10:45:31

Also, can we use Subqueries inside the PIVOT ???

i.e i want to use something like this

pivot
(
	max(Content) for Header in (Select Header from HeaderTable)
) as p

The above query returns error, i just need to know whether such a case is possible to select entries dynamically from a select statement

Regards
Srivatsa

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-27 : 11:06:37

no, you cant do that. You need a dynamic pivot if you don't know the eventual columns at design time:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=123085

Be One with the Optimizer
TG

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-10-27 : 11:41:32

Thanks
I was able to do it.
But i have one more issue now, Im not sure whether i should create a new topic for the same or include in this topic.

I need to export the so formed data into an excel file. The problem is the SQL Import/Export Wizard is not able to parse the dynamically generated QUERY, since it says i cannot allow DECLARE statements.

Also i tried to create a view ,but in vain..

Any help in exporting the resulting data into the excel format is deeply appreciated.

Regards
Srivatsa

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-27 : 11:54:48

Excel has its own pivot functionality. Can't you just link to the raw table and do the pivot in excel? Otherwise perhaps use the sql pivot statement as a SELECT INTO statement to create a pivoted table.

Be One with the Optimizer
TG

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-10-28 : 04:30:52

TG
Thanks for replying.

Actually i know that excel has inbuilt PIVOT functionality but i cannot use the same. Since, my requirement is to to automate the process of pulling the data from SQL and then exporting it to excel/csv.

I tried the following options to export in vain.

Export using Views :Creating a view with the query which does the pivoting dynamically, SQL views do not allow DECLARE

SQL Export using Query : Query parse fails to parse the DECLARE statements

Importing the data from SQL into Excel: Fail

Using : bcp, this returns Query successful, but does not export the data for some reason

Any more options you would suggest

Regards
Srivatsa

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-28 : 10:24:22

I'm not really an excel guy but I would think this should work:

- (in excel) link to external data which is your raw, unpivoted data in sql.
- (in different worksheet in excel) set up your pivot table using the (linked) data in step one.

Then whenever you want to see the current data just "refresh". I think you can set the properties of the excel file to either automatically refresh or not.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources