Plamodo
asked on
SQL Pivot Dynamically
I have a table that has a large number of columns, one of them being StartYear. I would love a query that takes all the unique values in the StartYear column and puts them into columns of their own, and pivots with a column: Actual
So current table is:
AgreementID StartYear Actual
1 2004 $56
1 2004 $23
1 2005 $85
2 2008 $77
and it would turn into:
AgreementID 2004 2005 2008
1 56
1 23
1 85
2 77
And I would need this query to add new columns automatically everytime new start years are added to the data in the original table. Is this possible?
So current table is:
AgreementID StartYear Actual
1 2004 $56
1 2004 $23
1 2005 $85
2 2008 $77
and it would turn into:
AgreementID 2004 2005 2008
1 56
1 23
1 85
2 77
And I would need this query to add new columns automatically everytime new start years are added to the data in the original table. Is this possible?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ralmada:
Okay, I created the stored proc (FundingActuals), and got it to execute as per what I had in mind - which is fantastic. Now, how do I encorporate that into a view? Can I somehow do a "SELECT * FROM FundingActuals" ? I want to be able to query those results.
Stored procs are a pretty new/foreign to me.
I just need to know how I can use this proc, and I'll divy out the points.
Okay, I created the stored proc (FundingActuals), and got it to execute as per what I had in mind - which is fantastic. Now, how do I encorporate that into a view? Can I somehow do a "SELECT * FROM FundingActuals" ? I want to be able to query those results.
Stored procs are a pretty new/foreign to me.
I just need to know how I can use this proc, and I'll divy out the points.
You can use Openrowset to "query" the result of the stored procedure. Attached you will see both ways, using SQL authentication and Windows authentication
--SQL Authentication
SELECT *
FROM OPENROWSET('SQLOLEDB','SERVER=YourServerName;Database=yourdatabase;UID=username;PWD=PASSWORD', 'Exec FundingActuals') AS a;
--Windows authentication
SELECT *
FROM OPENROWSET('SQLOLEDB','SERVER=YourServerName;Trusted_Connection=Yes;Database=yourdatabase', 'Exec FundingActuals') AS a;
ASKER
Hmm. That didn't work. I get an error "because this component is turned off as part of the security configuration.
There must be a way to use a stored proc in views - no? How are they usually used?
There must be a way to use a stored proc in views - no? How are they usually used?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That didn't work. Said the ad hock distributed queries does not exist. And that I don't have permission to run the reconfigure statement.
I'm going to talk to the DBA about this issue and see where it goes from there...
I'm going to talk to the DBA about this issue and see where it goes from there...
Alternatively, you can ask the DBA to create the linked server for you with sp_addlinkedserver
http://technet.microsoft.com/en-us/library/ms190479.aspx
and then run the query lilke this:
select * from OPENQUERY('yourlinkedserve rname', 'Exec FundingActuals') --change the server name with the name assigned by the DBA.
http://technet.microsoft.com/en-us/library/ms190479.aspx
and then run the query lilke this:
select * from OPENQUERY('yourlinkedserve
ASKER
Darn. DBA is away until after the holidays.
ASKER
I just realized I'm making this too complex. I am building sql rowsource for a listbox, so I'll know which columns I need at the moment, and can specify the years with code depending on what the user has selected.
eg. SELECT AgreementSeriesID, [2005] AS F2005, [2006] AS F2006
FROM (SELECT AgreementSeriesID, FiscalStartYear, ActualAmount
FROM FundingYearly) fy PIVOT (SUM(ActualAmount) FOR FiscalStartYear IN ([2005], [2006])) AS pvt
So, all I need to know now is how do I insert a where clause into this?
eg. SELECT AgreementSeriesID, [2005] AS F2005, [2006] AS F2006
FROM (SELECT AgreementSeriesID, FiscalStartYear, ActualAmount
FROM FundingYearly) fy PIVOT (SUM(ActualAmount) FOR FiscalStartYear IN ([2005], [2006])) AS pvt
So, all I need to know now is how do I insert a where clause into this?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys.
Open in new window