34,837
社区成员




----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-12-11 09:22:30
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([dept_name] varchar(5),[dept_attribute] varchar(1))
insert [tb]
select 'Dept1','A' union all
select 'Dept1','B' union all
select 'Dept1','C' union all
select 'Dept1','D' union all
select 'Dept2','B' union all
select 'Dept2','C' union all
select 'Dept2','D' union all
select 'Dept3','A' union all
select 'Dept3','B' union all
select 'Dept3','C' union all
select 'Dept3','D' union all
select 'Dept3','E'
--------------开始查询--------------------------
declare @sql nvarchar(max),@sql1 varchar(max),@name varchar(50)
set @sql=''
set @sql1=''
set @sql=@sql+'select distinct @name=stuff((select '',''+[name] from syscolumns where id=object_id(''Tb'') for xml path('''')), 1, 1, '''') from syscolumns'
exec sp_executesql @sql,N'@name varchar(50) output',@name output
--select @name
set @sql1=@sql1+'select '+@name+' from tb'
exec( @sql1)
----------------结果----------------------------
/* dept_attribute dept_name
-------------- ---------
A Dept1
B Dept1
C Dept1
D Dept1
B Dept2
C Dept2
D Dept2
A Dept3
B Dept3
C Dept3
D Dept3
E Dept3
(12 行受影响)
*/
declare @sql nvarchar(max),@sql1 varchar(max),@name varchar(50)
set @sql=''
set @sql1=''
set @sql=@sql+'select distinct @name=stuff((select '',''+[name] from syscolumns where id=object_id(''Tb'') for xml path('''')), 1, 1, '''') from syscolumns'
exec sp_executesql @sql,N'@name varchar(50)',@name
set @sql1=@sql1+'select '+@name+' from tb'
exec( @sql1)
大概这样 你自己去调试一下select row_number() over(order by RCID) as ID,ColName into #temp from Report_Column where TableName='Students'
declare @i int,@Count INT
,@ColName VARCHAR(200),@Str VARCHAR(50)
set @i = 1
select @Count = count(1) from #temp
while(@i <= @Count)
begin
select @Str = ColName from #temp where ID = @i
if(@ColName is null)
set @ColName=@Str
else
set @ColName=@ColName+','+@Str
set @i = @i + 1
end
declare @SQL varchar(2000)
set @SQL = 'select ' + @ColName + ' from Students'
exec(@SQL)