declare @s varchar(1000) ;with cte as ( Select distinct rid = ROW_NUMBER() over (partition by r1 order by getdate()) From [tbb] ) select @s = ISNULL(@s+',','')+'max(case when rid = '+LTRIM(rid)+' then state else null end) as 状态'+ltrim(rid)+',' +'max(case when rid = '+LTRIM(rid)+' then [date] else null end) as 日期'+ltrim(rid) from cte exec('with cte as ( Select *,rid = ROW_NUMBER() over (partition by r1 order by getdate()) From [tbb] ) select a.id,a.[name],'+ @s+' from taa a left join cte b on a.r1= b.r1 group by a.id,a.name')
create table A (id int,name varchar(10),r1 int) insert into A select 1,'aa',22 union all select 2,'bb',33 union all select 3,'cc',22 union all select 4,'dd',44 union all select 5,'ee',55
create table B(r1 int,[state] varchar(10),[date] datetime) insert into B select 22,'一级','2011-2-3' union all select 22,'二级','2011-5-3' union all select 33,'三级','2011-3-3' union all select 44,'六级','2011-3-27' union all select 33,'五级','2011-3-5' union all select 33,'八级','2011-3-8' --开始查询 --放到第一个全局临时表里 select no=ROW_NUMBER() over(partition by r1 order by [date]),* into ##tb1 from B --动态行转列B表放到第二个临时表 declare @sql varchar(8000) set @sql = 'select r1 ' select @sql = @sql + ' , max(case [no] when ' + rtrim(no) + ' then [state] end) [状态' + rtrim(no) + '],'+ ' max(case [no] when ' + rtrim(no) + ' then [date] end) [日期' + rtrim(no) + ']' from ##tb1 group by no set @sql = @sql + ' into ##tb2 from ##tb1 group by r1' exec(@sql) --左连接A表 select * from A left join ##tb2 on A.r1=##tb2.r1 --删掉临时表 drop table ##tb1,##tb2/* id name r1 r1 状态1 日期1 状态2 日期2 状态3 日期3 ----------- ---------- ----------- ----------- ---------- ----------------------- ---------- ----------------------- ---------- ----------------------- 1 aa 22 22 一级 2011-02-03 00:00:00.000 二级 2011-05-03 00:00:00.000 NULL NULL 2 bb 33 33 三级 2011-03-03 00:00:00.000 五级 2011-03-05 00:00:00.000 八级 2011-03-08 00:00:00.000 3 cc 22 22 一级 2011-02-03 00:00:00.000 二级 2011-05-03 00:00:00.000 NULL NULL 4 dd 44 44 六级 2011-03-27 00:00:00.000 NULL NULL NULL NULL 5 ee 55 NULL NULL NULL NULL NULL NULL NULL(5 行受影响)
好像有点不同,现写的,没测试 declare @Count int declare @i int select top 1 @Count = COUNT(*) from b group by r1declare @sql varchar(8000)set @i = 1 while @i <= @Count begin set @sql = @sql + ',max(case when num = ' + CAST(@i as varchar(100)) + ' then 状态 end) as [状态' + CAST(@i as varchar(100)) + ']' + ',max(case when num = ' + CAST(@i as varchar(100)) + ' then 日期 end) as [日期' + CAST(@i as varchar(100)) + ']' set @i = @i + 1 endexec(' ;with cte as ( select *,row_number() over(PARTITION BY r1 order by date) as num from b ) select a.id,a.name,a.r1 ' + @sql + ' from a,cte b where a.r1= b.r1 group by a.id,a.name,a.r1 ')
改改 declare @Count int declare @i int select top 1 @Count = COUNT(*) from b group by r1declare @sql varchar(8000)set @i = 1 while @i <= @Count begin set @sql = @sql + ',max(case when b.num = ' + CAST(@i as varchar(100)) + ' then 状态 end) as [状态' + CAST(@i as varchar(100)) + ']' + ',max(case when b.num = ' + CAST(@i as varchar(100)) + ' then 日期 end) as [日期' + CAST(@i as varchar(100)) + ']' set @i = @i + 1 endexec(' ;with cte as ( select *,row_number() over(PARTITION BY r1 order by date) as num from b ) select a.id,a.name,a.r1 ' + @sql + ' from a left join cte b on a.r1= b.r1 group by a.id,a.name,a.r1 ')
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2011-08-22 17:28:05 -- Verstion: -- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[A] if object_id('[A]') is not null drop table [A] go create table [A]([id] int,[name] varchar(2),[r1] int) insert [A] select 1,'aa',22 union all select 2,'bb',33 union all select 3,'cc',22 union all select 4,'dd',44 union all select 5,'ee',55 --> 测试数据:[B] if object_id('[B]') is not null drop table [B] go create table [B]([r1] int,[state] varchar(4),[date] datetime) insert [B] select 22,'一级','2011-2-3' union all select 22,'二级','2011-5-3' union all select 33,'三级','2011-3-3' union all select 44,'六级','2011-3-27' union all select 33,'五级','2011-3-5' union all select 33,'八级','2011-3-8' --------------开始查询-------------------------- declare @s varchar(1000) ;with cte as ( Select distinct rid = ROW_NUMBER() over (partition by r1 order by getdate()) From [b] ) select @s = ISNULL(@s+',','')+'max(case when rid = '+LTRIM(rid)+' then state else null end) as 状态'+ltrim(rid)+',' +'max(case when rid = '+LTRIM(rid)+' then [date] else null end) as 日期'+ltrim(rid) from cte exec('with cte as ( Select *,rid = ROW_NUMBER() over (partition by r1 order by getdate()) From [b] ) select a.id,a.[name],'+ @s+' from a left join cte b on a.r1= b.r1 group by a.id,a.name') ----------------结果---------------------------- /* id name 状态1 日期1 状态2 日期2 状态3 日期3 ----------- ---- ---- ----------------------- ---- ----------------------- ---- ----------------------- 1 aa 一级 2011-02-03 00:00:00.000 二级 2011-05-03 00:00:00.000 NULL NULL 2 bb 三级 2011-03-03 00:00:00.000 五级 2011-03-05 00:00:00.000 八级 2011-03-08 00:00:00.000 3 cc 一级 2011-02-03 00:00:00.000 二级 2011-05-03 00:00:00.000 NULL NULL 4 dd 六级 2011-03-27 00:00:00.000 NULL NULL NULL NULL 5 ee NULL NULL NULL NULL NULL NULL 警告: 聚合或其他 SET 操作消除了 Null 值。(5 行受影响)*/
declare @s varchar(1000)
;with cte
as
(
Select distinct rid = ROW_NUMBER() over (partition by r1 order by getdate())
From [tbb]
)
select @s = ISNULL(@s+',','')+'max(case when rid = '+LTRIM(rid)+' then state else null end) as 状态'+ltrim(rid)+','
+'max(case when rid = '+LTRIM(rid)+' then [date] else null end) as 日期'+ltrim(rid)
from cte
exec('with cte
as
(
Select *,rid = ROW_NUMBER() over (partition by r1 order by getdate())
From [tbb]
)
select a.id,a.[name],'+ @s+' from taa a left join cte b on a.r1= b.r1 group by a.id,a.name')
insert into A
select 1,'aa',22 union all
select 2,'bb',33 union all
select 3,'cc',22 union all
select 4,'dd',44 union all
select 5,'ee',55
create table B(r1 int,[state] varchar(10),[date] datetime)
insert into B
select 22,'一级','2011-2-3' union all
select 22,'二级','2011-5-3' union all
select 33,'三级','2011-3-3' union all
select 44,'六级','2011-3-27' union all
select 33,'五级','2011-3-5' union all
select 33,'八级','2011-3-8' --开始查询
--放到第一个全局临时表里
select no=ROW_NUMBER() over(partition by r1 order by [date]),* into ##tb1 from B --动态行转列B表放到第二个临时表
declare @sql varchar(8000)
set @sql = 'select r1 '
select @sql = @sql + ' , max(case [no] when ' + rtrim(no) + ' then [state] end) [状态' + rtrim(no) + '],'+
' max(case [no] when ' + rtrim(no) + ' then [date] end) [日期' + rtrim(no) + ']'
from ##tb1 group by no
set @sql = @sql + ' into ##tb2 from ##tb1 group by r1'
exec(@sql)
--左连接A表
select * from A left join ##tb2 on A.r1=##tb2.r1
--删掉临时表
drop table ##tb1,##tb2/*
id name r1 r1 状态1 日期1 状态2 日期2 状态3 日期3
----------- ---------- ----------- ----------- ---------- ----------------------- ---------- ----------------------- ---------- -----------------------
1 aa 22 22 一级 2011-02-03 00:00:00.000 二级 2011-05-03 00:00:00.000 NULL NULL
2 bb 33 33 三级 2011-03-03 00:00:00.000 五级 2011-03-05 00:00:00.000 八级 2011-03-08 00:00:00.000
3 cc 22 22 一级 2011-02-03 00:00:00.000 二级 2011-05-03 00:00:00.000 NULL NULL
4 dd 44 44 六级 2011-03-27 00:00:00.000 NULL NULL NULL NULL
5 ee 55 NULL NULL NULL NULL NULL NULL NULL(5 行受影响)
declare @Count int
declare @i int
select top 1 @Count = COUNT(*)
from b
group by r1declare @sql varchar(8000)set @i = 1
while @i <= @Count
begin
set @sql = @sql + ',max(case when num = ' + CAST(@i as varchar(100)) + ' then 状态 end) as [状态' + CAST(@i as varchar(100)) + ']'
+ ',max(case when num = ' + CAST(@i as varchar(100)) + ' then 日期 end) as [日期' + CAST(@i as varchar(100)) + ']'
set @i = @i + 1
endexec('
;with cte as (
select *,row_number() over(PARTITION BY r1 order by date) as num from b
)
select a.id,a.name,a.r1
' + @sql + '
from a,cte b
where a.r1= b.r1
group by a.id,a.name,a.r1 ')
declare @Count int
declare @i int
select top 1 @Count = COUNT(*)
from b
group by r1declare @sql varchar(8000)set @i = 1
while @i <= @Count
begin
set @sql = @sql + ',max(case when b.num = ' + CAST(@i as varchar(100)) + ' then 状态 end) as [状态' + CAST(@i as varchar(100)) + ']'
+ ',max(case when b.num = ' + CAST(@i as varchar(100)) + ' then 日期 end) as [日期' + CAST(@i as varchar(100)) + ']'
set @i = @i + 1
endexec('
;with cte as (
select *,row_number() over(PARTITION BY r1 order by date) as num from b
)
select a.id,a.name,a.r1
' + @sql + '
from a
left join cte b
on a.r1= b.r1
group by a.id,a.name,a.r1 ')
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-08-22 17:28:05
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([id] int,[name] varchar(2),[r1] int)
insert [A]
select 1,'aa',22 union all
select 2,'bb',33 union all
select 3,'cc',22 union all
select 4,'dd',44 union all
select 5,'ee',55
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([r1] int,[state] varchar(4),[date] datetime)
insert [B]
select 22,'一级','2011-2-3' union all
select 22,'二级','2011-5-3' union all
select 33,'三级','2011-3-3' union all
select 44,'六级','2011-3-27' union all
select 33,'五级','2011-3-5' union all
select 33,'八级','2011-3-8'
--------------开始查询--------------------------
declare @s varchar(1000)
;with cte as
(
Select distinct rid = ROW_NUMBER() over (partition by r1 order by getdate()) From [b]
)
select @s = ISNULL(@s+',','')+'max(case when rid = '+LTRIM(rid)+' then state else null end) as 状态'+ltrim(rid)+','
+'max(case when rid = '+LTRIM(rid)+' then [date] else null end) as 日期'+ltrim(rid)
from cte
exec('with cte
as
(
Select *,rid = ROW_NUMBER() over (partition by r1 order by getdate())
From [b]
)
select a.id,a.[name],'+ @s+' from a left join cte b on a.r1= b.r1 group by a.id,a.name')
----------------结果----------------------------
/* id name 状态1 日期1 状态2 日期2 状态3 日期3
----------- ---- ---- ----------------------- ---- ----------------------- ---- -----------------------
1 aa 一级 2011-02-03 00:00:00.000 二级 2011-05-03 00:00:00.000 NULL NULL
2 bb 三级 2011-03-03 00:00:00.000 五级 2011-03-05 00:00:00.000 八级 2011-03-08 00:00:00.000
3 cc 一级 2011-02-03 00:00:00.000 二级 2011-05-03 00:00:00.000 NULL NULL
4 dd 六级 2011-03-27 00:00:00.000 NULL NULL NULL NULL
5 ee NULL NULL NULL NULL NULL NULL
警告: 聚合或其他 SET 操作消除了 Null 值。(5 行受影响)*/