select cast(sum(case when 票类型='单人' then 售出数量 else 0 end) as varchar)+'|' +cast(sum(case when 票类型='单人' then 未售数量 else 0 end) as varchar) as 单人, cast(sum(case when 票类型='双人' then 售出数量 else 0 end) as varchar)+'|' +cast(sum(case when 票类型='双人' then 未售数量 else 0 end) as varchar) as 双人, cast(sum(case when 票类型='学生' then 售出数量 else 0 end) as varchar)+'|' +cast(sum(case when 票类型='学生' then 未售数量 else 0 end) as varchar) as 学生 from 表A
create table aa (atype varchar(10),销售数量 int,未售出数量 int)insert into aa select '单人',10,20 union all select '双人',20,30 union all select '学生',20,30 union all select '单人',20,30 union all select '单人',20,30select cast(sum(case when atype= '单人 ' then 销售数量 else 0 end) as varchar)+ ' ¦ ' +cast(sum(case when atype= '单人 ' then 未售出数量 else 0 end) as varchar) as 单人, cast(sum(case when atype= '双人 ' then 销售数量 else 0 end) as varchar)+ ' ¦ ' +cast(sum(case when atype= '双人 ' then 未售出数量 else 0 end) as varchar) as 双人, cast(sum(case when atype= '学生 ' then 销售数量 else 0 end) as varchar)+ ' ¦ ' +cast(sum(case when atype= '学生 ' then 未售出数量 else 0 end) as varchar) as 学生 from aa 结果集: 50|80 20|80 20|80
+cast(sum(case when 票类型='单人' then 未售数量 else 0 end) as varchar) as 单人,
cast(sum(case when 票类型='双人' then 售出数量 else 0 end) as varchar)+'|'
+cast(sum(case when 票类型='双人' then 未售数量 else 0 end) as varchar) as 双人,
cast(sum(case when 票类型='学生' then 售出数量 else 0 end) as varchar)+'|'
+cast(sum(case when 票类型='学生' then 未售数量 else 0 end) as varchar) as 学生
from 表A
union all select '双人',20,30
union all select '学生',20,30
union all select '单人',20,30
union all select '单人',20,30select cast(sum(case when atype= '单人 ' then 销售数量 else 0 end) as varchar)+ ' ¦ '
+cast(sum(case when atype= '单人 ' then 未售出数量 else 0 end) as varchar) as 单人,
cast(sum(case when atype= '双人 ' then 销售数量 else 0 end) as varchar)+ ' ¦ '
+cast(sum(case when atype= '双人 ' then 未售出数量 else 0 end) as varchar) as 双人,
cast(sum(case when atype= '学生 ' then 销售数量 else 0 end) as varchar)+ ' ¦ '
+cast(sum(case when atype= '学生 ' then 未售出数量 else 0 end) as varchar) as 学生
from aa
结果集:
50|80 20|80 20|80