求一SQL语句

[hzybc]

表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

[-晴天]

找找行转列的例程,太多了.

[ws_hgo]

我做的快吐啦

[hzybc]

我可能少写了一些记录

当
A SP1 3 有多条记录时，要求和的

[liangCK]

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

[htl258_Tony]

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

[Andy__Huang]

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 #tb



declare @sql varchar(8000)
set @sql=''

select @sql=@sql + ',['+rtrim(sp)+']=max(case sp when '''+rtrim(sp)+''' then rtrim(sl) end)'
from #tb group by sp

exec('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

[-晴天]

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
*/

[ws_hgo]

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',20

select 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

[ws_hgo]

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',20

select 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

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

[htl258_Tony]

[Quote=引用 3 楼 hzybc 的回复:]
我可能少写了一些记录

当
A SP1 3 有多条记录时，要求和的
[/Quote]

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

[hzybc]

谢谢大家的帮助，马上结贴

[ws_hgo]

数据输入错误
汗死:
--静态的

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

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

[ws_hgo]

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

[ws_hgo]

人齐结贴

[musecangying007]

mark

[ws_hgo]

数据还是错啦

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',20

select 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 xm

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

[用心呼吸]

强人不少啊，mark