Sharath provided me a Pivot Solution at This Link
But I'm struggling to get this to pivot for me.
Basically I need the totals by state columns
Right now, this select
A.[Client State] AS ClientState,
FROM [dbo].[Assignment] A
SELECT [Assignment Number], SUM(ISNULL(H.[Hours],0) + ISNULL(H.[On Call Hours],0)) HRS, YEAR(H.[Week Ending Date]) AS [YR]
FROM [dbo].[HoursTracking] H
[Assignment Number] ,YEAR(H.[Week Ending Date])
ON A.[Assignment Number] = HT.[Assignment Number]
WHERE HT.HRS > 0 AND A.[Lead ID] IS NOT NULL
ORDER BY A.[Client State],A.[Specialty], HT.[YR] DESC
What I need to see is this
The states are the sum of that state for that specialty and that year
Of course there will be all the states.