• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

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
0
robrodp
Asked:
robrodp
  • 3
  • 3
  • 2
2 Solutions
 
ralmadaCommented:
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

0
 
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 *
FROM (
   SELECT [state], [friend]
   FROM friends
) f
PIVOT (
   COUNT([state])
   FOR [state]
   IN ([Arizona],[California],[Washington])
) p
ORDER BY [friend]
;

Open in new window


Enter ALL the states you want to PIVOT.

Kevin
0
 
robrodpAuthor Commented:
What if instead of states I have say 60 variable values?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
ralmadaCommented:
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'

exec(@strSQL)

Open in new window

0
 
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.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html
0
 
robrodpAuthor Commented:
Nice code ralmada, if I want to save the results in a table, where does the code go...
0
 
ralmadaCommented:
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

0
 
robrodpAuthor Commented:
Great code
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now