有网友在网上问：
数据如下：
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
接下来，参考这篇《数据表列值转换为逗号分隔字符串》https://www.cnblogs.com/insus/p/10848578.html
DECLARE @cols NVARCHAR(MAX) EXECUTE[dbo].[usp_DateColumnToCommaDelimitedString] #T,createdate,@cols OUTPUT
再接下来，再参考另外一篇《动态为表添加存储时间字段》https://www.cnblogs.com/insus/p/10943614.html
EXECUTE [dbo].[usp_DyanmicallyAddStorageDatetimeField] ‘tempdb‘,‘dbo‘,‘#TempRpt‘,@cols EXECUTE(‘SELECT [caseno],[username],‘+ @cols +‘ FROM #TempRpt‘)
一切准备好，我们可以开始处理原始数据：
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‘)
到此，应该已经可以结束了。达到想要的结果。
扩展，Insus.NET添加多笔不同username ,caseno数据行，
但同一username ,caseno记录中，有重复的记录
程序还是使用上面的程序，也均能得到预期的结果。
原文：https://www.cnblogs.com/insus/p/10944163.html
