Solved

SQL Pivot

Posted on 2013-05-21
5
351 Views
Last Modified: 2013-05-21
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...
0
Comment
Question by:lrbrister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 500 total points
ID: 39185296
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
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 39185324
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
 

Author Closing Comment

by:lrbrister
ID: 39185477
Great answers
Watch for follow-up.  I messed this question up actually
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39185584
post your follow-up question
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question