SQL查询代码简化优化

Question

这里还有“布疋松布“，”平幅洗水“，”胚定1“，”胚定2“，等16个项，有没有什么动态的办法，或者其他更简便的方法？

select 
 (select SUM(isnull(b.Ds_SendQty,c.Ds_SendQty)) as qty
      from (
           SELECT *,ROW_NUMBER() OVER(PARTITION BY Ms_TankNo ORDER BY Ms_FlowID DESC) Num FROM Dye_Scheme
           ) t
           left join Bas_FlowInfo on Ms_FlowNo = Bf_FlowNo
           left join Dye_Schedule b on b.Ds_TankNo = Ms_TankNo
           left join Dye_Scheduled c on c.Ds_TankNo = Ms_TankNo
            WHERE t.Num=1 
            and Ms_FactualDate is null and Ms_StartDate is null 
            and Ms_InceptDate < GETDATE()
            and Bf_FlowName = '染部收单'
            group by Bf_FlowName
  ) as '染部收单',
 (select SUM(isnull(b.Ds_SendQty,c.Ds_SendQty)) as qty
      from (
           SELECT *,ROW_NUMBER() OVER(PARTITION BY Ms_TankNo ORDER BY Ms_FlowID DESC) Num FROM Dye_Scheme
           ) t
           left join Bas_FlowInfo on Ms_FlowNo = Bf_FlowNo
           left join Dye_Schedule b on b.Ds_TankNo = Ms_TankNo
           left join Dye_Scheduled c on c.Ds_TankNo = Ms_TankNo
            WHERE t.Num=1 
            and Ms_FactualDate is null and Ms_StartDate is null 
            and Ms_InceptDate < GETDATE()
            and Bf_FlowName = '成品定型1'
            group by Bf_FlowName
  ) as '成品定型1'

本意是想把这部分查询结果显示成一行，但是Bf_FlowName列统计可能会没有数据，就会缺少！

SELECT Bf_FlowName,SUM(isnull(b.Ds_SendQty,c.Ds_SendQty)) as qty
FROM(
SELECT *,ROW_NUMBER() OVER(PARTITION BY Ms_TankNo ORDER BY Ms_FlowID DESC) Num FROM Dye_Scheme
) t
left join Bas_FlowInfo on Ms_FlowNo = Bf_FlowNo
left join Dye_Schedule b on b.Ds_TankNo = Ms_TankNo
left join Dye_Scheduled c on c.Ds_TankNo = Ms_TankNo
WHERE t.Num=1 
and Ms_FactualDate is null and Ms_StartDate is null 
and Ms_InceptDate < GETDATE()
and Ms_FlowNo in(2,3,4,6,7,8,9,11,12,13,14,15,26,28,32,35)     --这里是相关的Bf_FlowName代码号
group by Ms_FlowNo,Bf_FlowName
order by Ms_FlowNo

想做成这种样子

请各位大师指导交流！谢谢！

Answer 1

试一试动态sql吧，我没有你原表的数据，只能在ssms软件中给你改写

DECLARE @DynamicSQL NVARCHAR(MAX) 
DECLARE @Columns NVARCHAR(MAX)

SELECT @Columns = COALESCE(@Columns + ',', '') + QUOTENAME(Bf_FlowName) 
FROM Bas_FlowInfo 
WHERE Ms_FlowNo IN (2,3,4,6,7,8,9,11,12,13,14,15,26,28,32,35)

SET @DynamicSQL = ' SELECT ' + @Columns + ', SUM(isnull(b.Ds_SendQty,c.Ds_SendQty)) as qty 
FROM( 
    SELECT *,ROW_NUMBER() OVER(PARTITION BY Ms_TankNo ORDER BY Ms_FlowID DESC) Num 
    FROM Dye_Scheme ) t 
left join Bas_FlowInfo 
on Ms_FlowNo = Bf_FlowNo left join Dye_Schedule b on b.Ds_TankNo = Ms_TankNo 
left join Dye_Scheduled c on c.Ds_TankNo = Ms_TankNo 
WHERE t.Num=1 and Ms_FactualDate is null and Ms_StartDate is null and Ms_InceptDate < GETDATE() 
and Ms_FlowNo in(2,3,4,6,7,8,9,11,12,13,14,15,26,28,32,35) 
 group by Ms_FlowNo,Bf_FlowName order by Ms_FlowNo'

EXEC sp_executesql @DynamicSQL