找到你要的答案

Q:Different columns of the same name SQL Sorted by Column Names

Q:同名的SQL列名称排序不同的列

I have a table in SQL

Category | Subcategory | Region | Count | Percent |
    A    |      Aa     |   ON   |   3   |   5%    |
    A    |      Aa     |   AT   |   1   |   2%    |
    A    |      Ab     |   ON   |   5   |   10%   |
    B    |      Bc     |   AT   |   1   |   21%   |

But I want to be able to make an SQL statement that'll give me the query like this

Category | Subcategory | Region | Count | Percent | Region | Count | Percent |
    A    |      Aa     |   AT   |   1   |   2%    |   ON   |   3   |   5%    |
    A    |      Ab     | NULL/0 | NULL/0|  NULL/0 |   ON   |   5   |  10%    |
    B    |      Bc     |   AT   |   1   |   21%   | NULL/0 | NULL/0|  NULL/0 |

Would this be possible to do in SQL as a either a select or a stored procedure?

我有一个表在SQL

Category | Subcategory | Region | Count | Percent |
    A    |      Aa     |   ON   |   3   |   5%    |
    A    |      Aa     |   AT   |   1   |   2%    |
    A    |      Ab     |   ON   |   5   |   10%   |
    B    |      Bc     |   AT   |   1   |   21%   |

但我希望能够做一个SQL语句,会给我这样的查询

Category | Subcategory | Region | Count | Percent | Region | Count | Percent |
    A    |      Aa     |   AT   |   1   |   2%    |   ON   |   3   |   5%    |
    A    |      Ab     | NULL/0 | NULL/0|  NULL/0 |   ON   |   5   |  10%    |
    B    |      Bc     |   AT   |   1   |   21%   | NULL/0 | NULL/0|  NULL/0 |

这可以做为一个SQL SELECT语句或存储过程?

answer1: 回答1:

As pointed out in the comments, you should use unique column names. To achieve your desired output, you can use conditional aggregation:

SQL Fiddle

SELECT
    Category,
    SubCategory,
    Region_AT = MAX(CASE WHEN Region = 'AT' THEN Region END),
    Count_AT = MAX(CASE WHEN Region = 'AT' THEN [Count] END),
    Percent_AT = MAX(CASE WHEN Region = 'AT' THEN [Percent] END),
    Region_ON = MAX(CASE WHEN Region = 'ON' THEN Region END),
    Count_ON = MAX(CASE WHEN Region = 'ON' THEN [Count] END),
    Percent_ON = MAX(CASE WHEN Region = 'ON' THEN [Percent] END)
FROM Tbl
GROUP BY Category, SubCategory

If you do not know how many Regions are there, you can use dynamic crosstab:

SQL Fiddle

DECLARE @sql1 NVARCHAR(2000) = '',
        @sql2 NVARCHAR(2000) = '',
        @sql3 NVARCHAR(2000) = ''

SELECT @sql1 =
'SELECT
    Category
    , SubCategory' + CHAR(10)

SELECT @sql2 = @sql2 +
'   , MAX(CASE WHEN Region = ''' + Region + ''' THEN Region END) AS Region_' + Region + CHAR(10) +
'   , MAX(CASE WHEN Region = ''' + Region + ''' THEN [Count] END) AS Count_' + Region + CHAR(10) +
'   , MAX(CASE WHEN Region = ''' + Region + ''' THEN [Percent] END) AS Percent_' + Region + CHAR(10)
FROM(
    SELECT DISTINCT Region FROM Tbl
)t

SELECT @sql3 = 
'FROM Tbl
GROUP BY Category, SubCategory'

PRINT (@sql1 + @sql2 + @sql3)
EXEC (@sql1 + @sql2 + @sql3)

You can read more about dynamic crosstab in this great article by Jeff Moden.

正如注释中指出的,您应该使用唯一的列名。为了实现您想要的输出,您可以使用条件聚合:

SQL的小提琴

SELECT
    Category,
    SubCategory,
    Region_AT = MAX(CASE WHEN Region = 'AT' THEN Region END),
    Count_AT = MAX(CASE WHEN Region = 'AT' THEN [Count] END),
    Percent_AT = MAX(CASE WHEN Region = 'AT' THEN [Percent] END),
    Region_ON = MAX(CASE WHEN Region = 'ON' THEN Region END),
    Count_ON = MAX(CASE WHEN Region = 'ON' THEN [Count] END),
    Percent_ON = MAX(CASE WHEN Region = 'ON' THEN [Percent] END)
FROM Tbl
GROUP BY Category, SubCategory

如果你不知道很多地区都有,你可以使用动态交叉表:

SQL的小提琴

DECLARE @sql1 NVARCHAR(2000) = '',
        @sql2 NVARCHAR(2000) = '',
        @sql3 NVARCHAR(2000) = ''

SELECT @sql1 =
'SELECT
    Category
    , SubCategory' + CHAR(10)

SELECT @sql2 = @sql2 +
'   , MAX(CASE WHEN Region = ''' + Region + ''' THEN Region END) AS Region_' + Region + CHAR(10) +
'   , MAX(CASE WHEN Region = ''' + Region + ''' THEN [Count] END) AS Count_' + Region + CHAR(10) +
'   , MAX(CASE WHEN Region = ''' + Region + ''' THEN [Percent] END) AS Percent_' + Region + CHAR(10)
FROM(
    SELECT DISTINCT Region FROM Tbl
)t

SELECT @sql3 = 
'FROM Tbl
GROUP BY Category, SubCategory'

PRINT (@sql1 + @sql2 + @sql3)
EXEC (@sql1 + @sql2 + @sql3)

你可以阅读更多关于动态交叉表在这篇大文章Jeff Moden。

sql  sql-server