2005 SQL Pivot

I am trying to pivot years but the years are not showing up
Code
Here are the results from both Queries the second one just has first inside with pivot
 Datayears are missing

What did I do wrong or what am I missing
LVL 8
Leo TorresSQL DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brendt HessSenior DBACommented:
The problem is with the SELECT field list.  You need to include the individual years being reported on in the SELECT.  As shown in an example from BoL:

SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM 
....
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt

Open in new window

0
Leo TorresSQL DeveloperAuthor Commented:
Away from Computer with code but yes, I did include every combination on list I  will try to post later of tomorrow morning

Still did not work

Select

SubmissionName

,ReportTotal

,FirstDOS

,LastDOS

from (

Select sr.SubmissionName

, sc.LineCount as ReportTotal

, isNULL(dos.FirstDOS,0) as FirstDOS

, isNull(dos.LastDOS,0) as LastDOS

, cast(isNull(s.[Year],0) as varchar(max))   as [Year]

, cast(isNull(s.LineCount,0) as varchar(max))  as YearlyCount

from dbo.Hedis_SubmissionReports sr

      join dbo.Hedis_SubmissionLineCounts sc

            on sr.ReportKey = sc.ReportKey

            and sc.FileID = 1

      Left Join dbo.Hedis_SubmissionDatesOService dos

            on dos.FileID = sc.FileID

                  and dos.ReportKey = sc.ReportKey

      Left Join dbo.Hedis_SubmissionCountsByYear s

            on s.FileID = dos.FileID

                  and s.ReportKey = dos.ReportKey    

                               

)p

pivot (MAX(p.[YearlyCount]) for p.[Year] in (

                                                                   [0]

                                                                  ,[2002]

                                                                  ,[2003]

                                                                  ,[2004]

                                                                  ,[2005]

                                                                  ,[2006]

                                                                  ,[2007]

                                                                 , [2008]

                                                                  ,[2009]

                                                                  ,[2010]

                                                                  ,[2011]

                                                                  ,[2012]

                                                                  ,[2013]

                                                                  ,[2017]

                                                                ,[2026]

                                                                  )  ) uPvt

order by SubmissionName

Open in new window

0
PortletPaulfreelancerCommented:
No points pl.

please try this:
SELECT
        SubmissionName
      , ReportTotal
      , FirstDOS
      , LastDOS
      , [0], [2002], [2003], [2004], [2005], [2006], [2007], [2008], [2009], [2010], [2011], [2012], [2013], [2017], [2026]
FROM (
        SELECT
                sr.SubmissionName
              , sc.LineCount                                 AS ReportTotal
              , ISNULL(dos.FirstDOS, 0)                      AS FirstDOS
              , ISNULL(dos.LastDOS, 0)                       AS LastDOS
              , CAST(ISNULL(s.[Year], 0) AS varchar(MAX))    AS [Year]
              , CAST(ISNULL(s.LineCount, 0) AS varchar(MAX)) AS YearlyCount
        FROM dbo.Hedis_SubmissionReports sr
        INNER JOIN dbo.Hedis_SubmissionLineCounts sc
                ON sr.ReportKey = sc.ReportKey
                AND sc.FileID = 1
        LEFT JOIN dbo.Hedis_SubmissionDatesOService dos
                ON dos.FileID = sc.FileID
                AND dos.ReportKey = sc.ReportKey
        LEFT JOIN dbo.Hedis_SubmissionCountsByYear s
                ON s.FileID = dos.FileID
                AND s.ReportKey = dos.ReportKey
        ) AS p
PIVOT (SUM([YearlyCount]) FOR [Year] IN ([0], [2002], [2003], [2004], [2005], [2006], [2007], [2008], [2009], [2010], [2011], [2012], [2013], [2017], [2026])) AS uPvt
ORDER BY
        SubmissionName

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Leo TorresSQL DeveloperAuthor Commented:
Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.