尝试在SQL中构造类似透视表的项

Question

我正在尝试创建一个显示某个地区的行业细分的结果。最终结果将如下所示。

  Area          Industry                    Number of sites
 000000       All industries                        250
 000001       Industry 1(11 and 21)                 22
 000001       Industry 2(23)                        25
 ......
 000001       Industry 10 (81)                      120

一些背景知识，这样上面的内容就有意义了。在原始数据集中，有6位行业代码。我们根据前两个数字对它们进行分解，然后进行自定义分组。11和21是一个组，依此类推。我可以计算出如何获得单个行，但却很难弄清楚如何在一个结果中获得所有11行。我可以编写和执行11个独立的查询，但我希望得到一个结果。下面是获取其中一行的代码。这是使用JOIN或UNION命令或运算符的地方吗？

SELECT
[Area],
COUNT(*) AS [Number of Sites]
FROM
dbo.sizeclassreport
Where area='000001' and (code like '11%' or code like '21%')
GROUP BY
[area]

Answer 1

我认为这是简单的东西，但互联网的结果是令人困惑的。它所做的就是在每个查询之间放置一个"UNION ALL“。

SELECT
[Area],
COUNT(*) AS [Number of Sites]
FROM
dbo.sizeclassreport
Where area='000001' and (code like '11%' or code like '21%')
GROUP BY
[area]
UNION ALL
SELECT
[Area],
COUNT(*) AS [Number of Sites]
FROM
dbo.sizeclassreport
Where area='000001' and (code like '23%')
GROUP BY
[area]
UNION ALL
SELECT
[Area],
COUNT(*) AS [Number of Sites]
FROM
dbo.sizeclassreport
Where area='000001' and (code like '31%' or code like '32%' or code like 
'33%')
GROUP BY
[area]