asked on 10/5/2011

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

Thx

Microsoft SQL Server 2005 SQL

Last Comment

robrodp

10/5/2011

ralmada

10/5/2011

you can do something like this

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

Open in new window

SOLUTION

Kevin Cross

10/5/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

robrodp

10/5/2011

ASKER

What if instead of states I have say 60 variable values?

ASKER CERTIFIED SOLUTION

ralmada

10/5/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Kevin Cross

10/5/2011

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.

https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html

robrodp

10/5/2011

ASKER

Nice code ralmada, if I want to save the results in a table, where does the code go...

ralmada

10/5/2011

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

robrodp

10/5/2011

ASKER

Great code