--创建数据测试环境
create table #tb (姓名 varchar(10), 房间号码 varchar(10),单位 varchar(10))
insert into #tb
select '001','101',1
union all select '002','110',1
union all select '003','120',1
union all select '004','202',1
union all select '004','212',1
union all select '004','302',1
union all select '005','606',2
union all select '006','706',2
union all select '006','755',2
union all select '006','346',2
union all select '007','118',3
union all select '007','128',3
select * from #tb
--得到想要的结果
declare @i int,@sql varchar(1000)
select @sql='',@i=aa
from(select top 1 单位,count(房间号码) as aa
from #tb group by 单位 order by count(房间号码) desc) a
while @i>0
select @sql=char(13)+',max(case a.id-b.id when '+cast(@i-1 as varchar)
+' then a.房间号码 end) as ['+cast(@i as varchar)
+']'+@sql
,@i=@i-1
set @sql='select a.单位'+char(13)+@sql
+char(13)+' from #temp a'
+char(13)+' ,(select 单位,min(id) as id from #temp group by 单位) b'
+char(13)+'where a.单位=b.单位'
+char(13)+'group by a.单位'
--print @sql
--为数据处理准备临时表
select id=identity(int,1,1),* into #temp from #tb
exec(@sql)
我是菜鸟呀,可否详细解释一下?为什么我在引用
select distinct RIGHT(B ,1) B INTO #1 from #
declare @sql varchar(1000)
set @sql =''
select @sql=@sql +',MAX(case when RIGHT(B,1) = ''' + RIGHT(B ,1) + ''' then B else null end) as ' + 'S'+RIGHT(B,1)
from #1
exec ('select c '+ @sql+ ' from # group by C')
--创建数据测试环境
create table #tb (姓名 varchar(10), 房间号码 varchar(10),单位 varchar(10))
insert into #tb
select '001','101',1
union all select '002','110',1
union all select '003','120',1
union all select '004','202',1
union all select '004','212',1
union all select '004','302',1
union all select '005','606',2
union all select '006','706',2
union all select '006','755',2
union all select '006','346',2
union all select '007','118',3
union all select '007','128',3
select * from #tb
go
--得到想要的结果
create procedure pbuild
as
declare @i int,@sql varchar(1000)
select @sql='',@i=aa
from(select top 1 单位,count(房间号码) as aa
from #tb group by 单位 order by count(房间号码) desc) a
while @i>0
select @sql=char(13)+',max(case a.id-b.id when '+cast(@i-1 as varchar)
+' then a.房间号码 end) as ['+cast(@i as varchar)
+']'+@sql
,@i=@i-1
set @sql='select a.单位'+char(13)+@sql
+char(13)+' from #temp a'
+char(13)+' ,(select 单位,min(id) as id from #temp group by 单位) b'
+char(13)+'where a.单位=b.单位'
+char(13)+'group by a.单位'
--print @sql
--为数据处理准备临时表
select id=identity(int,1,1),* into #temp from #tb
exec(@sql)
go
exec pbuild
--删除测试数据
drop table #tb
drop procedure pbuild
create table # (a varchar(10), b varchar(10),C VARCHAR(10))
INSERT INTO # VALUES('001', '501', '1')
INSERT INTO # VALUES('002', '502', '1')
INSERT INTO # VALUES('003', '503', '1')
INSERT INTO # VALUES('004', '401', '2')
INSERT INTO # VALUES('005', '402', '2')
SELECT * FROM #
select distinct RIGHT(B ,1) B INTO #1 from #
declare @sql varchar(1000)
set @sql =''
select @sql=@sql +',MAX(case when RIGHT(B,1) = ''' + RIGHT(B ,1) + ''' then B else null end) as ' + 'S'+RIGHT(B,1)
from #1
exec ('select c '+ @sql+ ' from # group by C')
declare @sql varchar(8000)
set @sql = 'select '
select @sql = @sql + 'sum(case a when '''+a+'''
then b else 0 end) '+a+'的数量,'
from (select distinct a from #) as a
create function f_getb(@c int)
returns varchar(200)
as
begin
declare @v varchar(200)
set @v=''
select @v=@v+space(2)+rtrim(b) from your_table
where c=@c
return @v
end
--调用
select c,dbo.f_getb(c) from your_table
group by c
declare @str varchar(8000)
set @str='select * from ('
select @str=@str+' select 固定字段,'+name +' from 表 union all ' from syscolumns where object_id('表')=id and Name<>'固定字段名' ---这里要去除固定字段
if @str<>''
set @str=left(@str,len(@str)-10)
set @str=@str +')tem order by 固定字段'
declare @str varchar(8000)
set @str='select * from ('
select @str=@str+' select 固定字段,'+name +' from 表 union all ' from syscolumns where object_id('表')=id and colorder >1
if @str<>''
set @str=left(@str,len(@str)-10)
set @str=@str +')tem order by 固定字段'
declare @sql varchar(100)
set @sql =''
select @sql=@sql +',max(case when b = ''' + b + ''' then b else null end as ' + b
from (select distinct b from table ) aa
exec ('select c '+ @sql+ ' from table group by a')