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...
Larry Bristersr. DeveloperAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
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
 
Larry Bristersr. DeveloperAuthor Commented:
Great answers
Watch for follow-up.  I messed this question up actually
0
 
SharathData EngineerCommented:
post your follow-up question
0
 
Larry Bristersr. DeveloperAuthor Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.