SQL Pivot

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
SELECT	HT.[YR],
		A.[Specialty],
		A.[Lead ID],
		A.[Client State] AS ClientState,
		HT.[HRS]
FROM	[dbo].[Assignment] A
INNER JOIN 
	(
		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
		GROUP BY
				[Assignment Number] ,YEAR(H.[Week Ending Date])        
	) HT
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

Open in new window


Produces this

sp

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.
ssw
lrbristerAsked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
can you try this?
declare @select varchar(max)
select @select = STUFF((select distinct ',' + quotename(StateName, '')
from States
for XML PATH('')
),1,1,'' )

select @select = 'select YR,Specialty,'+@select+
   ' from (
		SELECT	HT.[YR],
		A.[Specialty],
		A.[Lead ID],
		A.[Client State] AS ClientState,
		HT.[HRS]
FROM	[dbo].[Assignment] A
INNER JOIN 
	(
		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
		GROUP BY
				[Assignment Number] ,YEAR(H.[Week Ending Date])        
	) HT
ON	A.[Assignment Number] = HT.[Assignment Number]
WHERE	HT.HRS > 0 AND A.[Lead ID] IS NOT NULL) t1
 pivot (sum(HRS) for ClientState in ('+@select+')) p'
 exec(@select)

Open in new window

0
 
lrbristerAuthor Commented:
Excellent! Thanks

That's much better that what I manually came up with


SELECT [YR],[Specialty], [AK],[AL],[AR],[AZ],[CA],[CO],[CT],[DC],[DE],[FL],[GA],[HI],[IA],[ID],[IL],[IN],[KS],[KY],[LA],[MA],[MD],[ME],[MI],[MN],[MO],[MS],[MT],[NC],[ND],[NE],[NH],[NJ],[NM],[NV],[NY],[OH],[OK],[OR],[PA],[RI],[SC],[SD],[TN],[TX],[UT],[VA],[VI],[VT],[WA],[WI],[WV],[WY]
FROM
      (
            SELECT      HT.[YR],
                        A.[Specialty],
                        A.[Lead ID],
                        A.[Client State] AS ClientState,
                        HT.[HRS]
            FROM      [dbo].[Assignment] A
            INNER JOIN
                  (
                        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
                        GROUP BY
                                    [Assignment Number] ,YEAR(H.[Week Ending Date])        
                  ) HT
            ON      A.[Assignment Number] = HT.[Assignment Number]
            WHERE      HT.HRS > 0 AND A.[Lead ID] IS NOT NULL
      ) p
PIVOT
(
      SUM(P.[HRS])
      FOR P.ClientState IN ([AK],[AL],[AR],[AZ],[CA],[CO],[CT],[DC],[DE],[FL],[GA],[HI],[IA],[ID],[IL],[IN],[KS],[KY],[LA],[MA],[MD],[ME],[MI],[MN],[MO],[MS],[MT],[NC],[ND],[NE],[NH],[NJ],[NM],[NV],[NY],[OH],[OK],[OR],[PA],[RI],[SC],[SD],[TN],[TX],[UT],[VA],[VI],[VT],[WA],[WI],[WV],[WY])
) AS myPIVOT

GROUP BY [YR],[Specialty]
ORDER BY [YR],[Specialty]
0
All Courses

From novice to tech pro — start learning today.