Link to home
Create AccountLog in
Avatar of Leo Torres
Leo TorresFlag for United States of America

asked on

2005 SQL Pivot

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

What did I do wrong or what am I missing
Avatar of Brendt Hess
Brendt Hess
Flag of United States of America image

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

Avatar of Leo Torres

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank you!