--希望多出来一列按照CVGUID,PromotionCode分组，
--同一组的如果desc01 为 False 则 desc0对应的为 NO,
-- 如果desc01 为 True 则 desc0 对应的为 Yes
-- 如果desc0 为空的还是空
;WITH cet AS (
select 'BD2A1916-C2E6-46A5-9538-17F2BCA62B57' CVGUID, 'C0001' PromotionCode, NULL Desc0, NULL Desc01 UNION ALL
select 'BD2A1916-C2E6-46A5-9538-17F2BCA62B57', 'C0001', '买数量送百分比数量', NULL UNION ALL
select 'BD2A1916-C2E6-46A5-9538-17F2BCA62B57', 'C0001', '销售数量', 'False' UNION ALL
select 'BD2A1916-C2E6-46A5-9538-17F2BCA62B57', 'C0001', '合同金额', 'False' UNION ALL
select '1933DBD7-4763-4CF6-B2F1-B39990DADA54', 'P201506080005', NULL, NULL UNION ALL
select '1933DBD7-4763-4CF6-B2F1-B39990DADA54', 'P201506080005', NULL, NULL UNION ALL
select '1933DBD7-4763-4CF6-B2F1-B39990DADA54', 'P201506080005', NULL, NULL UNION ALL
select '1933DBD7-4763-4CF6-B2F1-B39990DADA54', 'P201506080005', NULL, NULL UNION ALL
select '1933DBD7-4763-4CF6-B2F1-B39990DADA54', 'P201506080005', '买金额送金额', NULL UNION ALL
select '1933DBD7-4763-4CF6-B2F1-B39990DADA54', 'P201506080005', '买数量送数量', NULL UNION ALL
select '1933DBD7-4763-4CF6-B2F1-B39990DADA54', 'P201506080005', '买数量送数量', NULL UNION ALL
select '1933DBD7-4763-4CF6-B2F1-B39990DADA54', 'P201506080005', '销售数量', 'True' UNION ALL
select '1933DBD7-4763-4CF6-B2F1-B39990DADA54', 'P201506080005', '销售金额', 'True' UNION ALL
select '1933DBD7-4763-4CF6-B2F1-B39990DADA54', 'P201506080005', '发票金额', 'True'
)
SELECT * FROM cet
------解决思路----------------------
同组内的Desc01 如果相同，可以这么写。
;WITH cte AS (
select 'BD2A1916-C2E6-46A5-9538-17F2BCA62B57' CVGUID, 'C0001' PromotionCode, NULL Desc0, NULL Desc01 UNION ALL
select 'BD2A1916-C2E6-46A5-9538-17F2BCA62B57', 'C0001', '买数量送百分比数量', NULL UNION ALL
select 'BD2A1916-C2E6-46A5-9538-17F2BCA62B57', 'C0001', '销售数量', 'False' UNION ALL
select 'BD2A1916-C2E6-46A5-9538-17F2BCA62B57', 'C0001', '合同金额', 'False' UNION ALL
select '1933DBD7-4763-4CF6-B2F1-B39990DADA54', 'P201506080005', NULL, NULL UNION ALL
select '1933DBD7-4763-4CF6-B2F1-B39990DADA54', 'P201506080005', NULL, NULL UNION ALL
select '1933DBD7-4763-4CF6-B2F1-B39990DADA54', 'P201506080005', NULL, NULL UNION ALL
select '1933DBD7-4763-4CF6-B2F1-B39990DADA54', 'P201506080005', NULL, NULL UNION ALL
select '1933DBD7-4763-4CF6-B2F1-B39990DADA54', 'P201506080005', '买金额送金额', NULL UNION ALL
select '1933DBD7-4763-4CF6-B2F1-B39990DADA54', 'P201506080005', '买数量送数量', NULL UNION ALL
select '1933DBD7-4763-4CF6-B2F1-B39990DADA54', 'P201506080005', '买数量送数量', NULL UNION ALL
select '1933DBD7-4763-4CF6-B2F1-B39990DADA54', 'P201506080005', '销售数量', 'True' UNION ALL
select '1933DBD7-4763-4CF6-B2F1-B39990DADA54', 'P201506080005', '销售金额', 'True' UNION ALL
select '1933DBD7-4763-4CF6-B2F1-B39990DADA54', 'P201506080005', '发票金额', 'True'
)
select T2.CVGUID, T2.PromotionCode, T2.Desc0, T2.Desc01,
isnull(case
when T2.Desc0 is not null and T2.Desc01 is null then
case
when T1.Desc01='False' then 'NO'
when T1.Desc01='true' then 'YES'
end end, '') as desc02
from cte as T2
left join
(
select CVGUID,PromotionCode, min(Desc01) as Desc01
from cte group by CVGUID, PromotionCode
) as T1
on T1.CVGUID = T2.CVGUID and T1.PromotionCode = T2.PromotionCode