sqlsqlserver的特殊函数COALESCE和PIVOT的用法分析

一、COALESCE是一个返回参数中第一个非NULL值的函数，

列如：COALESCE（a,b,c,d,e）;可以按照顺序取abcde，中的第一个非空数据，abcde可以是表达式

用case when 加ISNULL也可以实现，但是写法复杂了     

     --根据部件id获取产品型号的板材指定类型，1-获取客户的板材指定方式是真指定就是真指定，      --2-获取不可替代本厂编号是否是不可替代，是就是真指定      --3-获取MI上的客户是否指定板材，是就是加指定，否则就是不指定 select  temp1.ProductPartId, max(CASE WHEN temp1.BoardType='真指定' THEN '真指定' else null end) as pnBoardType ,max(CASE WHEN temp1.ProductNumber1 is not null THEN '真指定' else null end) as pnBoardType2 ,max(CASE WHEN temp3.bckhyq ='是' THEN '假指定' else '不指定' end )as pnBoardType3

,COALESCE(CASE WHEN temp1.BoardType='真指定' THEN '真指定' else null end, CASE WHEN temp1.ProductNumber1 is not null THEN '真指定' else null end, CASE WHEN temp3.bckhyq ='是' THEN '假指定' else '不指定' end) AS pnBoardTypetemp    INTO    #temp44 from  #temp11 temp1 LEFT JOIN #temp33 temp3 on temp3.ProductPartId=temp1.ProductPartId

GROUP BY temp1.ProductPartId,COALESCE(CASE WHEN temp1.BoardType='真指定' THEN '真指定' else null end, CASE WHEN temp1.ProductNumber1 is not null THEN '真指定' else null end, CASE WHEN temp3.bckhyq ='是' THEN '假指定' else '不指定' end)；

二、PIVOT函数，用于行转列

SELECT [非旋转列], [旋转后的列1], [旋转后的列2], ... FROM (SELECT [原始数据列] FROM 表) AS 数据源 PIVOT ( [聚合函数](被聚合列) FOR [旋转列] IN ([列值1], [列值2], ...) ) AS 别名

非旋转列：结果中保持为行的列（如产品类别、年份等）。

旋转列：需要从行值转换为列名的列（如月份、状态等）。

被聚合列：需要统计的数值列（如销售额、数量等）。

聚合函数：如 SUM、AVG、COUNT 等。

SELECT top 10 t.ProductPartId         ,t.bcjclx         ,t.BCXH         ,t.bckhyq from  ( SELECT p.ProductPartId, eo.ParamCode, p.ParamValue          FROM SL_pe_Schema.MakeProductPart(NOLOCK)part               JOIN SL_pe_Schema.MakeProcedureParam(NOLOCK)p ON p.ProductPartId=part.ProductPartId               JOIN SL_pe_Schema.BaseEngineersParam(NOLOCK)eo ON eo.EngineersParamId=p.EngineersParamId          WHERE eo.ParamCode IN ('bckhyq','bcjclx','dlcc','PPQTY','bcgys','yhhd', 'sfht', 'bh', 'wulu', 'tgvalue', 'jcth', 'lvhao', 'lvhou', 'drxs', 'JZCH','BCXH','tblx')             ) tmp                      PIVOT(min(tmp.ParamValue)                 FOR tmp.ParamCode  IN(bckhyq,bcjclx,dlcc,PPQTY,bcgys,yhhd, sfht, bh, wulu, tgvalue, jcth, JZCH,BCXH,tblx,lvhou,lvhao,drxs)                 ) t 

将原始数据行专列

当然平常的数据库可能没有这个函数，还是有通用方法的，那就是CASE WHEN，只要参数名是固定已知的，就可以用CASE WHEN加聚合函数进行 group by  行转列