动态数据透视SQL列数据到页眉
问题描述:
我想转置下面的表格,以便第一列(tabLabel)成为页眉。我需要动态执行此操作,因为行数未知。我已经看到了关于动态枢纽的帖子,但我不完全明白这将如何完成。动态数据透视SQL列数据到页眉
tabLabel documentId recipientId Date value
Street Address 1 1 NULL 123 mockingbird lane
City 1 1 NULL city
Patient Phone 1 1 NULL 999-999-9999
Responsible Phone 1 1 NULL 999-999-9999
Gross Income 1 1 NULL 999
Monthly Mortgage/Rent 1 1 NULL 100
Monthly Auto 1 1 NULL 200
最终版本:
Street Address City Patient Phone Responsible Phone Gross Income Monthly Mortage/Rent Monthly Auto documentId recipientId Date
123 mockingbird lane city 999-999-9999 999-999-9999 999 100 200 1 1 NULL
选择查询的原始表:
SELECT [tabLabel]
,[documentId]
,[recipientId]
,[Date]
,[value]
FROM [zDocusign_Document_Tab_Fields]
答
动态SQL
-- Build colums
DECLARE @cols NVARCHAR(MAX)
SELECT @cols = STUFF((
SELECT DISTINCT ',' + QUOTENAME([tabLabel])
FROM zDocusign_Document_Tab_Fields
FOR XML PATH('')
), 1, 1, '')
-- Selecting as FOR XML PATH will give you a string value with all of the fields combined
-- separated by comma. Stuff simply removes the first comma.
-- Quotename wraps the [tabLabel] value in brackets to allow for spaces in column name
-- You end up with
-- [City],[Gross Income],[Monthly Auto],[Monthly Mortgage/Rent],[Patient Phone],[Responsible Phone],[Street Address]
-- Build sql
DECLARE @sql NVARCHAR(MAX)
SET @sql = N'
SELECT ' + @cols +'
FROM zDocusign_Document_Tab_Fields
PIVOT (
MAX([value])
FOR [tabLabel] IN (' + @cols + ')
) p
'
-- Execute Sql
EXEC(@sql)
+0
非常感谢!立即工作! –
你能做到这一点在你的代码?这会更容易阅读。 – user2023861
@ user2023861你的意思是插入我的创建表或插入语句? –
我的意思是,如果您正在将这些数据读入某个应用程序,则可以将数据转到那里。我知道在C#中这样做会比我在Google上搜索“sql server dynamic pivot”时看到的要容易得多。 – user2023861