尽管您只有Name1和Name2 ATM，但这是动态数据透视的典型示例.

Although you only have Name1 and Name2 ATM, this is a typical example of a dynamic pivot.

创建以下SP:

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

现在事情变得更容易了.从输入中

Now things become easier. From the input

1   loca    namea   st1 1   house1  2   2007    1234
2   loca    namea   st1 1   house1  2   2007    2345
3   loca    namea   st1 1   house1  2   2007    3456
4   loca    namea   st1 1   house1  2   2008    6789
5   loca    namea   st1 1   house1  2   2008    7890
6   loca    nameb   st1 1   house1  2   2007    1234
7   locc    nameb   st1 1   house1  2   2007    2345
8   loca    nameb   st1 1   house1  2   2007    3456
9   loca    nameb   st1 1   house1  2   2008    6789
10  locc    nameb   st1 1   house1  2   2008    7890

我们按名称和年份进行汇总

we aggregate by name and year

SELECT
    location, sales_type, local_id, house_description, sales_order,
    [name] + '_' + cast(order_year AS varchar(20)) as nameyear, 
    max(amount) as amount
INTO
    ##crosstab
FROM
    working
GROUP BY
    location, sales_type, local_id, house_description, sales_order,
    [name] + '_' + cast(order_year AS varchar(20))

给予

loca    st1 1   house1  2   namea_2007  3456
loca    st1 1   house1  2   namea_2008  7890
loca    st1 1   house1  2   nameb_2007  3456
loca    st1 1   house1  2   nameb_2008  6789
locc    st1 1   house1  2   nameb_2007  2345
locc    st1 1   house1  2   nameb_2008  7890

然后，使用pivot_sp

then, using the pivot_sp

EXEC pivotsp
      'select * from ##crosstab',
      'location, sales_type, local_id, house_description, sales_order',
      'nameyear',
      'max',
      'amount',
      '##answer'

SELECT 
    *
FROM
    ##answer

我们获得

locat   st   local      house  so  namea_2007 namea_2008 nameb_2007 nameb_2008
loca    st1 1   house1  2   3456    7890    3456    6789
locc    st1 1   house1  2   NULL    NULL    2345    7890

HTH