Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Pivot

Posted on 2013-05-21
5
Medium Priority
?
354 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 2000 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 2000 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

664 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