Link to home
Start Free TrialLog in
Avatar of robrodp
robrodpFlag for Mexico

asked on

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
Avatar of ralmada
ralmada
Flag of Canada image

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
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of robrodp

ASKER

What if instead of states I have say 60 variable values?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of robrodp

ASKER

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

Avatar of robrodp

ASKER

Great code