• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

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?

3 Solutions
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Aside from using a dynamic pivot as indicated by AngelIII, you will need to add a column to make this pivot display the data the way you want, in your case I'm using the row_number() function. Please check the following query.
Declare @strSQL varchar(max) 
DECLARE @cols varchar(2000) 
                                '],[' + cast(StartYear as varchar) 
                        FROM    yourtable 
                        ORDER BY 1 
                        FOR XML PATH('') 
                      ), 1, 2, '') + ']' 
set @strSQL = 'select AgreementID, ' + @cols + 
                ' from ( 
                        SELECT AgreementID, StartYear, Actual, row_number() over (order by agreementID) rn 
                        FROM yourtable 
                ) o 
                pivot (max(Actual) FOR StartYear in (' + @cols + ')) p' 

Open in new window

PlamodoAuthor Commented:

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.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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
FROM OPENROWSET('SQLOLEDB','SERVER=YourServerName;Database=yourdatabase;UID=username;PWD=PASSWORD',  'Exec FundingActuals') AS a;

--Windows authentication
FROM OPENROWSET('SQLOLEDB','SERVER=YourServerName;Trusted_Connection=Yes;Database=yourdatabase',  'Exec FundingActuals') AS a;

Open in new window

PlamodoAuthor Commented:
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?
>>because this component is turned off as part of the security configuration.<<
You need to enable ad hoc distributed queries. try running this code before
sp_configure 'Ad Hoc Distributed Queries', 1

>>There must be a way to use a stored proc in views - no?  How are they usually used?
Usually you will create a function rather than a stored procedure, but the problem is that you have dynamic SQL in there which you cannot use it in a function.
Therefore the only way I see is using the distributed query (OPENROWSET) as I indicated above.
PlamodoAuthor Commented:
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...
Alternatively, you can ask the DBA to create the linked server for you with sp_addlinkedserver
and then run the query lilke this:
select * from OPENQUERY('yourlinkedservername', 'Exec FundingActuals') --change the server name with the name assigned by the DBA.
PlamodoAuthor Commented:
Darn.  DBA is away until after the holidays.
PlamodoAuthor Commented:
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?
SharathData EngineerCommented:
like this.

 SELECT     AgreementSeriesID, [2005] AS F2005, [2006] AS F2006
FROM         (SELECT     AgreementSeriesID, FiscalStartYear, ActualAmount
                       FROM          FundingYearly
                   WHERE FiscalStartYear IN ('2005','2006')) fy PIVOT (SUM(ActualAmount) FOR FiscalStartYear IN ([2005], [2006])) AS pvt
PlamodoAuthor Commented:
Thanks guys.

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now