SQL SERVER动态列名

栏目: 数据库 · 发布时间: 5年前

内容简介:在ms sql server实现动态呈现列的方法很多。下面Insus.NET解决也算是另外一种参考。如:

在ms sql server实现动态呈现列的方法很多。下面Insus.NET解决也算是另外一种参考。

如:

SQL SERVER动态列名

准备实现功能的数据:

SQL SERVER动态列名


CREATE TABLE [dbo].[Timing] ([When] NVARCHAR(10) NOT NULL PRIMARY KEY)
INSERT INTO [dbo].[Timing]  VALUES 
    (N'周五.晚上'),
    (N'周六.中午'),
    (N'周六.晚上'),
    (N'周日.中午'),
    (N'周日.晚上')
GO
SELECT [When] FROM [dbo].[Timing]
GO

Source Code

另一份数据:

SQL SERVER动态列名


CREATE TABLE [dbo].[Schedule] (
    [ID] INT IDENTITY(1,1) PRIMARY KEY,
    [Name] NVARCHAR(40),
    [When] NVARCHAR(10) FOREIGN KEY REFERENCES [dbo].[Timing]([When])
)
GO

INSERT INTO [dbo].[Schedule] ([NAME],[When]) VALUES 
(N'EMP-00201',N'周六.晚上'),(N'EMP-00201',N'周日.中午'),
(N'EMP-00202',N'周六.中午'),(N'EMP-00202',N'周六.晚上'),(N'EMP-00202',N'周日.中午'),
(N'EMP-00207',N'周五.晚上'),(N'EMP-00207',N'周六.中午'),(N'EMP-00207',N'周日.中午'),
(N'EMP-00209',N'周五.晚上'),(N'EMP-00209',N'周六.中午'),(N'EMP-00209',N'周六.晚上')
GO

SELECT [NAME],[When] FROM [dbo].[Schedule]
GO

Source Code

一切准备完毕,开始实现,创建一张临时表,将用来存储实现的数据。

SQL SERVER动态列名


IF OBJECT_ID('tempdb..#Temp_Result_Rpt') IS NOT NULL DROP TABLE #Temp_Result_Rpt  
CREATE TABLE #Temp_Result_Rpt
(   
    [Name] NVARCHAR(40) 
)        

Source Code

下面是处理动态列,把[dbo].[Timing]的数据转换为列,把它们处理为[xxx],[yyy],[zzz]...逗号串连在一起。

SQL SERVER动态列名


DECLARE @Comma_Delimited_Column_Names NVARCHAR(MAX)
EXECUTE [dbo].[usp_TableColumnValueToCommaDelimitedString] '[Timing]','[When]',@Comma_Delimited_Column_Names OUTPUT

SELECT @Comma_Delimited_Column_Names

Source Code

上面有一个自定义函数[dbo].[usp_TableColumnValueToCommaDelimitedString],它的实现方法,可以参考这里《 数据表列值转换为逗号分隔字符串https://www.cnblogs.com/insus/p/10848578.html

定义一个变量,

DECLARE @TABLE_NAME SYSNAME = N'#Temp_Result_Rpt'

给变量赋的值就是上面的创建的临时表名。

这个变量,将在下面的代码中使用得到。

接下来,我们需要把上面得到的动态列名,修改至临时表中去:

SQL SERVER动态列名


DECLARE @Source NVARCHAR(MAX) = @Comma_Delimited_Column_Names + N','
WHILE CHARINDEX(',', @Source) > 0
BEGIN
    DECLARE @DATA_TYPE SYSNAME = N'NVARCHAR(10)'
    DECLARE @COLUMN_NAME SYSNAME = SUBSTRING(@Source, 0, CHARINDEX(',', @Source))
    SET @Source = LTRIM(RTRIM(SUBSTRING(@Source, CHARINDEX(',', @Source) + 1, LEN(@Source)))) 
    EXECUTE('ALTER TABLE '+ @TABLE_NAME +' ADD '+ @COLUMN_NAME +' '+ @DATA_TYPE +' DEFAULT(N'''')')
END

EXECUTE('SELECT [Name],'+ @Comma_Delimited_Column_Names +' FROM '+ @TABLE_NAME +'')

Source Code

得到空表格,最后的动作,是需要把原始数据合并至这张临时表中。

有记录的,进行更新,没有记录的,插入新记录:

SQL SERVER动态列名


DECLARE @r INT = 1,@rs INT = 0
SELECT @rs = MAX([ID]) FROM [dbo].[Schedule]

WHILE @r <= @rs
BEGIN
    DECLARE @Name nvarchar(40)
    SELECT @COLUMN_NAME = [When],@Name = [Name] FROM [dbo].[Schedule] WHERE [ID] = @r
    
    EXECUTE('
        IF EXISTS(SELECT TOP 1 1 FROM '+ @TABLE_NAME +' WHERE [NAME] = N'''+ @NAME +''')
            UPDATE '+ @TABLE_NAME +' SET ['+ @COLUMN_NAME +']  = N''✔'' WHERE [NAME] = N'''+ @NAME +'''
        ELSE
            INSERT INTO '+ @TABLE_NAME +' ([NAME],['+ @COLUMN_NAME +']) VALUES(N'''+ @NAME +''',N''✔'')
        ')
    SET @r = @r + 1
END


EXECUTE('SELECT [Name],'+ @Comma_Delimited_Column_Names +' FROM '+ @TABLE_NAME +'')

Source Code

完成!

其中使用了很多动态SQL。


以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

赢在设计

赢在设计

[美] 洛芙迪 (Lance Loveday)、[美] 尼豪斯 (Sandra Niehaus) / 刘淼、枊靖、王卓昊 / 人民邮电出版社 / 2010-8 / 55.00

企业总是面临在网站设计和改进方面进行投资的抉择。怎样才能让有限的资金发挥出最大的效益呢?网站设计不应只是把网站做得赏心悦目,它更应该是提高经济收益和获得竞争优势的战略利器。是时候让网站发挥其潜能,以业务指标为导向来做设计决策,为提升网站收益而设计了。 作者凭借多年为众多网站做咨询工作的经验,为我们揭示了赢在设计的奥秘。它针对目前网站设计中存在的典型问题,先从宏观上探讨解决问题的战略手段,围绕......一起来看看 《赢在设计》 这本书的介绍吧!

HTML 编码/解码
HTML 编码/解码

HTML 编码/解码

Base64 编码/解码
Base64 编码/解码

Base64 编码/解码

HSV CMYK 转换工具
HSV CMYK 转换工具

HSV CMYK互换工具