Predefined column names in SQL Server pivot table

the other day I opened a topic here in StackOverflow (stackoverflow.com/questions/4663698/how-can-i-display-a-consolidated-version-of-my-sql-server-table). At that time I needed help on how to show data on a pivot table. From the help I got here in the forum, my research led me to this page about dynamic SQL: www.sommarskog.se/dynamic_sql.html. And then it led me to this awesome SQL script by Itzik Ben-Gan that will create a stored procedure that outputs a pivot table exactly the way I want: sommarskog.se/pivot_sp.sp.

Well, almost. I need one change in this stored procedure. Instead of having dynamic column names pulled from the @on_cols variable in the SPROC, I need the output table to hold generic column names in simple ASC order. Could be, for example, col1, col2, col3, col4 ... The dynamic column names are a problem for me. So I need them named by their index in the order they appear. I have tried all sorts of things changing this great SQL script, but it w开发者_StackOverflow中文版on't work.

I did not paste the code from the author because it is too long, but the link above will get us there.

Any help appreciated. Thank you very much

You need this helper function, or something similar to split a delimited list (the column names) to rows

CREATE function dbo.values2table
(
@values varchar(max),
@separator varchar(3),
@limit int -- set to -1 for no limit
) returns @res table (id int identity, [value] varchar(max))
as
begin
declare @value varchar(50)
declare @commapos int, @lastpos int
set @commapos = 0
select @lastpos = @commapos, @commapos = charindex(@separator, @values, @lastpos+1)
while @commapos > @lastpos and @limit <> 0
begin
    select @value = substring(@values, @lastpos+1, @commapos-@lastpos-1)
    if @value <> '' begin
        insert into @res select ltrim(rtrim(@value))
        set @limit = @limit-1
    end
    select @lastpos = @commapos, @commapos = charindex(@separator, @values, @lastpos+1)
end
select @value = substring(@values, @lastpos+1, len(@values))
if @value <> '' insert into @res select ltrim(rtrim(@value))
return
end
GO

Then change the pivot_sp proc to the below (SQL Server supports multi-line strings, so I dropped all the "+ @newline +"s)

CREATE PROC dbo.pivot_sp
  @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'MAX', -- Aggregate function.
  @agg_col  AS NVARCHAR(MAX),  -- Column to aggregate.
  @generic  AS bit = 0,
  @debug    AS bit = 1
AS

-- 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 = 
      STUFF(
        (SELECT N'','' +  quotename(
           CAST(pivot_col AS sysname)
           )  AS [text()]
         FROM (SELECT DISTINCT '
           + @on_cols + N' AS pivot_col
               FROM' + @query + N') AS DistinctCols
         ORDER BY pivot_col
         FOR XML PATH(''''))
        ,1, 1, N'''');'

  IF @debug = 1
     PRINT @sql

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

  DECLARE @colsout nvarchar(max)
  if @generic = 1
    select @colsout = coalesce(@colsout,'') + value + '] as col'+right(id,10)
    from dbo.values2table(left(@cols, LEN(@cols)-1), '],[', -1) X
  else
    select @colsout = @cols

  -- Create the PIVOT query
  SET @sql = N'
    SELECT ' + @on_rows + ',' + @colsout + '
      FROM (SELECT '
              + @on_rows
              + N', ' + @on_cols + N' AS pivot_col'
              + N', ' + @agg_col + N' AS agg_col
          FROM ' + @query + N')' +
              + N' AS PivotInput
      PIVOT(' + @agg_func + N'(agg_col)
        FOR pivot_col IN(' + @cols + N')) AS PivotOutput;'

    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
GO

And here is an example you should be able to run from any SQL Server db.

exec dbo.pivot_sp @query = '
select o.name object_name, c.system_type_id, c.name
from sys.columns c
inner join sys.objects o on o.object_id=c.object_id
where o.is_ms_shipped = 0',
@on_rows  = 'object_name',
@on_cols  = 'system_type_id',
@agg_func = N'COUNT', -- Aggregate function.
@agg_col  = 'name',
@generic=1,
@debug=1