How to transpose query rows and columns

Question

I want to perpendicularly reverse the sql server query, to change the rows values with cols values, here is the function:

<cfquery name="get_top_sales_TOTAL" datasource="#dsn#">
                    SELECT SUM(coalesce(NETTOTAL,0)) AS NETTOTAL, SC.CITY_ID, SC.CITY_NAME, M.INVOICE_MONTH 
                    FROM SETUP_CITY SC
                        LEFT OUTER JOIN COMPANY C ON SC.CITY_ID = C.CITY 
                        CROSS JOIN (SELECT DISTINCT DATEPART(MM,INVOICE_DATE) INVOICE_MONTH FROM #DSN2_ALIAS#.INVOICE) M 
                        LEFT OUTER JOIN #DSN2_ALIAS#.INVOICE I ON C.COMPANY_ID = I.COMPANY_ID AND DATEPART(MM,I.INVOICE_DATE) = M.INVOICE_MONTH AND I.PURCHASE_SALES = 1
                    WHERE SC.COUNTRY_ID=1 
                    GROUP BY M.INVOICE_MONTH, SC.CITY_ID, SC.CITY_NAME 
                    ORDER BY M.INVOICE_MONTH, SC.CITY_ID, SC.CITY_NAME
                </cfquery>

I have the table with the cols as: cities and rows as: months I have a very big list of the cities thus I want to list it vice-versa, so that it will be very long vertically not horizontally

so far i've wrote this:

<cfquery name="get_top_sales_TOTAL" datasource="#dsn#">
                    SELECT SUM(COALESCE(NETTOTAL,0)) AS NETTOTAL, S.CITY_ID, S.CITY_NAME, DATEPART(MM,I.INVOICE_DATE) INVOICE_MONTH
                    FROM #DSN2_ALIAS#.INVOICE I
                        LEFT OUTER JOIN COMPANY C ON I.COMPANY_ID = C.COMPANY_ID
                        CROSS JOIN (SELECT DISTINCT CITY_NAME,CITY_ID FROM SETUP_CITY) S
                        LEFT OUTER JOIN SETUP_CITY SC ON C.CITY = SC.CITY_ID AND S.CITY_ID = SC.CITY_ID
                    WHERE SC.COUNTRY_ID=1 AND I.PURCHASE_SALES = 1
                    GROUP BY S.CITY_ID, I.INVOICE_DATE,S.CITY_NAME
                    ORDER BY S.CITY_ID, I.INVOICE_DATE,S.CITY_NAME
                </cfquery>

but i get null instead of 0 ( zeroes ), what can be the problem?

+ EDIT if i use sisdog's technique ) my sql looks like this:

<cfquery name="get_top_sales_TOTAL" datasource="#dsn#">
                    SELECT 
                        S.CITY_ID,S.CITY_NAME,DATEPART(MM,I.INVOICE_DATE) INVOICE_MONTH,
                        JAN=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=1 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
                        FEB=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=2 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
                        MAR=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=3 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
                        APR=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=4 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
                        MAY=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=5 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
                        JUN=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=6 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
                        JUL=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=7 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
                        AUG=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=8 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
                        SEP=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=9 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
                        OCT=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=10 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
                        NOV=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=11 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
                        DEC=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=12 THEN COALESCE(NETTOTAL,0) ELSE 0 END)
                    FROM 
                        #DSN2_ALIAS#.INVOICE I 
                        LEFT OUTER JOIN COMPANY C ON I.COMPANY_ID = C.COMPANY_ID
                        CROSS JOIN (SELECT CITY_ID,CITY_NAME,COUNTRY_ID FROM SETUP_CITY) S 
                        LEFT OUTER JOIN SETUP_CITY SC ON SC.CITY_ID = C.CITY
                    WHERE 
                        S.COUNTRY_ID = 1
                    GROUP BY
                        S.CITY_ID,I.INVOICE_DATE,S.CITY_NAME
                    ORDER BY 
                        S.CITY_ID,I.INVOICE_DATE,S.CITY_NAME
                </cfquery>

and my output:

<cfoutput query="get_top_sales_TOTAL" group="city_id">
                        <tr height="20" class="color-row" onMouseOver=this.className="color-light"; onMouseOut=this.className="color-row"; class="color-row">
                            <td><b>#city_name#</b></td>
                            <cfoutput group="invoice_month">
                                <td class="txtbold">
                                    <cfif invoice_month eq 1>#TLFORMAT(JAN,2)#</cfif>
                                    <cfif invoice_month eq 2>#TLFORMAT(feb,2)#</cfif>
                                </td>
                            </cfoutput>
                        </tr>
                    </cfoutput>

but still i get this screenshots:

this is from the first code

this is from the first code

and this what i get from edit

and this what i get from edit

@JakeFeasel or how i can use this pivot?! i just heard bout it, but didnt use it at all :( — user745110, Jan 30 '12 at 15:51
@JakeFeasel thx :) just googled it, but still have difficulties in using it :( — user745110, Jan 30 '12 at 15:55
It's a bit tricky getting used to. When I get some more time, if you haven't posted here that you've got it working, I'll work on an actual answer instead of just a hint :) — Jake Feasel, Jan 30 '12 at 16:02
It would be helpful if you could post example tables showing 1) what your current query is returning and 2) the result you would like to see returned. — Jake Feasel, Jan 30 '12 at 17:51
You might want to look at http://www.sqlservercentral.com/articles/T-SQL/63681/ for a more detailed explanation of Pivot by Jeff Moden. — TimothyAWiseman, Jan 30 '12 at 20:04
See also http://stackoverflow.com/questions/3924641/poor-mans-sql-pivot-list-questions-as-columns-and-answers-per-user-in-one-row — Ben, Jan 30 '12 at 20:12

sisdog · Accepted Answer · 2012-02-02T15:42:49.247

I'm not sure why you have your CROSS JOIN in there, I'm not sure what you're trying to accomplish. Wouldn't simple left outer joins work? And since your pivot columns can only be 12 different types I think you can use "poor man's" pivoting below.

SELECT 
    SC.CITY_ID,SC.CITY_NAME,
    JAN=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=1 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    FEB=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=2 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    MAR=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=3 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    APR=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=4 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    MAY=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=5 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    JUN=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=6 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    JUL=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=7 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    AUG=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=8 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    SEP=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=9 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    OCT=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=10 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    NOV=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=11 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    DEC=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=12 THEN COALESCE(NETTOTAL,0) ELSE 0 END)
FROM 
    SETUP_CITY SC
    LEFT OUTER JOIN COMPANY C ON C.CITY = SC.CITY_ID
    LEFT OUTER JOIN INVOICE I ON I.COMPANY_ID = C.COMPANY_ID
GROUP BY
    SC.CITY_ID,SC.CITY_NAME
ORDER BY 
    SC.CITY_ID,SC.CITY_NAME

If you want to return all cities regardless of whether they have invoice totals or not, you just need to move your SETUP_CITY table as the first table in your FROM clause and then use LEFT OUTER to the other tables like this:

FROM 
    SETUP_CITY SC
    LEFT OUTER JOIN COMPANY C ON C.CITY = SC.CITY_IT
    LEFT OUTER JOIN INVOICE I ON I.COMPANY_ID = C.COMPANY_ID

And if you want a total row, just add this to the bottom of the SQL. It doesn't group by anything so that will get you your totals.

UNION
SELECT 
    0,'ALL CITIES',
    JAN=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=1 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    FEB=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=2 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    MAR=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=3 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    APR=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=4 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    MAY=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=5 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    JUN=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=6 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    JUL=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=7 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    AUG=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=8 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    SEP=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=9 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    OCT=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=10 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    NOV=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=11 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    DEC=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=12 THEN COALESCE(NETTOTAL,0) ELSE 0 END)
FROM 
    SETUP_CITY SC
    LEFT OUTER JOIN COMPANY C ON C.CITY = SC.CITY_ID
    LEFT OUTER JOIN INVOICE I ON I.COMPANY_ID = C.COMPANY_ID

Here are my results: enter image description here

Thank you, but i use cross join to apply the nettotal according to cities and months. but in ur code there are just sum values of the month, but no cities division — user745110, Jan 31 '12 at 07:55
This query results in cities as rows, months as columns, and the invoice totals for the matrixed cells. Isn't that what you're looking for? If not, can you send a sample of the output you'd like to see? — sisdog, Jan 31 '12 at 08:12
yes this is exactly what im looking for: cities as rows, months as columns, and the invoice totals for the matrixed cells — user745110, Jan 31 '12 at 08:33
btw i think this is quite a good idea to simply divide by months in sql ) but how do i cross join it with cities? — user745110, Jan 31 '12 at 08:50
As several people mentioned, it would be helpful to post the *desired* result. Despite reading the comments several times, I still do not know. — Leigh, Jan 31 '12 at 15:51
@user745110 - To see all cities you merely left join from cities (see edits to my answer above). I think you're getting hung up on what a cross join would buy you. Cross joins return cartisian products between two tables but since your month numbers are fixed at 12 that's not needed here. Now, things would be very different if your columns were dates instead of month numbers. In that case a completely different solution would be needed with either a PIVOT table query or dynamic SQL. — sisdog, Jan 31 '12 at 17:43
@sisdog but what about `sum(nettotal)` how to sum all of the values inside the month, it can't be nested inside the `case` — user745110, Feb 02 '12 at 11:54
@user745110 - If you want an extra row for the totals then you just need add a UNION clause to the statement. I updated my answer code. — sisdog, Feb 02 '12 at 15:43

How to transpose query rows and columns

1 Answers1