如何实现数据行转换列显示

在Insus.NET的blog里,可以找到相关的行列转换文章。可以点击下面链接 http://www.cnblogs.com/insus/archive/2011/03/05/1971446.html 慢慢看来。
不过,今天Insus.NET再详细演示一番,望让网友们更加理解与应用。
还是先来看看实时演示吧:



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

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



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


接下来,我们开始处理表变量的数据:



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


完整的存储过程:

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:



cs:



posted @ 2013-09-04 15:07  Insus.NET  阅读(1539)  评论(8编辑  收藏  举报