sql - 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 以链接到我的数据，但还有两个问题。事物列中的行顺序不是我想要的。

当我尝试下面的代码时，我得到一个“必须声明标量变量@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)太棒了!

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;