ms sql 2005 simple crosstabs

In my table

select state,friend,count(*) as quan from friends group by state,friend order by friend

I get a long list with tha data I need.  friend can appear more than once for a given state.

Arizona, John,12 (meaning in arizona I have 12 friendsa called john. And so forth.

I need a table that wiil have:

a comun called friend and one column for each state. And the value (Quan)  the number

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

you can do something like this

select friend, [Arizona], [Florida], [New York], .... etc etc....
from (select 	state, 
		1 as cnt 
	from yourtable
) o
pivot (sum(cnt) for state in ([Arizona], [Florida], [New York], ... etc etc ....)) p

Open in new window

Kevin CrossChief Technology OfficerCommented:
You can do this many ways, but given your list is finite, though tedious to type out the first time, for the States you can use PIVOT introduced in SQL 2005.

The general syntax is:

   SELECT [state], [friend]
   FROM friends
) f
   FOR [state]
   IN ([Arizona],[California],[Washington])
) p
ORDER BY [friend]

Open in new window

Enter ALL the states you want to PIVOT.

robrodpAuthor Commented:
What if instead of states I have say 60 variable values?
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

the concept is the same, but you might want to consider dynamic SQL
. Check the below example using state column

declare @strSQL varchar(max)
declare @cols varchar(max)

set @cols = stuff((select distinct  '], [' + state 
			from yourtable order by 1 for xml path('')), 1, 2, '') + ']'

set @strSQL = 'select friend, ' + @cols + ' from (
		select friend, state, 1 as cnt from yourtable) o
		pivot (count(cnt) for state in (' + @cols + ')) as p'


Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin CrossChief Technology OfficerCommented:
I agree with ralmada. Here is a nice article to explain the technique. It also offers a nice resource stored procedure that encapsulates the dynamic sql shown above.
robrodpAuthor Commented:
Nice code ralmada, if I want to save the results in a table, where does the code go...
in that case you mean create a new table , try changing this part

you will have to be careful though, not to run this twice otherwise you will get an error saying that a table already exists.
set @strSQL = 'select friend, ' + @cols + ' into yournewtable from (
		select friend, state, 1 as cnt from yourtable) o
		pivot (count(cnt) for state in (' + @cols + ')) as p'


Open in new window

robrodpAuthor Commented:
Great code
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

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.