本帖最后由 loveyang7733 于 2011-08-22 15:59:58 编辑

解决方案 »

  1.   


    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')
      

  2.   

    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 行受影响)
      

  3.   

    好像有点不同,现写的,没测试
    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 ')
      

  4.   

    改改
    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 ')
      

  5.   

    ----------------------------------------------------------------
    -- 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 行受影响)*/