ID Num
A1 30
A2 40
A3 30
A1 30
A1 30
A2 50这个是表一。怎么一个sql语句实现出表二的效果ID 30 40 50
A1 3 0 0
A2 0 1 1
A3 1 0 0
A1 30
A2 40
A3 30
A1 30
A1 30
A2 50这个是表一。怎么一个sql语句实现出表二的效果ID 30 40 50
A1 3 0 0
A2 0 1 1
A3 1 0 0
insert into @tb select 'A1',30
insert into @tb select 'A2',40
insert into @tb select 'A3',30
insert into @tb select 'A1',30
insert into @tb select 'A1',30
insert into @tb select 'A2',50select id,
max(case when num=30 then con else 0 end) as [30],
max(case when num=40 then con else 0 end) as [40],
max(case when num=50 then con else 0 end) as [50]
from (
select id,num,count(*) as con from @tb group by id,num
)t group by idid 30 40 50
A1 3 0 0
A2 0 1 1
A3 1 0 0
drop table tb
create table tb (id varchar(10),num varchar(10))
insert into tb values('A1','30')
insert into tb values('A2','40')
insert into tb values('A3','30')
insert into tb values('A1','30')
insert into tb values('A1','30')
insert into tb values('A2','50')select
t.ID as ID,
'30'=isnull((select count(*) from tb where id=t.id and num='30' group by id),0),
'40'=isnull((select count(*) from tb where id=t.id and num='40' group by id),0),
'50'=isnull((select count(*) from tb where id=t.id and num='50' group by id),0)
from tb t
group by t.id
declare @table table (id nvarchar(10),num int)
insert into @table select 'a1',30
union all select 'a2',40
union all select 'a3',30
union all select 'a1',30
union all select 'a1',30
union all select 'a2',50
select id ,[30]=sum(case when num=30 then 1 else 0 end),
[40]=sum(case when num=40 then 1 else 0 end),
[50]=sum(case when num=50 then 1 else 0 end)
from @table group by id
id 30 40 50
---------- ----------- ----------- -----------
a1 3 0 0
a2 0 1 1
a3 1 0 0(3 行受影响)
declare @table table (id nvarchar(10),num int)
insert into @table select 'a1',30
union all select 'a2',40
union all select 'a3',30
union all select 'a1',30
union all select 'a1',30
union all select 'a2',50
select id ,[30]=sum(case when num=30 then 1 else 0 end),
[40]=sum(case when num=40 then 1 else 0 end),
[50]=sum(case when num=50 then 1 else 0 end)
from @table group by id
id 30 40 50
---------- ----------- ----------- -----------
a1 3 0 0
a2 0 1 1
a3 1 0 0(3 行受影响)
from tb group by [id]
insert into tb select 'A1',30
insert into tb select 'A2',40
insert into tb select 'A3',30
insert into tb select 'A1',30
insert into tb select 'A1',30
insert into tb select 'A2',50
declare @sql varchar(8000)
set @sql = 'select id '
select @sql = @sql + ' , sum(case num when ' + convert(nvarchar, num) + ' then 1 else 0 end) [' + convert(nvarchar, num) + ']'
from (select distinct num from tb) as a
set @sql = @sql + ' from tb group by id'
exec(@sql)
count( case when Num='40' then 1 else 0 end ) as '40',
count( case when Num='50' then 1 else 0 end ) as '50'
from table
group by ID
max(case when num=30 then con else 0 end) as [30],
max(case when num=40 then con else 0 end) as [40],
max(case when num=50 then con else 0 end) as [50]
.........
from (
select id,num,count(*) as con from @tb group by id,num
)t group by id
来写吗?
能不能给个通用的转换方法?