从ACCESS转换计数/枢轴转换到SQL SERVER 2005

Question

需要一些帮助，从access TRANSFORM COUNT/PIVOT转换为SQL SERVER，下面是access中的sql：

TRANSFORM Count(tmpTbl.TC) AS CountOfTC
SELECT tmpTbl.SID, tmpTbl.CSID, tmpTbl.M,WoOr.QCL
FROM tmpTbl INNER JOIN WoOr ON tmpTbl.WO = WoOr.WO
WHERE (((tmpTbl.IsSelected)=True))
GROUP BY tmpTbl.SID, tmpTbl.CSID, tmpTbl.M,WoOr.QCL
PIVOT tmpTbl.TN;

输出：

SID |   CSID | M |   QCL | EPA 1 | EPA 2 |
-----------------------------------------|
1   |   A    |GW |  IV   |  3    |       |
2   |   B    |GW |  IV   |       |  4    |
------------------------------------------

WHere发现3 EPA 1计数，4 EPA 2计数出现在tmpTbl表中。

预先谢谢你！

Answer 1

Server不使用TRANSFORM关键字和PIVOT将数据行转换为列。支点的基本语法将使用MSDN中的示例。

SELECT <non-pivoted column>,   -- your final columns displayed will be in this select
    [first pivoted column] AS <column name>,
    [second pivoted column] AS <column name>,
    ...
    [last pivoted column] AS <column name>
FROM
(
   <SELECT query that produces the data>  -- the FROM will include your existing select from/join 
) AS <alias for the source query>
PIVOT
(
   <aggregation function>(<column being aggregated>)  -- your count Count(tmpTbl.TC)
   FOR
   [<column that contains the values that will become column headers>]  -- the FOR includes the tmpTbl.TN
    IN ( [first pivoted column], [second pivoted column],               -- the IN will be your EPA1 and EPA2 values
    ... [last pivoted column])
) AS <alias for the pivot table>

一旦您了解了现有Access查询的所有部分将在Server枢轴中的位置，语法就很容易编写。在Server中，当前的查询如下：

select sid, csid, m, qcl, [EPA 1], [EPA 2]
from
(
    select t.sid, t.csid, t.m, w.qcl, t.tc, t.tn
    from tmpTbl t
    inner join WoOr w
        on t.wo = w.wo
    where t.isselected = 1
) d
pivot
(
    count(tc)
    for tn in ([EPA 1], [EPA 2])
) piv;

如果有未知值，则需要使用动态SQL来获得结果：

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(tn) 
                    from tmpTbl
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT sid, csid, m, qcl, ' + @cols + ' 
            from 
            (
              select t.sid, t.csid, t.m, w.qcl, t.tc, t.tn
              from tmpTbl t
              inner join WoOr w
                  on t.wo = w.wo
              where t.isselected = 1
            ) x
            pivot 
            (
                count(tc)
                for tn in (' + @cols + ')
            ) p '

execute sp_executesql @query;