sql - 计算多列值的数量

Question

我有一个包含 11 列的表格。第一列包括类别名称。其余 10 列具有白色、绿色、大、损坏等值，这些值可以随时间变化。

我需要一个 SQL 查询来查找表(10 列)中每个值有多少。

表 1:

+------------+------------+
|  ID        | decription |
+------------+------------+
| 1          | white      |
| 2          | green      |
| 3          | big        |
| 4          | damaged    |
+------------+------------+

表 2:

+------------+-----------+-----------+-----------+
|  CATEGORY  | SECTION 1 | SECTION 2 | SECTION 3 |
+------------+-----------+-----------+-----------+
| Category 1 | white     | green     | big       |
| Category 2 | big       | damaged   | white     |
| Category 1 | white     | green     | big       |
| Category 3 | big       | damaged   | white     |
+------------+-----------+-----------+-----------+

期望的结果:

+------------+-------+-------+-----+---------+
|  CATEGORY  | White | Green | Big | Damaged |
+------------+-------+-------+-----+---------+
| Category 1 |    20 |    10 |   9 |      50 |
| Category 2 |    25 |    21 |  15 |       5 |
+------------+-------+-------+-----+---------+

是否可以像查询一样动态地这样做？

它在 visual studio 报告中使用 MS sql

谢谢

Answer 1

您对设计和预期结果有些困惑。问题是你的表是非规范化的，然后你想要的最终结果也是非规范化的。您可以通过取消透视您的 Section 列，然后旋转这些列的值来获得最终结果。您需要动态执行此操作，从而进一步增加了困惑。

首先，我建议您重新考虑您的表结构，因为这太困惑而无法维护。

与此同时，在您甚至考虑编写动态版本以获得结果之前，您必须通过静态或硬编码查询获得正确的逻辑。现在，您没有说明您使用的是哪个版本的 SQL Server，但您首先需要取消透视 Section 列。您可以使用 UNPIVOT 函数或 CROSS APPLY。您的查询将以类似于以下的内容开头:

select 
  category,
  value
from yourtable 
unpivot
(
  value for cols in (Section1,Section2,Section3)
) u

参见 SQL Fiddle with Demo .这会将您的数据转换为以下格式:

|   CATEGORY |   VALUE |
|------------|---------|
| Category 1 |   white |
| Category 1 |   green |
| Category 1 |     big |
| Category 2 |     big |
| Category 2 | damaged |
| Category 2 |   white |

现在您有多个 Category 行 - 每个值对应以前在 Section 列中的每个值。由于您需要 Category 中每个单词的总数，您现在可以应用 pivot 函数:

select 
  category,
  white, green, big, damaged
from
(
  select 
    category,
    value
  from yourtable 
  unpivot
  (
    value for cols in (Section1,Section2,Section3)
  ) u
) un
pivot
(
  count(value)
  for value in (white, green, big, damaged)
) p;

参见 SQL Fiddle with Demo .这将为您提供所需的结果，但现在您需要动态完成此操作。您必须使用动态 SQL，它会创建一个 SQL 字符串，执行后会给出最终结果。

如果 UNPIVOT 的列数有限，那么您将在字符串中创建新列值的列表，然后执行类似于:

DECLARE @query  AS NVARCHAR(MAX),
    @colsPivot as  NVARCHAR(MAX);


select @colsPivot 
        = STUFF((SELECT ',' + quotename(SectionValue)
                 from yourtable
                 cross apply
                 (
                   select Section1 union all
                   select Section2 union all
                   select Section3
                 ) d (SectionValue)                
                 group by SectionValue
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query 
  = 'select category, '+@colspivot+'
      from
      (
        select 
          category,
          value
        from yourtable 
        unpivot
        (
          value
          for cols in (Section1, Section2, Section3)
        ) un
      ) x
      pivot
      (
        count(value)
        for value in ('+ @colspivot +')
      ) p'

exec sp_executesql @query 

参见 SQL Fiddle with Demo

如果要逆透视的列数未知，那么您的过程会稍微复杂一些。您需要生成一个包含要反透视的列的字符串，您可以使用 sys.columns 表来获取此列表:

select @colsUnpivot 
    = stuff((select ','+quotename(C.name)
             from sys.columns as C
             where C.object_id = object_id('yourtable') and
                   C.name like 'Section%'
             for xml path('')), 1, 1, '')

然后您需要获取新列值的列表 - 但由于这些是动态的，我们需要做一些工作来生成此列表。您需要对表进行逆透视以将值列表生成到临时表中以供使用。创建一个临时表来存储值:

create table #Category_Section
(
    Category varchar(50),
    SectionValue varchar(50)
);

使用需要反透视的数据加载临时表:

set @unpivotquery 
  = 'select 
        category,
        value
      from yourtable 
      unpivot
      (
        value for cols in ('+ @colsUnpivot +')
      ) u'

insert into #Category_Section exec(@unpivotquery);

参见 SQL Fiddle with Demo .您会看到您的数据看起来与上面的静态版本相同。现在您需要创建一个字符串，其中包含将在最终查询中使用的临时表中的值:

select @colsPivot 
        = STUFF((SELECT ',' + quotename(SectionValue)
                 from #Category_Section
                 group by SectionValue
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

一旦你有了所有这些，你就可以将它们组合成一个最终的查询:

DECLARE @colsUnpivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @colsPivot as  NVARCHAR(MAX),
    @unpivotquery  AS NVARCHAR(MAX);

select @colsUnpivot 
    = stuff((select ','+quotename(C.name)
             from sys.columns as C
             where C.object_id = object_id('yourtable') and
                   C.name like 'Section%'
             for xml path('')), 1, 1, '');

create table #Category_Section
(
    Category varchar(50),
    SectionValue varchar(50)
);

set @unpivotquery 
  = 'select 
        category,
        value
      from yourtable 
      unpivot
      (
        value for cols in ('+ @colsUnpivot +')
      ) u';

insert into #Category_Section exec(@unpivotquery);

select @colsPivot 
        = STUFF((SELECT ',' + quotename(SectionValue)
                 from #Category_Section
                 group by SectionValue
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query 
  = 'select category, '+@colspivot+'
      from
      (
        select 
          category,
          value
        from yourtable 
        unpivot
        (
          value
          for cols in ('+ @colsunpivot +')
        ) un
      ) x
      pivot
      (
        count(value)
        for value in ('+ @colspivot +')
      ) p'

exec sp_executesql @query 

参见 SQL Fiddle with Demo .所有版本都将为您提供最终结果:

|   CATEGORY | BIG | DAMAGED | GREEN | WHITE |
|------------|-----|---------|-------|-------|
| Category 1 |   2 |       0 |     2 |     2 |
| Category 2 |   1 |       1 |     0 |     1 |
| Category 3 |   1 |       1 |     0 |     1 |

如果您的值存储在单独的表中，那么您将从该表生成值列表:

DECLARE @query  AS NVARCHAR(MAX),
    @colsPivot as  NVARCHAR(MAX);


select @colsPivot 
        = STUFF((SELECT ',' + quotename(decription)
                 from descriptions             
                 group by decription
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query 
  = 'select category, '+@colspivot+'
      from
      (
        select 
          category,
          value
        from yourtable 
        unpivot
        (
          value
          for cols in (Section1, Section2, Section3)
        ) un
      ) x
      pivot
      (
        count(value)
        for value in ('+ @colspivot +')
      ) p'

exec sp_executesql @query 

参见 SQL Fiddle with Demo仍然得到相同的结果:

|   CATEGORY | BIG | DAMAGED | GREEN | WHITE |
|------------|-----|---------|-------|-------|
| Category 1 |   2 |       0 |     2 |     2 |
| Category 2 |   1 |       1 |     0 |     1 |
| Category 3 |   1 |       1 |     0 |     1 |