记一次简单的存储过程和Pivot行转列

一次,简单,存储,过程,pivot,转列 · 浏览次数 : 14

小编点评

```sql CREATE PROC FactoryCalendar @MD001 NVARCHAR(50)ASBEGIN DECLARE @Columns AS NVARCHAR(MAX) DECLARE @SQL AS NVARCHAR(MAX) -- 使用动态SQL生成资源名称的列名 SELECT @Columns = STRING_AGG(QUOTENAME(资源名称), ', ') WITHIN GROUP (ORDER BY 资源名称) FROM ( SELECT DISTINCT MB.MB002 AS 资源名称 FROM BW_CMSMD CMD INNER JOIN BW_MPSMB MB ON CMD.MD003 = MB.MB003 INNER JOIN BW_MPSMD MD ON MB.MB001 = MD.MD001 WHERE CMD.MD001 = @MD001 ) AS ColName; print @Columns; -- 构建动态SQL查询 SET @SQL = ' SELECT * FROM ( SELECT MB.MB002 AS 资源名称, CMD.MD001 AS 部门编号, CMD.MD002 AS 部门名称, MD.MD002 AS 日期, MD.MD003 AS 当日产能 FROM BW_CMSMD CMD INNER JOIN BW_MPSMB MB ON CMD.MD003 = MB.MB003 INNER JOIN BW_MPSMD MD ON MB.MB001 = MD.MD001 WHERE CMD.MD001 = ''' + @MD001 + ''' ) AS SourceData PIVOT ( MAX(当日产能) FOR 资源名称 IN (' + @Columns + ') ) AS PivotedData;' -- 执行动态SQL查询 EXEC(@SQL); END ```

正文

首先我很讨厌写存储过程，其次我很讨厌

没办法，主要是需要进行行转列，项目经理说可以用Pivot。我不是很精通sql，但是我会百度呀~

pivot需要有确定的列名。那我这个项目里面没办法确定，最后问了gpt，使用动态sql(我以前也没用过)，不过效果是我想要的，于是乎，改成存储过程吧。

简单的存储过程不难，其实以前我也会写，只是很久很久……似乎我工作之后就没用过，差不多就忘了……

小小总结一下

创建存储过程

CREATE PROC(PROCEDURE ) FactoryCalendar
//create 创建  
//proc(procedure是全称，proc就行)存储过程  
//FactoryCalendar 你的存过过程名字

可以传参或者不传参

CREATE PROCEDURE FactoryCalendar
    @MD001 NVARCHAR(50)
AS
//定义的变量呢需要加@ 然后加个类型，代表参数类型
//AS关键字主要在于将参数和存储过程主体分开，as下面你就知道存储过程开始了

接下来就是你要执行的sql了。一般begin开始end结尾。不多废话了。写个案例就知道了

-- 创建存储过程，获取资源组工作日历
CREATE PROCEDURE FactoryCalendar
    @MD001 NVARCHAR(50)
AS
BEGIN
    DECLARE @Columns AS NVARCHAR(MAX)
    DECLARE @SQL AS NVARCHAR(MAX)

    -- 使用动态SQL生成资源名称的列名
    SELECT @Columns = STRING_AGG(QUOTENAME(资源名称), ', ') WITHIN GROUP (ORDER BY 资源名称)
    FROM (
        SELECT DISTINCT MB.MB002 AS 资源名称
        FROM BW_CMSMD CMD
        INNER JOIN BW_MPSMB MB ON CMD.MD003 = MB.MB003
        INNER JOIN BW_MPSMD MD ON MB.MB001 = MD.MD001
        WHERE CMD.MD001 = @MD001
    ) AS ColName;
    print @Columns;
    -- 构建动态SQL查询
    SET @SQL = '
    SELECT *
    FROM (
        SELECT 
            MB.MB002 AS 资源名称, 
            CMD.MD001 AS 部门编号, 
            CMD.MD002 AS 部门名称, 
            MD.MD002 AS 日期, 
            MD.MD003 AS 当日产能
        FROM BW_CMSMD CMD
        INNER JOIN BW_MPSMB MB ON CMD.MD003 = MB.MB003
        INNER JOIN BW_MPSMD MD ON MB.MB001 = MD.MD001
        WHERE CMD.MD001 = ''' + @MD001 + '''
    ) AS SourceData
    PIVOT (
        MAX(当日产能)
        FOR 资源名称 IN (' + @Columns + ')
    ) AS PivotedData;'

    -- 执行动态SQL查询
    EXEC(@SQL);
END

　　4.执行存储过程：有参数就这么写，没有直接 exec FactoryCalendar

exec FactoryCalendar   '6875'