ms sql 2005 simple crosstabs

Posted on 2011-10-05
Last Modified: 2012-05-12
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

Question by:robrodp
    LVL 41

    Expert Comment

    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

    LVL 59

    Assisted Solution

    by:Kevin Cross
    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 (
       FOR [state]
       IN ([Arizona],[California],[Washington])
    ) p
    ORDER BY [friend]

    Open in new window

    Enter ALL the states you want to PIVOT.


    Author Comment

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

    Accepted Solution

    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

    LVL 59

    Expert Comment

    by:Kevin Cross
    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.

    Author Comment

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

    Expert Comment

    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


    Author Closing Comment

    Great code

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now