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

and this what i get from edit

Answer 1

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: