返回
软件
分类

今天Insus.NET再详细演示一番,如果我们对那些动想产生的列

日期: 2020-04-22 04:33 浏览次数 : 72

写SQL语句时,我们尽量使用具体列名来替换星号(*),虽然效能上微不足道,但写了,实际上已经是另外的境界了。
比如很多时候,我们都会这样写:

在Insus.NET的blog里,可以找到相关的行列转换文章。可以点击下面链接  慢慢看来。
不过,今天Insus.NET再详细演示一番,望让网友们更加理解与应用。
还是先来看看实时演示吧:
必赢备用网址 1

SELECT * FROM [tableName]

想实现行与列转换,得先一弄清楚哪些原数据被转为列名必赢备用网址 ,,哪些原数列名被转换为行数据。就依上图来说。原列名[Fruit Name]没有被转换,还是作为新表的列名,而原[Kind Name]和[ID]列名被转换为新[Fruit Name]列的行数据了,而原[Fuit Name]所在行的数据将转换为列名。

实际上,我们正确的写法是,想SELECT哪些具体列,应该具体列出:

好,我们弄清楚,在存储过程中,创建一个雏形的临时表,并插入两笔数据。
必赢备用网址 2

SELECT [column1],[column2],[column3],[column4] FROM [tableName]

然后,我们宣告一个表变量,你也可以宣告一个临时表也行。目的是把需要处理的数据先存入这个表变量中。这样有处理时,不必每次去读取原始表数据。
必赢备用网址 3

如果我们对那些动想产生的列,怎样办呢?Insus.NET就依此例 《如何实现数据行转换列显示》 来说。它是动态产生列的。所产生的列是一个不确定的因素。如:
必赢备用网址 4
第#47行:

接下来,我们开始处理表变量的数据:
必赢备用网址 5

SELECT * FROM [dbo].[#DummyTable]

上图的代码示例中,
#26是找出表变量中[Fruit_nbr]最大的值。
#28至#45行是一个循环块,只要变量@r小于等于变量@rs,它都会跑,每跑完一遍,变量@r都会在#44行中加1。
#32行代码,是判断表变量中是否有跳号的记录。虽然[Fruit_nbr]的一个自动增长的字段,如果在记录维护时,有删除记录的话,就会出现跳号。因此需要判断。
#34行代码,是把当前处理记录的两个值,即ID和Kind Name找出来。
#36至#38 行代码,是修心临时表,添加一个字段。
#40和#41是对临时表作更新操作。
#47行代码是SELECT临时表所有记录。

好象只能用星号来应用了。因为在此情形,根本无法指定具体的列名。办法是有的,可以看看Insus.NET是怎样解决这个问题。
必赢备用网址 6Insus.NET对存储过程,添加了#23和#35行代码。#23行是宣告一个变量将用来存储新表的列名,由于[Fruit Name]是位于第一列,也是固定的,所以先作为变量的默认值。
在#35行代码中,每循环一行记录,join超[FruitName]值。[Fruit Name],[苹果],[梨],[海棠果],......
而在#48行,由于是列名是一个动态变量,只能改写为动态SQL语句了。

完整的存储过程:

必赢备用网址 7必赢备用网址 8

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Insus.NET
-- Create date: 2013-09-04
-- Description:    convert row to column.
-- =============================================
CREATE PROCEDURE [dbo].[usp_Fruit_Row2Col]
AS
BEGIN
    IF OBJECT_ID('[dbo].[#DummyTable]') IS NOT NULL
        DROP TABLE [dbo].[#DummyTable]    
    CREATE TABLE  [dbo].[#DummyTable] 
     (   
        [Fruit Name] NVARCHAR(20)
     )       
     INSERT INTO [dbo].[#DummyTable] ([Fruit Name]) VALUES ('Kind Name'),('ID')    


     DECLARE @ft AS TABLE ([Fruit_nbr] INT,[KindName] NVARCHAR(30),[FruitName] NVARCHAR(30))
     INSERT INTO @ft SELECT [Fruit_nbr],[KindName],[FruitName] FROM [dbo].[udf_Fruit]()


     DECLARE @r INT = 1, @rs INT = 0
     SELECT @rs = MAX(ISNULL([Fruit_nbr],0)) FROM @ft     

     WHILE @r <= @rs 
     BEGIN
        DECLARE @kn NVARCHAR(30), @fn NVARCHAR(20)

        IF EXISTS (SELECT TOP 1 1 FROM @ft WHERE [Fruit_nbr] = @r)
        BEGIN        
            SELECT @kn = [KindName], @fn = [FruitName] FROM @ft WHERE [Fruit_nbr] = @r

            EXECUTE('IF NOT EXISTS(SELECT [name] FROM [tempdb].[dbo].[syscolumns] WHERE [id] = OBJECT_ID(''[tempdb].[dbo].[#DummyTable]'') 
                     AND [tempdb].[dbo].[syscolumns].[name] = '''+ @fn +''')
                     ALTER TABLE [tempdb].[dbo].[#DummyTable] ADD [' + @fn + '] NVARCHAR(100)')

            EXECUTE('UPDATE [dbo].[#DummyTable] SET ['+ @fn +'] = N'''+ @kn +''' WHERE [Fruit Name] = ''Kind Name''')
            EXECUTE('UPDATE [dbo].[#DummyTable] SET ['+ @fn +'] = '''+ @r +''' WHERE [Fruit Name] = ''ID''')    

        END
        SET @r += 1
     END

    SELECT * FROM [dbo].[#DummyTable]
END

[dbo].[usp_Fruit_Row2Col]

 在html markup:
必赢备用网址 9

今天Insus.NET再详细演示一番,如果我们对那些动想产生的列。cs:
必赢备用网址 10