SQL将结果集分组

结果集如下

ID(int)       Name（varcher）         LB(int)         SL(int)
1                               A                             1                     100
2                               A                             2                       80
3                               B                             1                       50
4                               B                             2                       30
5                               C                             1                       20
......

如何利用Name合并成如下结果集
ID       Name           LB1         SL1         LB2         SL2
1             A               1           100           2           80
2             B               1             50           2           30
3             C               1             20           2             0   （注意C结果集不存在LB   =   2的记录）
......

SQL code

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(1),[LB] int,[SL] int)
Insert #T
select 1,N'A',1,100 union all
select 2,N'A',2,80 union all
select 3,N'B',1,50 union all
select 4,N'B',2,30 union all
select 5,N'C',1,20
Go

declare @s nvarchar(4000)
set @s=''
Select     @s=@s+N','+quotename('LB'+RTRIM(LB))+N'=max(case when LB=N'+quotename(LB,'''')+N' then LB else '''' end),'
+quotename('SL'+RTRIM(LB))+N'=max(case when LB=N'+quotename(LB,'''')+N' then SL else '''' end)'

from #T group by LB
--顯示生成語句
print N'select ID=row_number()over(order by min(ID)), Name'+@s+N' from #T group by Name'

exec(N'select ID=row_number()over(order by min(ID)), Name'+@s+N' from #T group by Name')
/*
ID    Name    LB1    SL1    LB2    SL2
1    A    1    100    2    80
2    B    1    50    2    30
3    C    1    20    0    0
*/

SQL code

;with cte as
(
    select a.Name,b.LB
    from (select distinct Name from tb) a cross join (select distinct LB from tb)b
)

select row_number() over (order by getdate()) as id,
    a.Name,1 as LB1,sum(case when a.LB=1 then b.SL else 0 end) SL1,
    2 as LB2,sum(case when a.LB=2 then b.SL else 0 end) SL2
from cte a join tb b on a.Name = b.Name and a.LB = b.LB
group by a.Name

上边换成 left join

LB--沒規律時用

SQL code

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(1),[LB] int,[SL] int)
Insert #T
select 1,N'A',1,100 union all
select 2,N'A',2,80 union all
select 3,N'B',1,50 union all
select 4,N'B',2,30 union all
select 5,N'C',1,20
Go

DECLARE @s NVARCHAR(4000),@i NVARCHAR(3)

Select TOP 1 @i=COUNT(*),@s='' from #T GROUP BY Name order by count(*) desc

WHILE @i>0
    SELECT @s=N',[LB'+@i+']=max(case when Row='+@i+N' then LB else 0 end),'+'[SL'+@i+']=max(case when Row='+@i+N' then SL else 0 end)'+@s,@i=@i-1


EXEC(N'SELECT ID=row_number()over(order by min(ID)),Name'+@s+N'
FROM (select *, row=row_number()over(partition by Name order by Name)from #T) as a
GROUP BY Name')
go
/*
ID    Name    LB1    SL1    LB2    SL2
1    A    1    100    2    80
2    B    1    50    2    30
3    C    1    20    0    0
*/

SQL code

Create table tb([ID] int,[Name] nvarchar(1),[LB] int,[SL] int)
Insert tb
select 1,N'A',1,100 union all
select 2,N'A',2,80 union all
select 3,N'B',1,50 union all
select 4,N'B',2,30 union all
select 5,N'C',1,20
Go

;with cte as
(
    select a.Name,b.LB
    from (select distinct Name from tb) a cross join (select distinct LB from tb)b
)

select row_number() over (order by getdate()) as id,
    a.Name,1 as LB1,sum(case when a.LB=1 then isnull(b.SL,0) else 0 end) SL1,
    2 as LB2,sum(case when a.LB=2 then isnull(b.SL,0) else 0 end) SL2
from cte a left join tb b on a.Name = b.Name and a.LB = b.LB
group by a.Name

drop table tb

/*******************

id                   Name LB1         SL1         LB2         SL2
-------------------- ---- ----------- ----------- ----------- -----------
1                    A    1           100         2           80
2                    B    1           50          2           30
3                    C    1           20          2           0

(3 行受影响)

十分感谢大家的帮助。^_^

十分感谢大家的帮助。

SQL code

if object_id('tb','U') is not null
   drop table tb
go
create table tb
(
 id int identity(1,1),
 name varchar(10),
 lb int,
 sl int
)
go
insert into tb (name,lb,sl)
select 'A',1,100 union all
select 'A',2,80 union all
select 'B',1,50 union all
select 'B',2,30 union all
select 'C',1,20
go
select * from tb a where not exists(select 1 from tb where name=a.name and sl>a.sl)
go
/*
id          name       lb          sl
----------- ---------- ----------- -----------
1           A          1           100
3           B          1           50
5           C          1           20

(3 行受影响)

*/