循环、行列转换、跨表更新的综合案列

一、背景

1.1 数据库

SQL SERVER 数据库

 

1.2 场景说明

当需要对每批次，月，日或者其他可以按照数字排序的结果说明；

结果是数字，但是每个数字代表的含义不同

 

1.3 建表

CREATE TABLE [cnblogs].[pivot_t] (
  [姓名] nvarchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [批次] int  NULL,
  [结果] int  NULL
)  

 

CREATE TABLE [cnblogs].[pivot_id] (
  [id] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [说明] nvarchar(255) COLLATE Chinese_PRC_CI_AS  NULL
)  

 

1.4 插入数据

INSERT INTO [cnblogs].[pivot_t]([姓名], [批次], [结果]) VALUES (N'张三', 1, 4);
INSERT INTO [cnblogs].[pivot_t]([姓名], [批次], [结果]) VALUES (N'张三', 2, 3);
INSERT INTO [cnblogs].[pivot_t]([姓名], [批次], [结果]) VALUES (N'张三', 3, 1);
INSERT INTO [cnblogs].[pivot_t]([姓名], [批次], [结果]) VALUES (N'张三', 4, 5);
INSERT INTO [cnblogs].[pivot_t]([姓名], [批次], [结果]) VALUES (N'张三', 5, 2);
INSERT INTO [cnblogs].[pivot_t]([姓名], [批次], [结果]) VALUES (N'张三', 6, 1);
INSERT INTO [cnblogs].[pivot_t]([姓名], [批次], [结果]) VALUES (N'李四', 1, 1);
INSERT INTO [cnblogs].[pivot_t]([姓名], [批次], [结果]) VALUES (N'李四', 2, 3);
INSERT INTO [cnblogs].[pivot_t]([姓名], [批次], [结果]) VALUES (N'李四', 3, 5);
INSERT INTO [cnblogs].[pivot_t]([姓名], [批次], [结果]) VALUES (N'李四', 4, 2);
INSERT INTO [cnblogs].[pivot_t]([姓名], [批次], [结果]) VALUES (N'李四', 5, 4);
INSERT INTO [cnblogs].[pivot_t]([姓名], [批次], [结果]) VALUES (N'李四', 6, 2);

 

INSERT INTO [cnblogs].[pivot_id]([id], [说明]) VALUES ('1', N'微信支付');
INSERT INTO [cnblogs].[pivot_id]([id], [说明]) VALUES ('2', N'支付宝支付');
INSERT INTO [cnblogs].[pivot_id]([id], [说明]) VALUES ('3', N'财付通支付');
INSERT INTO [cnblogs].[pivot_id]([id], [说明]) VALUES ('4', N'点券支付');
INSERT INTO [cnblogs].[pivot_id]([id], [说明]) VALUES ('5', N'支付失败');

 

1.5 目的

 

 

 

 

 

二、建立存储过程

USE [test]
GO
/****** Object:  StoredProcedure [cnblogs].[行列转换]    Script Date: 2022/3/6 16:09:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


create  PROCEDURE [cnblogs].[行列转换]
AS 
BEGIN


drop table if exists #pivot;                            -- 更改批次值，批次值会变成列名；
select 姓名,CONCAT('t',批次) as 批次,cast(结果 as int) as 结果 into #pivot from test.cnblogs.pivot_t   -- 聚合的部分必须是数字
;

drop table if exists #pivot_table;                      -- 完成行列转换
SELECT * INTO #pivot_table
FROM
(
SELECT * FROM #pivot AS P
PIVOT(sum(结果) FOR 批次 in ([t1],[t2],[t3],[t4],[t5],[t6])) AS T
) AS t1



/*
对结果含义进行更新，由数字转换为文字
因为进行行列转换之后多出来6列，所以进行循环
*/
declare @start_num int ;
declare @cl_name varchar(20);
declare @alter_sql varchar(MAX);
declare @update_sql varchar(MAX);
set @start_num = 1 ;
WHILE (@start_num<= 6)
    BEGIN
    set @cl_name = CONCAT('t',cast(@start_num as varchar));                                    -- 取列名
    set @alter_sql = 'ALTER TABLE #pivot_table ALTER COLUMN ['+ @cl_name + '] varchar(20)';    -- 将列调整为字符串格式
    set @update_sql = 'UPDATE A SET A.['+ @cl_name + '] = B.[说明] FROM #pivot_table as A, test.cnblogs.pivot_id as B where A.['+ @cl_name + ']=B.[id] '; -- 跨表更新
    exec (@alter_sql);                                                                        -- 执行更改格式命令
    exec (@update_sql);                                                                       -- 执行更新命令 
    set @start_num = @start_num+1                                                             -- 准备对下一列执行处理
    END



;
drop table if exists test.dbo.pivot_result                             
SELECT * 
INTO test.dbo.pivot_result
FROM #pivot_table
;


END

 

 

三、查看结果