有網友在網上問:
資料如下:
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
接下來,參考這篇《資料表列值轉換為逗号分隔字元串》javascript:void(0)
改寫一下:
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
運作示例,并得到結果:
DECLARE @cols NVARCHAR(MAX)
EXECUTE[dbo].[usp_DateColumnToCommaDelimitedString] #T,createdate,@cols OUTPUT
再接下來,再參考另外一篇《動态為表添加存儲時間字段》javascript:void(0)
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記錄中,有重複的記錄