SQL SERVER 对应列值问题

[Pact_Alice]

--根据CVGUID PromotionCode 分组 想得到一列，
--如果desc0 的值以买金额为开头 那么值就是 desc0=销售金额对应的salesvolume 的值
--如果desc0 的值以买数量为开头 那么值就是 desc0=销售数量 对应的salesvolume 的值
;WITH cet AS (
select '6F774D1C-C463-42CF-9E14-E9646B371C2A' CVGUID, 'P201506080005'PromotionCode, 'R201506180001'ElementRelationCode, '买金额送金额'DESC0, 1.0000 SalesVolume union all
select '6F774D1C-C463-42CF-9E14-E9646B371C2A' CVGUID, 'P201506080005'PromotionCode, 'R201506230003' ,'买数量送数量', 1.0000 union all
select '6F774D1C-C463-42CF-9E14-E9646B371C2A' CVGUID, 'P201506080005'PromotionCode, 'R201507310001', '买数量送数量', 5.0000 union all
select '6F774D1C-C463-42CF-9E14-E9646B371C2A' CVGUID, 'P201506080005'PromotionCode, 'T201506080013', '销售数量', 5000.0000 union all
select '6F774D1C-C463-42CF-9E14-E9646B371C2A' CVGUID, 'P201506080005'PromotionCode, 'T201506180001', '销售金额', 20.0000 union all
select '6F774D1C-C463-42CF-9E14-E9646B371C2A' CVGUID, 'P201506080005'PromotionCode, 'T201506190001', '发票金额', 12.0000
)
SELECT * FROM cet
如图

[qq_17482963]

;WITH cet AS (
select '6F774D1C-C463-42CF-9E14-E9646B371C2A' CVGUID, 'P201506080005'PromotionCode, 'R201506180001'ElementRelationCode, '买金额送金额'DESC0, 1.0000 SalesVolume union all
select '6F774D1C-C463-42CF-9E14-E9646B371C2A' CVGUID, 'P201506080005'PromotionCode, 'R201506230003' ,'买数量送数量', 1.0000 union all
select '6F774D1C-C463-42CF-9E14-E9646B371C2A' CVGUID, 'P201506080005'PromotionCode, 'R201507310001', '买数量送数量', 5.0000 union all
select '6F774D1C-C463-42CF-9E14-E9646B371C2A' CVGUID, 'P201506080005'PromotionCode, 'T201506080013', '销售数量', 5000.0000 union all
select '6F774D1C-C463-42CF-9E14-E9646B371C2A' CVGUID, 'P201506080005'PromotionCode, 'T201506180001', '销售金额', 20.0000 union all
select '6F774D1C-C463-42CF-9E14-E9646B371C2A' CVGUID, 'P201506080005'PromotionCode, 'T201506190001', '发票金额', 12.0000
),
cet1 as
(
select *,case when LEFT(DESC0,3)='买金额' then '销售金额' when LEFT(DESC0,3)='买数量' then '销售数量' else null end desc1 from cet
)

SELECT a.CVGUID,a.PromotionCode,a.ElementRelationCode,a.DESC0,a.SalesVolume,b.SalesVolume as desc1 FROM cet1 a left join cet b on a.CVGUID=b.CVGUID and a.desc1=b.DESC0
試試這個