如何在SQL select语句中动态创建列

Question

我有三个表格：Team（团队）、Option（选项）、OptionTeam（选项团队）
Team（团队）包含TeamId和Name
Option（选项）包含OptionId和OptionGroup
OptionTeam（选项团队）包含TeamId、OptionId和OptionGroup

select a.TeamId, a.Name
(select count(*) from OptionTeam ot where ot.TeamId=a.TeamId and ot.OptionGroup=4) as Option1,
(select count(*) from OptionTeam ot where ot.TeamId=a.TeamId and ot.OptionGroup=5) as Option2,
(select count(*) from OptionTeam ot where ot.TeamId=a.TeamId and ot.OptionGroup=6) as Option3,
(select count(*) from OptionTeam ot where ot.TeamId=a.TeamId and ot.OptionGroup=11) as Option4
from Team a 

我想获取一个团队列表，并且额外的列显示每个团队连接到每个选项组的选项数量。可以通过上述查询实现，但是我想使用选项表中的OptionGroup值替换4,5,6,11。

必须是动态的，因为将来可能会有新的选项组，我希望存储过程能够处理它。

示例数据：

Team  
TeamId  
1  
2  
3  

选项

OptionId | OptionGroup  
11 | 4  
12 | 5  
13 | 4  
14 | 4  
15 | 5  

OptionTeam

TeamId | OptionId | OptionGroup  
1 | 11 | 4  
1 | 13 | 4  
2 | 12 | 5  
2 | 14 | 4  
3 | 15 | 5  

And the list I want to get is

TeamId | Group4 (OptionGroup=4) | Group5 (OptionGroup=5)  
1 | 2 | 0  
2 | 1 | 1  
3 | 0 | 1  

- Stavros

---

你使用的是哪种关系型数据库管理系统？MySQL、SQL Server等？ - JNK

Answer 1

您需要一个动态枢轴来实现此目的。以下是存储过程:

CREATE PROC [dbo].[pivotsp]
      @query    AS NVARCHAR(MAX),                   -- The query, can also be the name of a table/view.
      @on_rows  AS NVARCHAR(MAX),                   -- The columns that will be regular rows.
      @on_cols  AS NVARCHAR(MAX),                   -- The columns that are to be pivoted.
      @agg_func AS NVARCHAR(257) = N'SUM',          -- Aggregate function.
      @agg_col  AS NVARCHAR(MAX),                   -- Column to aggregate.
      @output   AS NVARCHAR(257) = N'',             -- Table for results
      @debug    AS bit = 0                          -- 1 for debugging
    AS

    -- Example usage:
    --    exec pivotsp
    --          'select * from vsaleshistory',
    --          'market,marketid,family,familyid,Forecaster,Forecasterid,product,productid',
    --          'month',
    --          'sum',
    --          'ku',
    --          '##sales'

    -- Input validation
    IF @query IS NULL OR @on_rows IS NULL OR @on_cols IS NULL
       OR @agg_func IS NULL OR @agg_col IS NULL
    BEGIN
      RAISERROR('Invalid input parameters.', 16, 1);
      RETURN;
    END

    -- Additional input validation goes here (SQL Injection attempts, etc.)

    BEGIN TRY
      DECLARE
        @sql     AS NVARCHAR(MAX),
        @cols    AS NVARCHAR(MAX),
        @newline AS NVARCHAR(2);

      SET @newline = NCHAR(13) + NCHAR(10);

      -- If input is a valid table or view
      -- construct a SELECT statement against it
      IF COALESCE(OBJECT_ID(@query, N'U'),
                  OBJECT_ID(@query, N'V')) IS NOT NULL
        SET @query = N'SELECT * FROM ' + @query;

      -- Make the query a derived table
      SET @query = N'(' + @query + N') AS Query';

      -- Handle * input in @agg_col
      IF @agg_col = N'*'
        SET @agg_col = N'1';

      -- Construct column list
      SET @sql =
          N'SET @result = '                                    + @newline +
          N'  STUFF('                                          + @newline +
          N'    (SELECT N'','' +  quotename( '
                       + 'CAST(pivot_col AS sysname)' +
                       + ')  AS [text()]'                          + @newline +
          N'     FROM (SELECT DISTINCT('
                       + @on_cols + N') AS pivot_col'              + @newline +
          N'           FROM' + @query + N') AS DistinctCols'   + @newline +
          N'     ORDER BY pivot_col'                           + @newline +
          N'     FOR XML PATH(''''))'                          + @newline +
          N'    ,1, 1, N'''');'

      IF @debug = 1
         PRINT @sql

      EXEC sp_executesql
        @stmt   = @sql,
        @params = N'@result AS NVARCHAR(MAX) OUTPUT',
        @result = @cols OUTPUT;

      IF @debug = 1
         PRINT @cols

      -- Create the PIVOT query
      IF @output = N''
          begin
            SET @sql =
                N'SELECT *'                                          + @newline +
                N'FROM (SELECT '
                              + @on_rows
                              + N', ' + @on_cols + N' AS pivot_col'
                              + N', ' + @agg_col + N' AS agg_col'        + @newline +
                N'      FROM ' + @query + N')' +
                              + N' AS PivotInput'                        + @newline +
                N'  PIVOT(' + @agg_func + N'(agg_col)'               + @newline +
                N'    FOR pivot_col IN(' + @cols + N')) AS PivotOutput;'
          end
      ELSE
          begin
            set @sql = 'IF  EXISTS (SELECT * FROM tempdb.sys.objects WHERE  ' +
                'name = ''' + @output + ''' AND type = N''U'') DROP TABLE tempdb.' + @output
            EXEC sp_executesql @sql;

            SET @sql =
                N'SELECT * INTO ' + @output                          + @newline +
                N'FROM (SELECT '
                              + @on_rows
                              + N', ' + @on_cols + N' AS pivot_col'
                              + N', ' + @agg_col + N' AS agg_col'        + @newline +
                N'      FROM ' + @query + N')' +
                              + N' AS PivotInput'                        + @newline +
                N'  PIVOT(' + @agg_func + N'(agg_col)'               + @newline +
                N'    FOR pivot_col IN(' + @cols + N')) AS PivotOutput;'
          end

        IF @debug = 1
           PRINT @sql

        EXEC sp_executesql @sql;
    END TRY
    BEGIN CATCH
      DECLARE
        @error_message  AS NVARCHAR(2047),
        @error_severity AS INT,
        @error_state    AS INT;

      SET @error_message  = ERROR_MESSAGE();
      SET @error_severity = ERROR_SEVERITY();
      SET @error_state    = ERROR_STATE();

      RAISERROR(@error_message, @error_severity, @error_state);

      RETURN;
    END CATCH

有了这个，就可以轻松地在可变数量的列上进行旋转：

EXEC pivotsp
        'SELECT TeamID, OptionGroup, OptionID AS Options FROM OptionTeam',
        'Teamid',        -- Row headers
        'optiongroup',   -- item to aggregate
        'count',         -- aggregation function
        'optiongroup',   -- Column header
        '##temp'         -- output table name
    SELECT * FROM ##temp

结果：

   Teamid   4   5
    1   2   0
    2   1   1
    3   0   1

- smirkingman

Answer 2

SELECT  a.*, o.optionGroup, COUNT(*)
FROM    team a
CROSS JOIN
        option o
JOIN    OptionTeam ot
ON      ot.teamId = a.teamId
        AND ot.optionGroup = o.optionGroup
WHERE   o.OptionId = @id
GROUP BY
        a.teamId, o.optionGroup

- Quassnoi

---

你从哪里获取 @id？ - Stavros

@Stavros：这就是我该问你的问题 :) 请发布您的表格的示例数据以及您想要获得的输出。 - Quassnoi

@Quassnoi：我刚刚添加了示例数据 :) - Stavros

@Stavros：所以如果有第三个“optiongroup”，你希望为它添加第三列吗？ - Quassnoi

@Stavros：除非您创建一个动态查询（在运行时组成的查询），否则无法使用SQL完成此操作。 - Quassnoi

Answer 3

select teamID,
sum(case when optionGroup = 4 then 1 else 0 end) as optionGroup4,
sum(case when optionGroup = 5 then 1 else 0 end) as optionGroup5,
from optionteam
group by teamID

如果想要添加更多的选项组而不改变代码，请尝试按该字段进行分组：

select teamID,optionGroup,count(optionID) as optionCount
from optionteam
group by teamID,optionGroup

- Beth

---

这不会创建不同的列 :( - Stavros