robrodp
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
What if instead of states I have say 60 variable values?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html
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.
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'
....
ASKER
Great code
Open in new window