如何转置查询行和列

Question

我想垂直反转 sql server 查询，用 cols 值更改行值，这是函数：

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

我的表的 cols 为：cities，行为：months< /strong> 我有一个非常大的城市列表，因此我想列出它，反之亦然，这样它会很长垂直而不是水平

到目前为止我已经写了：

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

但我得到 null 而不是 0 （零） ，可能是什么问题？

+编辑如果我使用sisdog的技术）我的sql看起来像这样：

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

和我的输出：

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

但我仍然得到这个屏幕截图：

这是来自第一个代码

这是我从编辑中得到的

Answer 1

我不确定为什么你在那里有交叉连接，我不确定你想要完成什么。简单的左外连接不起作用吗？由于你的枢轴柱只能是 12 种不同类型，我认为你可以使用下面的“穷人”枢轴。

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

如果您想返回所有城市，无论它们是否有发票总额，您只需将 SETUP_CITY 表移动为 FROM 子句中的第一个表，然后对其他表使用 LEFT OUTER，如下所示

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

：总行数，只需将其添加到 SQL 的底部即可。它不按任何内容进行分组，因此您可以得到总计。

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

这是我的结果：