27,579
社区成员
发帖
与我相关
我的任务
分享
<table border="1">
<tr><td>Id</td><td>type</td><td>days</td><tr/>
<tr><td>1</td><td>TYPEA1</td><td>3</td><tr/>
<tr><td>1</td><td>TYPEA2</td><td>4</td><tr/>
<tr><td>1</td><td>TYPEA3</td><td>5</td><tr/>
<tr><td>1</td><td>TYPEB1</td><td>4</td><tr/>
<tr><td>1</td><td>TYPEB2</td><td>3</td><tr/>
<tr><td>1</td><td>TYPEB3</td><td>4</td><tr/>
<tr><td>1</td><td>TYPEC1</td><td>5</td><tr/>
<tr><td>1</td><td>TYPEC2</td><td>4</td><tr/>
<tr><td>1</td><td>TYPEC3</td><td>3</td><tr/>
<tr><td>2</td><td>TYPEA1</td><td>3</td><tr/>
<tr><td>2</td><td>TYPEA2</td><td>4</td><tr/>
<tr><td>2</td><td>TYPEA3</td><td>5</td><tr/>
<tr><td>2</td><td>TYPEB1</td><td>4</td><tr/>
<tr><td>2</td><td>TYPEB2</td><td>3</td><tr/>
<tr><td>2</td><td>TYPEB3</td><td>4</td><tr/>
<tr><td>2</td><td>TYPEC1</td><td>5</td><tr/>
<tr><td>2</td><td>TYPEC2</td><td>4</td><tr/>
<tr><td>2</td><td>TYPEC3</td><td>3</td><tr/>
</table>
<table border="1">
<tr><td>Id</td><td>TYPEA</td><td>TYPEB</td><td>TYPEC</td><tr/>
<tr><td>1</td><td>12</td><td>11</td><td>12</td><tr/>
<tr><td>2</td><td>12</td><td>11</td><td>12</td><tr/>
</table>
declare @t table (Id int,type varchar(10),days int)
insert into @t
select 1,'avalue1',3 union all
select 1,'aavalue2',4 union all
select 1,'aaavalue3',5 union all
select 1,'bvalue1',4 union all
select 1,'bbvalue2',3 union all
select 1,'bbbvalue3',4 union all
select 1,'cvalue1',5 union all
select 1,'ccvalue2',4 union all
select 1,'cccvalue3',3 union all
select 2,'avalue1',3 union all
select 2,'aavalue2',4 union all
select 2,'aaavalue3',5 union all
select 2,'bvalue1',4 union all
select 2,'bbvalue2',3 union all
select 2,'bbbvalue3',4 union all
select 2,'cvalue1',5 union all
select 2,'ccvalue2',4 union all
select 2,'cccvalue3',3
select
ID ,
sum(case type
when 'avalue1' then days
when 'aavalue2' then days
when 'aaavalue3' then days
else 0 end) as TYPEA,
sum(case type
when 'bvalue1' then days
when 'bbvalue2' then days
when 'bbbvalue3' then days
else 0 end) as TYPEB,
sum(case type
when 'cvalue1' then days
when 'ccvalue2' then days
when 'cccvalue3' then days
else 0 end) as TYPEC
from @t group by ID
/*
ID TYPEA TYPEB TYPEC
----------- ----------- ----------- -----------
1 12 11 12
2 12 11 12
(2 row(s) affected)
*/
declare @t table (Id int,type varchar(10),days int)
insert into @t
select 1,'avalue1',3 union all
select 1,'aavalue2',4 union all
select 1,'aaavalue3',5 union all
select 1,'bvalue1',4 union all
select 1,'bbvalue2',3 union all
select 1,'bbbvalue3',4 union all
select 1,'cvalue1',5 union all
select 1,'ccvalue2',4 union all
select 1,'cccvalue3',3 union all
select 2,'avalue1',3 union all
select 2,'aavalue2',4 union all
select 2,'aaavalue3',5 union all
select 2,'bvalue1',4 union all
select 2,'bbvalue2',3 union all
select 2,'bbbvalue3',4 union all
select 2,'cvalue1',5 union all
select 2,'ccvalue2',4 union all
select 2,'cccvalue3',3
;with maco as
(
select
ID,left(type,1) as type,sum(days) as days
from @t group by ID,left(type,1)
)
select
ID ,
sum(case when type='a' then days else 0 end) as TYPEA,
sum(case when type='b' then days else 0 end) as TYPEB,
sum(case when type='c' then days else 0 end) as TYPEC
from maco group by ID
/*
ID TYPEA TYPEB TYPEC
----------- ----------- ----------- -----------
1 12 11 12
2 12 11 12
(2 row(s) affected)
*/
declare @t table (Id int,type varchar(6),days int)
insert into @t
select 1,'avalue1(TYPEA1)',3 union all
select 1,'aavalue2(TYPEA2)',4 union all
select 1,'aaavalue3(TYPEA3)',5 union all
select 1,'bvalue1(TYPEB1)',4 union all
select 1,'bbvalue2(TYPEB2)',3 union all
select 1,'bbbvalue3(TYPEB3)',4 union all
select 1,'cvalue1(TYPEC1)',5 union all
select 1,'ccvalue2(TYPEC2)',4 union all
select 1,'cccvalue3(TYPEC3)',3 union all
select 2,'avalue1(TYPEA1)',3 union all
select 2,'aavalue2(TYPEA2)',4 union all
select 2,'aaavalue3(TYPEA3)',5 union all
select 2,'bvalue1(TYPEB1)',4 union all
select 2,'bbvalue2(TYPEB2)',3 union all
select 2,'bbbvalue3(TYPEB3)',4 union all
select 2,'cvalue1(TYPEC1)',5 union all
select 2,'ccvalue2(TYPEC2)',4 union all
select 2,'cccvalue3(TYPEC3)',3
谢谢
declare @t table (Id int,type varchar(6),days int)
insert into @t
select 1,'TYPEA1',3 union all
select 1,'TYPEA2',4 union all
select 1,'TYPEA3',5 union all
select 1,'TYPEB1',4 union all
select 1,'TYPEB2',3 union all
select 1,'TYPEB3',4 union all
select 1,'TYPEC1',5 union all
select 1,'TYPEC2',4 union all
select 1,'TYPEC3',3 union all
select 2,'TYPEA1',3 union all
select 2,'TYPEA2',4 union all
select 2,'TYPEA3',5 union all
select 2,'TYPEB1',4 union all
select 2,'TYPEB2',3 union all
select 2,'TYPEB3',4 union all
select 2,'TYPEC1',5 union all
select 2,'TYPEC2',4 union all
select 2,'TYPEC3',3
;with maco as
(
select
ID,left(type,5) as type,sum(days) as days
from @t group by ID,left(type,5)
)
select
ID ,
sum(case when type='TYPEA' then days else 0 end) as TYPEA,
sum(case when type='TYPEB' then days else 0 end) as TYPEB,
sum(case when type='TYPEC' then days else 0 end) as TYPEC
from maco group by ID
/*
ID TYPEA TYPEB TYPEC
----------- ----------- ----------- -----------
1 12 11 12
2 12 11 12
(2 row(s) affected)
*/