T-SQL计数问题

Question

我有一个需要总结的表格，如下所示：

ID      A   B   C   D   E   F   G
----------------------------------
1-100   1   2   1   1   1   1   1
1-201   1   2   1   2   2   2   2
1-322   1   1   1   1   2   2   1
2-155   1   1   2   1   1   2   2
2-167   2   1   2   1   2   1   2
2-389   2   2   1   2   1   1   2
2-423   1   2   2   2   1   1   1
3-10    2   1   1   1   2   2   2
3-222   1   1   1   1   2   2   1
3-397   2   1   1   2   2   1   1

在上表中，数值1编码为S，而数值2编码为R。此外，ID是一个代码，格式为XX、YY或XX，其中“-”前的数字代表XX、YY或XX。

我想要的摘要是这样的。

         XX         YY              ZZ
------------------------------------------
A   S   3   100%    2   50%     1   33%
    R   0   0%      2   50%     2   66%
B   S   2   66%     2   50%     3   100%
    R   1   33%     2   50%     0   0%
C   S   3   100%    3   75%     3   100%
    R   0   0%      1   25%     0   0%
D   S   2   66%     2   50%     2   66%
    R   1   33%     2   50%     1   33%
E   S   1   33%     3   75%     0   0%
    R   2   66%     1   25%     3   100%
F   S   1   33%     3   75%     2   66%
    R   2   66%     1   25%     1   33%
G   S   2   66%     1   25%     1   33%
    R   1   33%     3   75%     2   66%

所以，我需要旋转表格，计算1/2并创建百分比。

这让我感到困惑，我已经走了一些死路，不知道如何做到这一点（更不用说如何优雅地做到了）

提前感谢！

---

在Martin的帮助下，我已经非常接近了。我的数据当然比我给出的示例数据更混乱，所以我仍然有困难。我已经审查了数据，并放置了我想要的正确编码 - 是的，编码非常混乱，我无法控制 :)

我已经扩展了Martins SQL以链接到我的数据，但仍有两个问题。 Thing列中的行顺序不完全是我想要的。

当我尝试以下代码时，我会得到一个“必须声明标量变量@order”的错误 - 它不喜欢连接到我的名为myOrder的临时表。

DECLARE @myOrder TABLE (rug varchar(3), rugOrder int)    
INSERT @myOrder
    SELECT 'INH', 1 UNION ALL
    SELECT 'RIF', 2 UNION ALL
    SELECT 'KM', 3 UNION ALL
    SELECT 'AK', 4 UNION ALL
    SELECT 'CM', 5 UNION ALL
    SELECT 'MOX', 6 UNION ALL
    SELECT 'OFX', 7;

WITH YourData(ID, INH, RIF, KM, AK, CM, MOX, OFX) As
(SELECT Sample_ID, INH, RIF, KM, AK, CM, MOX, OFX
FROM dbo.[GCT_Rug] WHERE Sample_ID NOT LIKE '99%')

, Unpivoted AS
(
SELECT S_R_Flag,
       Thing,
       Site = 
        CASE 
        WHEN LEFT(ID,1) = 1 THEN 1 
        WHEN LEFT(ID,1) = 6 THEN 1 
        WHEN LEFT(ID,1) = 8 THEN 2 
        WHEN LEFT(ID,1) = 9 THEN 3 END

FROM YourData
UNPIVOT
   (S_R_Flag FOR Thing IN (INH, RIF, KM, AK, CM, MOX, OFX)
)AS unpvt)
SELECT Thing
       ,SRFLAG =
            CASE 
                WHEN S_R_Flag = 1 THEN 'S'
                WHEN S_R_Flag = 2 THEN 'R'
            END
       ,[1] AS IND
   ,round(CAST([1] AS FLOAT) / NULLIF(SUM([1]) OVER (PARTITION BY Thing),0)*100,1) AS 'Ind Percent'
       ,[2] AS MD
   ,round(CAST([2] AS FLOAT) / NULLIF(SUM([2]) OVER (PARTITION BY Thing),0)*100,1) AS 'MD Percent'
   ,[3] AS 'SA'
   ,round(CAST([3] AS FLOAT) / NULLIF(SUM([3]) OVER (PARTITION BY Thing),0)*100,1) AS 'SA Percent'

FROM Unpivoted 
INNER JOIN @myOrder
ON Unpivoted.Thing= @myOrder.rug
PIVOT (COUNT (Site) FOR Site IN ( [1], [2], [3])) AS pvt
ORDER BY rugOrder,
         SRFLAG;

错误信息“必须声明标量变量@myOrder”，是什么意思？为什么我不能连接它？

再次感谢你们（尤其是Martin），你们太棒了！

- user918967

---

你能把你查询的部分内容添加进来吗？ - Eric R.

到目前为止，我一直在按照这个路线 SUM(CASE WHEN A=1 THEN 1 ELSE 0 END) AS AS, SUM(CASE WHEN A=2 THEN 1 ELSE 0 END) AS AB。 - user918967

发布完整的查询将有助于解决问题。 - sorpigal

我没有完整的查询。当我试图推动数据时，很快我意识到我的方法不会成功。我仍在努力找出最好的方法... 我想把数据移到一个格式中，其中有三列ID、COLUMNABCDEFG和Data，在COLUMNABCDEFG中的数据将是A、B、C、D、E、F或G，而Data将是1或2。然后我可以使用COUNT聚合... - user918967

Answer 1

这基本上可以给你所需的结果，但我没有费心将数字值映射到代码。

;WITH YourData(ID,A,B,C,D,E,F,G) As
(
SELECT '1-100',1,2,1,1,1,1,1 UNION ALL
SELECT '1-201',1,2,1,2,2,2,2 UNION ALL
SELECT '1-322',1,1,1,1,2,2,1 UNION ALL
SELECT '2-155',1,1,2,1,1,2,2 UNION ALL
SELECT '2-167',2,1,2,1,2,1,2 UNION ALL
SELECT '2-389',2,2,1,2,1,1,2 UNION ALL
SELECT '2-423',1,2,2,2,1,1,1 UNION ALL
SELECT '3-10 ',2,1,1,1,2,2,2 UNION ALL
SELECT '3-222',1,1,1,1,2,2,1 UNION ALL
SELECT '3-397',2,1,1,2,2,1,1
), Unpivoted AS
(
SELECT S_R_Flag,
       Thing,
       SUBSTRING(ID,1,CHARINDEX('-',ID )-1) AS Code,ID
FROM YourData
UNPIVOT
   (S_R_Flag FOR Thing IN (A,B,C,D,E,F,G)
)AS unpvt)
SELECT Thing
       ,S_R_Flag
       ,[1]
       ,CAST([1] AS FLOAT) / SUM([1]) OVER (PARTITION BY Thing)
       ,[2]
       ,CAST([2] AS FLOAT) / SUM([2]) OVER (PARTITION BY Thing)
       ,[3]
       ,CAST([3] AS FLOAT) / SUM([3]) OVER (PARTITION BY Thing)
FROM Unpivoted
PIVOT (COUNT (ID) FOR Code IN ( [1], [2], [3] )) AS pvt
ORDER BY Thing,
         S_R_Flag;

- Martin Smith

---

这个能更泛化一些吗？表格会随着时间增长而变大（ID），因此我不知道行数或1和2的模式。 - user918967

@user918967 - 没有动态SQL是不行的。除非能够将此XML技术调整为可行，否则PIVOT和UNPIVOT都需要静态列列表。 - Martin Smith

嗨，马丁，CAST([1] AS FLOAT) / NULLIF(SUM([1]) OVER (PARTITION BY Thing),0) 应该非常接近，但现在所有百分比都是 NULL。看起来 Sum[1] 不等于 0，因此它显示为 NULL？ - user918967

让我们在聊天中继续这个讨论：http://chat.stackoverflow.com/rooms/3742/discussion-between-user918967-and-martin-smith - user918967

@user918967 回复：你发布的代码需要给表变量取一个别名。使用 INNER JOIN @myOrder [@myOrder] ON Unpivoted.Thing= [@myOrder].rug - Martin Smith