数据库表行列转换

use pubs
go

declare @table_name varchar(35), -- if the table have too much fields ,this proc may be malfunction
    @column_list nvarchar(4000) -- field list, separated with ,
select @table_name = 'jobs'
select @column_list = '' --'job_id, job_desc'

declare @declare_var_section varchar(8000), @init_var_section varchar(8000),
    @gen_select_stmt varchar(8000), @print_stmt varchar(8000), @select_title varchar(8000),
    @print_final varchar(8000), @combine_all varchar(8000), @exec_stmt  varchar(8000)
select
    @declare_var_section = '', @init_var_section = '', @gen_select_stmt = '', @print_stmt = '', @select_title = '',
    @print_final = '', @combine_all = '', @exec_stmt = ''

select
    @declare_var_section = @declare_var_section + ',@' + COLUMN_NAME + ' NVARCHAR(4000)',
    @init_var_section = @init_var_section + ',@' + COLUMN_NAME + '='''''''+COLUMN_NAME +'''''''',
    @gen_select_stmt = @gen_select_stmt + 'SELECT @' + COLUMN_NAME + '=@' + COLUMN_NAME +
        '+'',''''''+ISNULL(RTRIM(REPLACE(CAST(' + COLUMN_NAME +' AS VARCHAR), '''''''', '''''''''''')), '''') + '''''''' FROM ' +
        @table_name + char(13),
    @print_stmt = @print_stmt +'PRINT @' + COLUMN_NAME + char(13),
    @combine_all = @combine_all + '+ '' UNION SELECT ALL ''+ @' +COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @table_name and
    (isnull(@column_list, '') = '' or charindex(COLUMN_NAME, @column_list) > 0)
order by ORDINAL_POSITION

select
    @declare_var_section = 'DECLARE @sqlstr VARCHAR(8000)' + @declare_var_section + char(13) + char(13),
    @init_var_section = 'SELECT @sqlstr=''''' + @init_var_section + char(13) + char(13),
    @print_final = 'PRINT (@title' + replace(@combine_all, '+', '+ CHAR(13) +') + ')' + char(13) + char(13),
    @exec_stmt = 'EXEC (@title' + @combine_all + ')' + char(13) + char(13),
    @select_title =
    'declare @count int, @title nvarchar(4000)
    select @count = 1, @title = ''''
    select  @title = @title + '',['' +cast(@count as varchar) + '']='''''''''', @count = @count+1 from ' + @table_name +'
    select @title = ''select [0]='''''''''' + @title + '' where 1=2''' + char(13) + char(13)

print '-- BEGIN DUMP SQL STATEMENT --'
print '-- @declare_var_section'
print @declare_var_section
print '-- @init_var_section'
print @init_var_section
print '-- @gen_select_stmt'
print @gen_select_stmt
print '-- @select_title'
print @select_title
print '-- @print_final'
print @print_final
print '-- @exec_stmt'
print @exec_stmt
print '-- END OF DUMP SQL STATEMENT --'
-- print '-- @combine_all'
-- print @combine_all
-- print '-- @print_stmt'
-- print @print_stmt
print ''

exec(@declare_var_section+
    @init_var_section+
    @gen_select_stmt+
    @select_title+
    @print_final+
    @exec_stmt)