22,209
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :TravyLee
-- Date :2012-11-02 13:02:44
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 6.1 <X86> (Build 7600: )
--
----------------------------------------------------------------
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]([name] varchar(1),[dep] varchar(4))
insert [test]
select 'A','dep1' union all
select 'A','dep2' union all
select 'B','dep1' union all
select 'C','dep2' union all
select 'D','dep1' union all
select 'B','dep3'
go
--2000数据库处理方法:
alter table test
add id int identity(1,1)
go
select
px=(select count(1) from test b where a.dep=b.dep and a.id>=b.id),
[name],
[dep]
into
#tb
from
test a
declare @str varchar(max)
set @str=''
select
@str=@str+',[name'+LTRIM(px)+']=max(case when px='+LTRIM(px)+' then [name] else '+''''''+' end)'
from
#tb
group by
px
exec('select [dep]'+@str+' from #tb group by [dep]')
/*
dep name1 name2 name3
dep1 A B D
dep2 A C
dep3 B
*/
--try
;with c1(staff_user_id, client_id) as
(
select 27, 257 union all
select 135, 257 union all
select 111, 211
),
c2(staff_user_id, staffname) as ---c1,c2 虚视图用来准备数据
(
select 27, '张三' union all
select 111, '李四' union all
select 135, '王二'
)
SELECT d.client_id,
STUFF((SELECT ','+LTRIM(c2.staffname)
FROM c1
JOIN c2 ON c1.staff_user_id = c2.staff_user_id
where client_id = d.client_id FOR XML PATH('')),1,1,'') NAME
FROM c1 d
GROUP BY d.client_id
client_id NAME
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
211 李四
257 张三,王二
(2 行受影响)
declare @sql varchar(8000)
set @sql = 'select dep '
select @sql = @sql + ' , max(case name when ''' + name + ''' then name else '' end) ' from (select distinct name from tb) as a
set @sql = @sql + ' from tb group by dep '
exec(@sql)
declare @sql varchar(8000)
set @sql = 'select dep '
select @sql = @sql + ' , max(case name when ''' + name + ''' then name else '' end) from (select distinct name from tb) as a
set @sql = @sql + ' from tb group by dep '
exec(@sql)
declare @sql varchar(8000)
set @sql = 'select dep '
select @sql = @sql + ' , max(case name when ''' + name + ''' then name else '' end) from (select distinct name from tb) as a
set @sql = @sql + ' from tb group by dep
exec(@sql)
----------------------------------------------------------------
-- Author :TravyLee
-- Date :2012-11-02 13:02:44
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 6.1 <X86> (Build 7600: )
--
----------------------------------------------------------------
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]([name] varchar(1),[dep] varchar(4))
insert [test]
select 'A','dep1' union all
select 'A','dep2' union all
select 'B','dep1' union all
select 'C','dep2' union all
select 'D','dep1' union all
select 'B','dep3'
go
select
px=ROW_NUMBER()over(partition by [dep] order by name),
[name],
[dep]
into
#tb
from
test
--drop table #tb
declare @str varchar(max)
set @str=''
select
@str=@str+',[name'+LTRIM(px)+']=max(case when px='+LTRIM(px)+' then [name] else '+''''''+' end)'
from
#tb
group by
px
exec('select [dep]'+@str+' from #tb group by [dep]')
----------------结果----------------------------
/*
dep name1 name2 name3
---- ----- ----- -----
dep1 A B D
dep2 A C
dep3 B
(3 行受影响)
*/
--更正一下
----------------------------------------------------------------
-- Author :TravyLee
-- Date :2012-11-02 13:02:44
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 6.1 <X86> (Build 7600: )
--
----------------------------------------------------------------
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]([name] varchar(1),[dep] varchar(4))
insert [test]
select 'A','dep1' union all
select 'A','dep2' union all
select 'B','dep1' union all
select 'C','dep2' union all
select 'D','dep1' union all
select 'B','dep3'
go
select
px=ROW_NUMBER()over(partition by [dep] order by (select 1)),
[name],
[dep]
into
#tb
from
test
declare @str varchar(max)
set @str=''
select
@str=@str+',[name'+LTRIM(px)+']=max(case when px='+LTRIM(px)+' then [name] else '+''''''+' end)'
from
#tb
group by
px
exec('select [dep]'+@str+' from #tb group by [dep]')
----------------结果----------------------------
/*
dep name1 name2 name3
dep1 A B D
dep2 C A
dep3 B
*/
USE test
GO
--if object_id('table') is not null
-- drop table [table]
--Go
----test data
--Create table [table]([name] nvarchar(1),[dep] nvarchar(4))
--Insert into [table]
--Select N'A',N'dep1'
--Union all Select N'A',N'dep2'
--Union all Select N'B',N'dep1'
--Union all Select N'C',N'dep2'
--Union all Select N'D',N'dep1'
--Union all Select N'B',N'dep3'
IF object_id('tempdb..#')IS NOT NULL
DROP TABLE #
Go
SELECT N'name'+LTRIM(ROW_NUMBER()OVER(PARTITION BY dep ORDER BY name)) AS col,name,dep INTO # from [table]
DECLARE @Sql NVARCHAR(MAX)
SELECT @Sql=ISNULL(@Sql+',','')+N'MAX(CASE WHEN col=N'''+col+N''' THEN name ELSE '''' END) As '+col FROM #
GROUP BY col
EXEC ( N'SELECT dep,'+@Sql+N' FROM # GROUP BY dep' )
/*
dep name1 name2 name3
---- ----- ----- -----
dep1 A B D
dep2 A C
dep3 B
*/
Go