SQL Pivot

How can I do a select on a Specialties table and a states table

Specialties table has
SpecialtyID     StateID


States table has
StateID     StateName (Varchar(2))

So that I have this output

SpecialtyID   AK               AL   AZ   etc....
data              count(AK)   etc...
lrbristerAsked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
You can do it dynamically if you have lot of states.
 
 declare @select varchar(max)
select @select = STUFF((select distinct ',' + quotename(StateName, '')
from States
for XML PATH('')
),1,1,'' )

select @select = 'select *
  from (
		select s1.SpecialtyID,s2.StateName
		  from Specialties s1
		  join States s2 on s1.StateID = s2.StateID) t1
 pivot (count(StateName) for StateName in ('+@select+')) p'
 exec(@select)

Open in new window


 http://sqlfiddle.com/#!3/e12ec/1
0
 
SharathConnect With a Mentor Data EngineerCommented:
try this
select *
  from (
		select s1.SpecialtyID,s2.StateName
		  from Specialties s1
		  join States s2 on s1.StateID = s2.StateID) t1
 pivot (count(StateName) for StateName in ([AK],[AL],[AZ])) p		  

Open in new window


http://sqlfiddle.com/#!3/75dc7/1
0
 
lrbristerAuthor Commented:
Great answers
Watch for follow-up.  I messed this question up actually
0
 
SharathData EngineerCommented:
post your follow-up question
0
All Courses

From novice to tech pro — start learning today.