行转列问题(案例)，

行转列问题(案例)，

有网友在网上问：

数据如下：

number  createdate                         username         caseno
1       2018-05-29 18:57:54.150    戴婷                 201708220028
2      2018-07-04 14:28:40.167    戴婷                 201708220028
3     2019-01-02 11:34:53.533    戴婷         201708220028
4     2019-01-16 18:17:01.313    戴婷                201708220028

期望得到的结果：

username   caseno                     createdate1                             createdate2                           createdate3                         createdate4
戴婷               201708220028          2018-05-29 18:57:54.150      2018-07-04 14:28:40.167       2019-01-02 11:34:53.533     2019-01-16 18:17:01.313

下面是Insus.NET实现方法：

创建一个临时表，将用来存储最终结果：

 

IF OBJECT_ID('tempdb..#TempRpt') IS NOT NULL DROP TABLE #TempRpt CREATE TABLE #TempRpt ( [caseno] bigint, [username] nvarchar(20) ) SELECT [caseno],[username] FROM #TempRpt Source Code

 

接下来，参考这篇《数据表列值转换为逗号分隔字符串》https://www.cnblogs.com/insus/p/10848578.html

改写一下：

 

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Insus.NET -- Create date: 2019-05-29 -- Update date: 2019-05-29 -- Description: 时间表列值转换为逗号分隔字符串 -- ============================================= CREATE PROCEDURE [dbo].[usp_DateColumnToCommaDelimitedString] ( @TableName SYSNAME, @DateColumn SYSNAME, @Comma_Delimited_String NVARCHAR(MAX) OUTPUT ) AS BEGIN DECLARE @query NVARCHAR(MAX) = N'SET @Comma_Delimited_String = STUFF((SELECT DISTINCT '','' + QUOTENAME(CAST('+ @DateColumn +' AS DATE)) FROM '+ @TableName +' FOR XML PATH(''''), TYPE ).value(''.'', ''NVARCHAR(MAX)'') ,1,1,'''')' EXECUTE sp_executeSql @query, N'@Comma_Delimited_String AS NVARCHAR(MAX) OUTPUT',@Comma_Delimited_String OUTPUT END GO Source Code

 

运行示例，并得到结果：

 

DECLARE @cols NVARCHAR(MAX) EXECUTE[dbo].[usp_DateColumnToCommaDelimitedString] #T,createdate,@cols OUTPUT Source Code

 

再接下来，再参考另外一篇《动态为表添加存储时间字段》https://www.cnblogs.com/insus/p/10943614.html

 

EXECUTE [dbo].[usp_DyanmicallyAddStorageDatetimeField] 'tempdb','dbo','#TempRpt',@cols EXECUTE('SELECT [caseno],[username],'+ @cols +' FROM #TempRpt') Source Code

 

一切准备好，我们可以开始处理原始数据：

 

DECLARE @r INT = 1,@rs INT = 0 SELECT @rs = MAX([number]) FROM #T WHILE @r <= @rs BEGIN DECLARE @COLUMN_NAME SYSNAME,@createdate datetime,@username nvarchar(20),@caseno bigint SELECT @COLUMN_NAME = CONVERT(VARCHAR(30),[createdate], 23),@createdate = [createdate],@username = [username],@caseno = [caseno] FROM #T WHERE [number] = @r IF EXISTS(SELECT TOP 1 1 FROM #TempRpt WHERE [caseno] = @caseno) EXECUTE('UPDATE #TempRpt SET ['+ @COLUMN_NAME +'] = '''+ @createdate +''' WHERE [caseno] = '''+ @caseno +'''' ) ELSE EXECUTE('INSERT INTO #TempRpt ([caseno],[username],['+ @COLUMN_NAME +']) VALUES('''+ @caseno +''',N'''+ @username +''','''+ @createdate +''')') SET @r = @r + 1 END EXECUTE('SELECT [caseno],[username],'+ @cols +' FROM #TempRpt') Source Code

 

到此，应该已经可以结束了。达到想要的结果。

 

扩展，Insus.NET添加多笔不同username ,caseno数据行，
但同一username ,caseno记录中，有重复的记录

 

程序还是使用上面的程序，也均能得到预期的结果。