Connect Excel Pivot Table to SQL Server Stored Procedure

Friends,
Hi ...
I have been connecting Excel pivot tables to SQL server tables and views
through an ODBC DSN connection.
I know there is a way to connect a pivot table to a SQL Server stored
procedure, but can't figure it out. The Excel pivot table connection wizard
doesn't seem to offer stored procedures as one of the connection choices.
Anyone out there know how to do this?
Thanks, Bill Morgan
Thanks ...
Hi, again ...
Got answer from SQL Server programmer, and was able to connect Excel pivot
table to SQL Server stored procedure:
In Excel, go to Data \ Get External Data \ New Database Query. Choose SQL
Server connection and then select the appropriate DSN connection to your SQL
Server (and in the DSN options, be sure to set the DSN default to the right
database).
Follow the dialog box prompts. You may not see the right tables/views to
choose from, but select any table in order to keep going through the
successive dialog boxes. When given the choice, you want to "View Data or
Edit Query in Microsoft Query" (dialog box with 3 radio buttons - you want
the middle button in Excel 2000)
When you get to the end of the dialog boxes, go into View/SQL (or click on
SQL icon on the toolbar) and delete the SQL code that is in the SQL box. In
it's place, type EXEC your_database_name.Owner.your_stored_procedure_nam e.
Click OK when the dialog box says you cannot view your query graphically.
Once you see the query result (rows and columns of data) go to File \ Export
results to Excel. Then be sure to check the Pivot Table radio button on the
dialog box that comes up.
The pivot table is refreshable, and as long as the UserName in the DSN has
ReadOnly priveledges (and as long as you have granted EXEC permission on the
stored procedure) you can share the pivot table with other users.
"bill_morgan_3333" wrote:

> Friends,
> Hi ...
> I have been connecting Excel pivot tables to SQL server tables and views
> through an ODBC DSN connection.
> I know there is a way to connect a pivot table to a SQL Server stored
> procedure, but can't figure it out. The Excel pivot table connection wizard
> doesn't seem to offer stored procedures as one of the connection choices.
> Anyone out there know how to do this?
> Thanks, Bill Morgan
> Thanks ...
>

Connect Excel Pivot Table to SQL Server Stored Procedure

Friends,
Hi ...
I have been connecting Excel pivot tables to SQL server tables and views
through an ODBC DSN connection.
I know there is a way to connect a pivot table to a SQL Server stored
procedure, but can't figure it out. The Excel pivot table connection wizard
doesn't seem to offer stored procedures as one of the connection choices.
Anyone out there know how to do this?
Thanks, Bill Morgan
Thanks ...Hi, again ...
Got answer from SQL Server programmer, and was able to connect Excel pivot
table to SQL Server stored procedure:
In Excel, go to Data \ Get External Data \ New Database Query. Choose SQL
Server connection and then select the appropriate DSN connection to your SQL
Server (and in the DSN options, be sure to set the DSN default to the right
database).
Follow the dialog box prompts. You may not see the right tables/views to
choose from, but select any table in order to keep going through the
successive dialog boxes. When given the choice, you want to "View Data or
Edit Query in Microsoft Query" (dialog box with 3 radio buttons - you want
the middle button in Excel 2000)
When you get to the end of the dialog boxes, go into View/SQL (or click on
SQL icon on the toolbar) and delete the SQL code that is in the SQL box. In
it's place, type EXEC your_database_name.Owner.your_stored_procedure_name.
Click OK when the dialog box says you cannot view your query graphically.
Once you see the query result (rows and columns of data) go to File \ Export
results to Excel. Then be sure to check the Pivot Table radio button on the
dialog box that comes up.
The pivot table is refreshable, and as long as the UserName in the DSN has
ReadOnly priveledges (and as long as you have granted EXEC permission on the
stored procedure) you can share the pivot table with other users.
"bill_morgan_3333" wrote:

> Friends,
> Hi ...
> I have been connecting Excel pivot tables to SQL server tables and views
> through an ODBC DSN connection.
> I know there is a way to connect a pivot table to a SQL Server stored
> procedure, but can't figure it out. The Excel pivot table connection wizar
d
> doesn't seem to offer stored procedures as one of the connection choices.
> Anyone out there know how to do this?
> Thanks, Bill Morgan
> Thanks ...
>

confused about pivot

I need to transform the following layout by pivoting, but am confused ......I have a compound primary key that I want to keep intact but then values in the row need to be broken out into their own row.

I need to go from this...

PKcol1 PKcol2 PKcol3 col4 col5 col6 col7

A 2007 1 Y N N N

A 2007 2 Y Y N N

A 2007 3 N N N Y

into this....

A 2007 1 col4 Y

A 2007 1 col5 N

A 2007 1 col6 N

A 2007 1 col7 N

A 2007 2 col4 Y

A 2007 2 col5 Y

A 2007 2 col6 N

A 2007 2 col7 N

A 2007 3 col4 N

A 2007 3 col5 N

A 2007 3 col6 N

A 2007 3 col7 Y

Can I do this using PIVOT or should I just do 4 inserts (one for each col40col7) into a temp table? Any suggestions?

Give a look to UNPIVOT in books online; this is an UNPIVOT and not a PIVOT

|||

Kent is right, you need to use UNPIVOT.

select

PKcol1,

PKcol2,

PKcol3,

col,

[value]

from

dbo.t1

unpivot

(

[value]

for col in ([col4], [col5], [col6], [col7])

) as unpvt;

AMB