0

I have a pivot table, but my issue is, once a year, it needs manually modifying and it shouldnt have to be that way.

What i currently have is:

SELECT * FROM (SELECT Injuries.PlayerID, InjuryID, YEAR(DateInjured) AS YearInjured, (YEAR(CURRENT_TIMESTAMP) - YEAR(DateOfBirth)) AS Age FROM Injuries INNER JOIN Players ON (Players.PlayerID = Injuries.PlayerID)) AS T
PIVOT (
    COUNT(InjuryID)
    FOR YearInjured IN ([2008],[2009],[2010],[2011],[2012],[2013],[2014],[2015])
) AS PT

I have tried 

SELECT * FROM (SELECT Injuries.PlayerID, InjuryID, YEAR(DateInjured) AS YearInjured, (YEAR(CURRENT_TIMESTAMP) - YEAR(DateOfBirth)) AS Age FROM Injuries INNER JOIN Players ON (Players.PlayerID = Injuries.PlayerID)) AS T
PIVOT (
    COUNT(InjuryID)
    FOR YearInjured IN (SELECT DISTICT YEAR(DateInjured) FROM Injuries)
) AS PT

But SQL server does not like me doing that. I have also attempted to DECLARE a table and select the years into that and use that within the PIVOT, but that does not seem to work too.

Is there a way, without being forced to use sp_exec and a generated query, to do what i am trying to do?

bizzehdee
  • 20,289
  • 11
  • 46
  • 76
  • 3
    You want a dynamic pivot. It requires either manual intervention or dynamic SQL. – Gordon Linoff Jan 27 '16 at 15:30
  • Do you need the columns to be named by the absolute year, or would the relative year be acceptable? Have you tried using SSRS (or EXCEL) as your reporting medium, as it can dynamically name the columns? Have you tried doing a UNION of one query with the column headings and a second with the data? – Pieter Geerkens Jan 27 '16 at 15:31
  • What is the error when trying to do the second query? – sagi Jan 27 '16 at 15:31
  • Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'SELECT'. Msg 102, Level 15, State 1, Line 4 Incorrect syntax near 'DateInjured'. – bizzehdee Jan 27 '16 at 15:32
  • @bizzehdee are you sure the format that year(dateInjured) returns is equal to the format of yearInjured ? – sagi Jan 27 '16 at 15:36
  • might be worth noting that if you use `(YEAR(CURRENT_TIMESTAMP) - YEAR(DateOfBirth)) AS Age` to get someone's age, you might offend them. – JamieD77 Jan 27 '16 at 15:59
  • its a rough estimate of somebodies age and irrelevant to the question – bizzehdee Jan 27 '16 at 16:13
  • apparently the search box is irrelevant also – JamieD77 Jan 27 '16 at 16:31

0 Answers0