Sql-Server
如何使用 SQL Server 中 XML 數據中的序號位置（不是名稱）解析列名？
我在數據庫中有數百個具有相同結構的表：
一些Id、Pos、 不同數量的其他欄位
因此，例如，一個表可能如下所示：
PersonId, Pos, Hobby, Degree 12345, 1, Basketball, Bachelor of Science 12345, 2, Baseball, Master of Science 12345, 3, Boxing, NULL 12345, 4, Tennis, NULL 22222, 1, Golf, Bachelor of Science 22222, 2, NULL, Master of Science 22222, 3, NULL, Doctorate我想匯總每列 3-N 的值。所以這會變成：
12345, "Basketball, Baseball, Boxing, Tennis", "Bachelor of Science, Master of Science" 22222, "Golf", "Bachelor of Science, Master of Science, Doctorate"另一個表可能如下所示：
ClientId, Pos, Location, Language, CommunicationType 33333, 1, North Carolina, English, Phone 33333, 2, New York, Spanish, Email 33333, 3, NULL, Portuguese, NULL 44444, 1, California, English, Phone 44444, 2, NULL, NULL, Email變成這樣：
33333, "North Carolina, New York", "English, Spanish, Portugeue", "Phone, Email" 44444, "California", "English", "Phone, Email"我想做的是創建一個 TVF，我可以在其中指定表名並讓函式返回其欄位。理想情況下，就像我剛剛在上面展示的那樣捲起來。
Solomon Rutzky 提供了一個解決方案（SQL Server：將表名作為參數傳遞給表值函式），他展示瞭如何使用 XML 和 CASE 語句在 TVF 中接受表名。
這是一個改編：
DECLARE @TableName sysname = 'objects' /* DECLARE @TableName sysname = 'columns' DECLARE @TableName sysname = 'indexes' */ SELECT tab.BaseData.value(N'/row[1]/@name', N'VARCHAR(128)') AS [name], tab.BaseData.value(N'/row[1]/@object_id', N'BIGINT') AS [object_id], * FROM ( SELECT CASE @TableName WHEN N'objects' THEN (SELECT * FROM master.sys.tables FOR XML RAW, TYPE) WHEN N'indexes' THEN (SELECT * FROM master.sys.indexes FOR XML RAW, TYPE) WHEN N'columns' THEN (SELECT * FROM master.sys.columns FOR XML RAW, TYPE) END AS [BaseData] ) tab;如果我要創建一個怪物 CASE 語句並考慮所有可能的傳入表名，有沒有辦法按序號位置（而不是像我上面那樣的名稱）引用列？更好的是，也可以匯總並劃定它們的值（這是我的最終目標）？
先感謝您！
此外，這裡是創建我的兩個範例表的 DDL：
CREATE TABLE [dbo].[Person]( [PersonId] [int] NULL, [Pos] [int] NULL, [Hobby] [varchar](100) NULL, [Degree] [varchar](50) NULL ) GO INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (12345, 1, N'Basketball', N'Bachelor of Science') GO INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (12345, 2, N'Baseball', N'Master of Science') GO INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (12345, 3, N'Boxing', NULL) GO INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (12345, 4, N'Tennis', NULL) GO INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (22222, 1, N'Golf', N'Bachelor of Science') GO INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (22222, 2, NULL, N'Master of Science') GO INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (22222, 3, NULL, N'Doctorate') GO CREATE TABLE [dbo].[Client]( [ClientId] [int] NULL, [Pos] [int] NULL, [Location] [varchar](100) NULL, [Language] [varchar](50) NULL, [CommunicationType] [varchar](50) NULL ) GO INSERT [dbo].[Client] ([ClientId], [Pos], [Location], [Language], [CommunicationType]) VALUES (33333, 1, N'North Carolina', N'English', N'Phone') GO INSERT [dbo].[Client] ([ClientId], [Pos], [Location], [Language], [CommunicationType]) VALUES (33333, 2, N'New York', N'Spanish', N'Email') GO INSERT [dbo].[Client] ([ClientId], [Pos], [Location], [Language], [CommunicationType]) VALUES (33333, 3, NULL, N'Portuguese', NULL) GO INSERT [dbo].[Client] ([ClientId], [Pos], [Location], [Language], [CommunicationType]) VALUES (44444, 1, N'California', N'English', N'Phone') GO INSERT [dbo].[Client] ([ClientId], [Pos], [Location], [Language], [CommunicationType]) VALUES (44444, 2, NULL, NULL, N'Email') GO SELECT * FROM Person; SELECT * FROM Client;
有沒有辦法按序號位置引用列
是的，但我不確定這將如何幫助你做你想做的事。您將序數位置放在謂詞中，就像您已經為
row[1].改為
'/row[1]/@name'獲取第三列將如下所示'/row[1]/@*[3]'。您應該知道空值不會創建任何屬性，因此第三個屬性中的數據並不總是來自第三列。要解決此問題，您可以為列值生成元素而不是屬性，並用於
XSINIL獲取列中空值的空元素，例如：SELECT * FROM master.sys.indexes FOR XML RAW, ELEMENTS XSINIL, TYPE. 然後您需要從 XML 中選擇第三個元素而不是第三個屬性'/row[1]/*[3]'。您已經在*“創建一個怪物 CASE 語句並考慮所有可能的傳入表名”*的路徑上，所以為什麼不創建一個怪物查詢來代替您想要的東西，而不需要 XML 的東西。
select T.PersonId as Id, '"' + string_agg(T.Hobby, ',') within group (order by T.Pos) + '", ' + '"' + string_agg(T.Degree, ',') within group (order by T.Pos) + '"' as Value from dbo.Person as T where @TableName = N'Person' group by T.PersonId union all select T.ClientId, '"' + string_agg(T.Location, ',') within group (order by T.Pos) + '", ' + '"' + string_agg(T.Language, ',') within group (order by T.Pos) + '", ' + '"' + string_agg(T.CommunicationType, ',') within group (order by T.Pos) + '"' from dbo.Client as T where @TableName = N'Client' group by T.ClientId;如果您需要經常甚至自動更新函式，則可以對元表使用動態 SQL 來生成上述查詢。
由於您使用的是 SQL Server 2016，因此
string_agg()您不需要使用它for xml path來進行連接。查詢變大了，但原理相同，仍然可以使用動態 SQL 創建。select T.PersonId as Id, '"' + stuff(( select ', '+T2.Hobby from dbo.Person as T2 where T.PersonId = T2.PersonId order by T2.Pos for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, '') + '", ' + '"' + stuff(( select ', '+T2.Degree from dbo.Person as T2 where T.PersonId = T2.PersonId order by T2.Pos for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, '') + '"' as Value from dbo.Person as T where @TableName = N'Person' group by T.PersonId union all select T.ClientId, '"' + stuff(( select ', '+T2.Location from dbo.Client as T2 where T.ClientId = T2.ClientId order by T2.Pos for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, '') + '", ' + '"' + stuff(( select ', '+T2.Language from dbo.Client as T2 where T.ClientId = T2.ClientId order by T2.Pos for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, '') + '", ' + '"' + stuff(( select ', '+T2.CommunicationType from dbo.Client as T2 where T.ClientId = T2.ClientId order by T2.Pos for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, '') + '"' from dbo.Client as T where @TableName = N'Client' group by T.ClientId;