求一SQL语句

Question

表SpB
xm    SP     SL
-----------------
A     SP1    1
A     SP2    2
A     SP3    3
B     SP1    2
B     SP2    3
B     SP3    4
C     SP1    5
C     SP2    6
B     SP5    10
A     SP4    12
D     SP6    20想要得到以下结果:
xm    SP1    SP2    SP3    SP4   SP5   SP6
---------------------------------------------
A      1      2      3      12    0     0
B      2      3      4       0    10    0
C      5      6      0       0    0     0
D      0      0      0       0    0     20

Answer 1

我可能少写了一些记录当
A   SP1  3  有多条记录时，要求和的

Answer 2

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: #SpB
IF OBJECT_ID('tempdb.dbo.#SpB') IS NOT NULL DROP TABLE #SpB
CREATE TABLE #SpB (xm VARCHAR(1),SP VARCHAR(3),SL INT)
INSERT INTO #SpB
SELECT 'A','SP1',1 UNION ALL
SELECT 'A','SP2',2 UNION ALL
SELECT 'A','SP3',3 UNION ALL
SELECT 'B','SP1',2 UNION ALL
SELECT 'B','SP2',3 UNION ALL
SELECT 'B','SP3',4 UNION ALL
SELECT 'C','SP1',5 UNION ALL
SELECT 'C','SP2',6 UNION ALL
SELECT 'B','SP5',10 UNION ALL
SELECT 'A','SP4',12 UNION ALL
SELECT 'D','SP6',20--SQL查询如下:DECLARE @column VARCHAR(500);
SET @column='';SELECT
    @column=@column+',['+SP+']'
FROM #SpB
GROUP BY SP;SET @column=STUFF(@column,1,1,'');EXEC('SELECT * FROM #SpB PIVOT(SUM(SL) FOR SP IN('+@column+')) AS pvt')DROP TABLE #SpB;/*
xm   SP1         SP2         SP3         SP4         SP5         SP6
---- ----------- ----------- ----------- ----------- ----------- -----------
A    1           2           3           12          NULL        NULL
B    2           3           4           NULL        10          NULL
C    5           6           NULL        NULL        NULL        NULL
D    NULL        NULL        NULL        NULL        NULL        20(4 行受影响)
*/

Answer 3

if object_id('[SpB]') is not null drop table [SpB] 
go 
create table [SpB]([xm] varchar(10),[SP] varchar(10),[SL] int)
insert [SpB] select 'A','SP1',1
union all select 'A','SP2',2
union all select 'A','SP3',3
union all select 'B','SP1',2
union all select 'B','SP2',3
union all select 'B','SP3',4
union all select 'C','SP1',5
union all select 'C','SP2',6
union all select 'B','SP5',10
union all select 'A','SP4',12
union all select 'D','SP6',20
go
--select * from SpB
declare @s varchar(8000)
set @s='select xm '
select @s=@s+',max(case sp when '''+sp+''' then sl else 0 end) ['+sp+']'
from SpB
group by sp
order by sp
select @s=@s+' from Spb group by xm'
exec(@s)
/*
xm         SP1         SP2         SP3         SP4         SP5         SP6
---------- ----------- ----------- ----------- ----------- ----------- -----------
A          1           2           3           12          0           0
B          2           3           4           0           10          0
C          5           6           0           0           0           0
D          0           0           0           0           0           20(4 行受影响)
*/

Answer 4

create table #tb(xm varchar(1),sp varchar(10),sl numeric(5))
insert into #tb
select 'A','SP1','1'
union all select 'A','SP2','2'
union all select 'A','SP3','3'
union all select 'B','SP1','2'
union all select 'B','SP2','3'
union all select 'B','SP3','4'
union all select 'C','SP1','5'
union all select 'C','SP2','6'
union all select 'B','SP5','10'
union all select 'A','SP4','12'
union all select 'D','SP6','20'select * from #tbdeclare @sql varchar(8000)
set @sql=''select @sql=@sql + ',['+rtrim(sp)+']=max(case sp when '''+rtrim(sp)+''' then rtrim(sl) end)'
from #tb group by spexec('select xm'+@sql+'from  #tb group by xm' )xm     SP1     SP2     SP3     SP4     SP5     SP6
----------------------------------------------------------------
A 1 2 3 12 NULL NULL
B 2 3 4 NULL 10 NULL
C 5 6 NULL NULL NULL NULL
D NULL NULL NULL NULL NULL 20

Answer 5

create table tb(xm varchar(10),SP varchar(10),SL int)
insert into tb select 'A','SP1',1 
insert into tb select 'A','SP2',2 
insert into tb select 'A','SP3',3 
insert into tb select 'B','SP1',2 
insert into tb select 'B','SP2',3 
insert into tb select 'B','SP3',4 
insert into tb select 'C','SP1',5 
insert into tb select 'C','SP2',6 
insert into tb select 'B','SP5',10 
insert into tb select 'A','SP4',12 
insert into tb select 'D','SP6',20 
select distinct xm,
sp1=isnull((select sl from tb where xm=a.xm and sp='SP1'),0),
sp2=isnull((select sl from tb where xm=a.xm and sp='SP2'),0),
sp3=isnull((select sl from tb where xm=a.xm and sp='SP3'),0),
sp4=isnull((select sl from tb where xm=a.xm and sp='SP4'),0),
sp5=isnull((select sl from tb where xm=a.xm and sp='SP5'),0),
sp6=isnull((select sl from tb where xm=a.xm and sp='SP6'),0)
from tb a
go
drop table tb
/*
xm         sp1         sp2         sp3         sp4         sp5         sp6
---------- ----------- ----------- ----------- ----------- ----------- -----------
A          1           2           3           12          0           0
B          2           3           4           0           10          0
C          5           6           0           0           0           0
D          0           0           0           0           0           20
*/

Answer 6

create table #SPB2 
(
  xm nvarchar(20),
  SP nvarchar(20),
  SL int
)
insert into #SPB2 select 'A','SP1',1
union all select 'A','SP2',2
union all select 'A','SP3',3
union all select 'B','SP1',1
union all select 'B','SP2',2
union all select 'B','SP3',3
union all select 'C','SP1',5
union all select 'C','SP2',6
union all select 'A','SP4',12
union all select 'D','SP6',20select xm,
max(case when SP='SP1' then SL else 0 end) 'SP1',
max(case when SP='SP2' then SL else 0 end) 'SP2',
max(case when SP='SP3' then SL else 0 end) 'SP3',
max(case when SP='SP4' then SL else 0 end) 'SP4',
max(case when SP='SP5' then SL else 0 end) 'SP5',
max(case when SP='SP6' then SL else 0 end) 'SP6'
from #SPB2 group by xm

Answer 7

create table #SPB2 
(
  xm nvarchar(20),
  SP nvarchar(20),
  SL int
)
insert into #SPB2 select 'A','SP1',1
union all select 'A','SP2',2
union all select 'A','SP3',3
union all select 'B','SP1',1
union all select 'B','SP2',2
union all select 'B','SP3',3
union all select 'C','SP1',5
union all select 'C','SP2',6
union all select 'A','SP4',12
union all select 'D','SP6',20select xm,
max(case when SP='SP1' then SL else 0 end) 'SP1',
max(case when SP='SP2' then SL else 0 end) 'SP2',
max(case when SP='SP3' then SL else 0 end) 'SP3',
max(case when SP='SP4' then SL else 0 end) 'SP4',
max(case when SP='SP5' then SL else 0 end) 'SP5',
max(case when SP='SP6' then SL else 0 end) 'SP6'
from #SPB2 group by xmxm                   SP1         SP2         SP3         SP4         SP5         SP6
-------------------- ----------- ----------- ----------- ----------- ----------- -----------
A                    1           2           3           12          0           0
B                    1           2           3           0           0           0
C                    5           6           0           0           0           0
D                    0           0           0           0           0           20(4 行受影响)

Answer 8

if object_id('[SpB]') is not null drop table [SpB] 
go 
create table [SpB]([xm] varchar(10),[SP] varchar(10),[SL] int)
insert [SpB] select 'A','SP1',1
union all select 'A','SP2',2
union all select 'A','SP3',3
union all select 'B','SP1',2
union all select 'B','SP2',3
union all select 'B','SP3',4
union all select 'C','SP1',5
union all select 'C','SP2',6
union all select 'B','SP5',10
union all select 'A','SP4',12
union all select 'D','SP6',20
go
--select * from SpB
declare @s varchar(8000)
set @s='select xm '
select @s=@s+',sum(case sp when '''+sp+''' then sl else 0 end) ['+sp+']'
from SpB
group by sp
order by sp
select @s=@s+' from Spb group by xm'
exec(@s)
/*
xm         SP1         SP2         SP3         SP4         SP5         SP6
---------- ----------- ----------- ----------- ----------- ----------- -----------
A          1           2           3           12          0           0
B          2           3           4           0           10          0
C          5           6           0           0           0           0
D          0           0           0           0           0           20(4 行受影响)
*/

Answer 9

数据输入错误
汗死:
--静态的
create table #SPB3
(
  xm nvarchar(20),
  SP nvarchar(20),
  SL int
)
insert into #SPB3 select 'A','SP1',1
union all select 'A','SP2',2
union all select 'A','SP3',3
union all select 'B','SP1',1
union all select 'B','SP2',2
union all select 'B','SP3',3
union all select 'C','SP1',5
union all select 'C','SP2',6
union all select 'B','SP5',10
union all select 'A','SP4',12
union all select 'D','SP6',20select xm,
max(case when SP='SP1' then SL else 0 end) 'SP1',
max(case when SP='SP2' then SL else 0 end) 'SP2',
max(case when SP='SP3' then SL else 0 end) 'SP3',
max(case when SP='SP4' then SL else 0 end) 'SP4',
max(case when SP='SP5' then SL else 0 end) 'SP5',
max(case when SP='SP6' then SL else 0 end) 'SP6'
from #SPB3 group by xm
xm                   SP1         SP2         SP3         SP4         SP5         SP6
-------------------- ----------- ----------- ----------- ----------- ----------- -----------
A                    1           2           3           12          0           0
B                    1           2           3           0           10          0
C                    5           6           0           0           0           0
D                    0           0           0           0           0           20(4 行受影响)

Answer 10

--动态的
create table #SPB3
(
  xm nvarchar(20),
  SP nvarchar(20),
  SL int
)
insert into #SPB3 select 'A','SP1',1
union all select 'A','SP2',2
union all select 'A','SP3',3
union all select 'B','SP1',1
union all select 'B','SP2',2
union all select 'B','SP3',3
union all select 'C','SP1',5
union all select 'C','SP2',6
union all select 'B','SP5',10
union all select 'A','SP4',12
union all select 'D','SP6',20
declare @sql varchar(2000)
set @sql='select xm'
select @sql=@sql+',max(case when SP= '''+SP+'''then SL else 0 end) ['+SP+']'
from (select distinct(SP) from #SPB3)S
--print(@sql)
select @sql=@sql+'from #SPB3 group by xm'
exec(@sql)xm                   SP1         SP2         SP3         SP4         SP5         SP6
-------------------- ----------- ----------- ----------- ----------- ----------- -----------
A                    1           2           3           12          0           0
B                    1           2           3           0           10          0
C                    5           6           0           0           0           0
D                    0           0           0           0           0           20(4 行受影响)

Answer 11

数据还是错啦
create table #SPB4
(
  xm nvarchar(20),
  SP nvarchar(20),
  SL int
)
insert into #SPB4 select 'A','SP1',1
union all select 'A','SP2',2
union all select 'A','SP3',3
union all select 'B','SP1',2
union all select 'B','SP2',3
union all select 'B','SP3',4
union all select 'C','SP1',5
union all select 'C','SP2',6
union all select 'B','SP5',10
union all select 'A','SP4',12
union all select 'D','SP6',20select xm,
max(case when SP='SP1' then SL else 0 end) 'SP1',
max(case when SP='SP2' then SL else 0 end) 'SP2',
max(case when SP='SP3' then SL else 0 end) 'SP3',
max(case when SP='SP4' then SL else 0 end) 'SP4',
max(case when SP='SP5' then SL else 0 end) 'SP5',
max(case when SP='SP6' then SL else 0 end) 'SP6'
from #SPB4 group by xmdeclare @sql varchar(2000)
set @sql='select xm'
select @sql=@sql+',max(case when SP= '''+SP+'''then SL else 0 end) ['+SP+']'
from (select distinct(SP) from #SPB4)S
--print(@sql)
select @sql=@sql+'from #SPB4 group by xm'
exec(@sql)
xm                   SP1         SP2         SP3         SP4         SP5         SP6
-------------------- ----------- ----------- ----------- ----------- ----------- -----------
A                    1           2           3           12          0           0
B                    2           3           4           0           10          0
C                    5           6           0           0           0           0
D                    0           0           0           0           0           20(4 行受影响)