将多条记录转换为一列

Question

在 SQL 2008 中，我有下表，需要转换为下表，为每个

Date    Name    Colors
Nov01   John    Red
Nov02   Mike    Green
Nov02   Mike    Blue
Nov02   Mike    Grey
Nov03   Melissa Yellow
Nov03   Melissa Orange
Nov10   Rita    Pink
Nov10   Rita    Red

转换

Date    Name    Red Green   Blue    Grey    Yellow  Orange
Nov01   John    Red
Nov02   Mike        Green   Blue    Grey
Nov03   Melissa                 Yellow  Orange
Nov10   Rita    Red                     Pink

后创建一条记录，请参见下图，因为我不知道如何在此处格式化表

非常感谢。

Answer 1

请参阅：

如何将行旋转为列（自定义旋转）

Answer 2

看起来您想要一个动态枢轴。

添加这个存储过程：

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 * from mytable',
  '[date],name',
  'colours',
  'min',
  '#temp'
select * from #temp

（MIN 有点麻烦，但它应该会产生你想要的结果）