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