SQL SERVER动态列名__列名无效如何解决sql

相关文章推荐
绅士的咖啡 · UIElement.IsMouseDirec ...· 1 年前 ·
愤怒的伤痕 · pandas处理重复值 - 掘金· 1 年前 ·
活泼的冲锋衣 · oracle 判断是否是时间_百度知道· 1 年前 ·
俊秀的海龟 · grep常用命令整理 - 知乎· 1 年前 ·
CREATE TABLE [dbo].[Timing] ([When] NVARCHAR(10) NOT NULL PRIMARY KEY)
INSERT INTO [dbo].[Timing]  VALUES 
    (N'周五.晚上'),
    (N'周六.中午'),
    (N'周六.晚上'),
    (N'周日.中午'),
    (N'周日.晚上')
SELECT [When] FROM [dbo].[Timing]
Source Code
另一份数据：
CREATE TABLE [dbo].[Schedule] (
    [ID] INT IDENTITY(1,1) PRIMARY KEY,
    [Name] NVARCHAR(40),
    [When] NVARCHAR(10) FOREIGN KEY REFERENCES [dbo].[Timing]([When])
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'周六.晚上')
SELECT [NAME],[When] FROM [dbo].[Schedule]
Source Code
一切准备完毕，开始实现，创建一张临时表，将用来存储实现的数据。
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]...逗号串连在一起。
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]，它的实现方法，可以参考这里《数据表列值转换为逗号分隔字符串》javascript:void(0)：
定义一个变量，
DECLARE @TABLE_NAME SYSNAME = N'#Temp_Result_Rpt'
给变量赋的值就是上面的创建的临时表名。
这个变量，将在下面的代码中使用得到。
接下来，我们需要把上面得到的动态列名，修改至临时表中去：
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'''')')
EXECUTE('SELECT [Name],'+ @Comma_Delimited_Column_Names +' FROM '+ @TABLE_NAME +'')
Source Code
得到空表格，最后的动作，是需要把原始数据合并至这张临时表中。
有记录的，进行更新，没有记录的，插入新记录：
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 +'''
            INSERT INTO '+ @TABLE_NAME +' ([NAME],['+ @COLUMN_NAME +']) VALUES(N'''+ @NAME +''',N''✔'')
    SET @r = @r + 1
EXECUTE('SELECT [Name],'+ @Comma_Delimited_Column_Names +' FROM '+ @TABLE_NAME +'')
Source Code
其中使用了很多动态SQL。