原表:
<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>
求SQL,谢谢。
------最佳解决方案--------------------
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,