How can parse column names using ordinal position (not name) from XML data in SQL Server?

Question

I have several hundred tables in a database that have the same structure:

SomeId, Pos, Varying Number of Other Fields

So, for example, one table may look like this:

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

I want to roll up the values for every column 3-N. So this would become:

12345, "Basketball, Baseball, Boxing, Tennis",  "Bachelor of Science, Master of Science"
22222, "Golf", "Bachelor of Science, Master of Science, Doctorate"

Another table might look like this:

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

and become this:

33333, "North Carolina, New York", "English, Spanish, Portugeue", "Phone, Email"
44444, "California", "English", "Phone, Email"

What I would like to do is create a TVF where I can specify the table name and have the function return its fields. Ideally, rolled up like I just demonstrated above.

Solomon Rutzky provided a solution (SQL Server: Pass table name into table valued function as a parameter) where he showed how to use XML and CASE statements to accept table names in a TVF.

Here's an adaptation:

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;

If I were to create a monster CASE statement and account for all possible incoming table names, is there a way to reference the columns by ordinal position (rather than name like I'm doing above)? Even better, roll up and delimit their values too (which is my ultimate goal)?

Thank you in advance!

Also, here's the DDL to create my two sample tables:

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;

Unfortunately it can’t be a stored procedure...

Mike S
– Mike S

2021-04-21 01:08:38 +00:00
Commented Apr 21, 2021 at 1:08 — Mike S
– Mike S, Commented Apr 21, 2021 at 1:08

Paul White · Accepted Answer · 2021-04-25 00:30:46Z

is there a way to reference the columns by ordinal position

Yes there is but I'm not sure how that would help you doing what you want. You put the ordinal position in the predicate, as you already do for row[1].

Changing '/row[1]/@name' to instead get the third column would look like '/row[1]/@*[3]'. You should be aware of that null values does not create any attributes so your data in the third attribute will not always come from the third column.

To fix that you could generate elements instead of attributes for column values and use XSINIL to get empty elements for null values in columns, ex: SELECT * FROM master.sys.indexes FOR XML RAW, ELEMENTS XSINIL, TYPE. Then you need to select the third element from the XML instead of the third attribute '/row[1]/*[3]'.

You are already on a path to "create a monster CASE statement and account for all possible incoming table names" so why not create a monster query that does what you want instead, without the XML stuff.

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;

You could use dynamic SQL against meta tables to generate the above query if you are in a situation where you need to update the function often or even automatically.

Since you are on SQL Server 2016 you don't have string_agg() you need to use for xml path to do the concatenation. The query got bigger but it is the same principle and can still be created using dynamic 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;

Charlieface · Accepted Answer · 2021-04-24 23:19:06Z

You cannot use dynamic SQL here, as that will not work inside a TVF. You could use dynamic to generate the actual code below though.

Given that you are on SQL Server 2016, you do not have STRING_AGG available, so you are going to have to use FOR XML/STUFF method, which is pretty complex with multiple columns.

It's not necessary or performant to keep querying the data again for every column, you can use a combination of APPLY and .value

CREATE OR ALTER FUNCTION GetTableInfo (@Tablename sysname)
RETURNS TABLE
AS RETURN
(
    SELECT PersonId AS Id,
        '"' + STUFF(v.XmlValues.query('for $c in col1 return concat(",", string($c))').value('text()[1]','nvarchar(max)'),1,1,'') + '" ' +
        '"' + STUFF(v.XmlValues.query('for $c in col2 return concat(",", string($c))').value('text()[1]','nvarchar(max)'),1,1,'') + '" ' +
        '"' + STUFF(v.XmlValues.query('for $c in col3 return concat(",", string($c))').value('text()[1]','nvarchar(max)'),1,1,'') + '" ' AS RollupValues
    FROM (SELECT DISTINCT PersonId FROM Person) t1
    CROSS APPLY (VALUES((
            SELECT col1, col2, col3
            FROM Person t2
            WHERE t2.PersonId = t1.PersonId
            ORDER BY t2.Pos
            FOR XML PATH (''), TYPE
        ))) v(XmlValues)
    WHERE @Tablename = 'Person'
   UNION ALL
    SELECT ClientId,
        '"' + STUFF(v.XmlValues.query('for $c in col1 return concat(",", string($c))').value('text()[1]','nvarchar(max)'),1,1,'') + '" ' +
        '"' + STUFF(v.XmlValues.query('for $c in col2 return concat(",", string($c))').value('text()[1]','nvarchar(max)'),1,1,'') + '" ' +
        '"' + STUFF(v.XmlValues.query('for $c in col3 return concat(",", string($c))').value('text()[1]','nvarchar(max)'),1,1,'') + '" ' AS RollupValues
    FROM (SELECT DISTINCT ClientId FROM Client) t1
    CROSS APPLY (VALUES((
            SELECT col1, col2, col3
            FROM Client t2
            WHERE t2.ClientId = t1.ClientId
            ORDER BY t2.Pos
            FOR XML PATH (''), TYPE
        ))) v(XmlValues)
    WHERE @Tablename = 'Client'
   UNION ALL
......
);

GO

To reiterate, you need to replace col1,col2 with the actual column names, same with the table names. You cannot do this with dynamic SQL in a function.

For completeness, I will show the STRING_AGG method which is far simpler:

CREATE OR ALTER FUNCTION GetTableInfo (@Tablename sysname)
RETURNS TABLE
AS RETURN
(
    SELECT PersonId AS Id,
        '"' + STRING_AGG(CAST(col1 AS nvarchar(max)), ', ') WITHIN GROUP (ORDER BY Pos) + '" ' +
        '"' + STRING_AGG(CAST(col2 AS nvarchar(max)), ', ') WITHIN GROUP (ORDER BY Pos) + '" ' +
        '"' + STRING_AGG(CAST(col3 AS nvarchar(max)), ', ') WITHIN GROUP (ORDER BY Pos) + '" ' AS RollupValues
    FROM Person
    WHERE @Tablename = 'Person'
    GROUP BY PersonId
   UNION ALL
    SELECT ClientId,
        '"' + STRING_AGG(CAST(col1 AS nvarchar(max)), ', ') WITHIN GROUP (ORDER BY Pos) + '" ' +
        '"' + STRING_AGG(CAST(col2 AS nvarchar(max)), ', ') WITHIN GROUP (ORDER BY Pos) + '" ' +
        '"' + STRING_AGG(CAST(col3 AS nvarchar(max)), ', ') WITHIN GROUP (ORDER BY Pos) + '" ' AS RollupValues
    FROM Client
    WHERE @Tablename = 'Client'
    GROUP BY ClientId
   UNION ALL
......
);

GO

@MikaelEriksson I'm not sure this would work. While it's very clever, it requires that I specify the column names for every table, correct? — Mike S
– Mike S, Commented Apr 24, 2021 at 0:21
@MikaelEriksson You're quite right, I have now modified it and it should work now — Charlieface
– Charlieface, Commented Apr 24, 2021 at 23:19
@MikeS You have to supply every column because you want a UDF, you cannot do dynamic SQL in a UDF — Charlieface
– Charlieface, Commented Apr 24, 2021 at 23:21

Paul White · Accepted Answer · 2021-04-26 13:25:51Z

I don't believe it is possible to do exactly what you want with a function, because it must have a fixed output shape (number, types, and names of columns).

One possible approximation is to return a fixed number of columns (with generic names), each containing an aggregation of strings, with null returned for the extra columns not applicable to a source table with fewer columns than the maximum.

As noted in other answers, STRING_AGG is ideal for that, but not available in SQL Server 2016. An efficient replacement for that can be provided by a SQL CLR streaming table-valued function as noted in the linked Q & A. Now, I know you'll say you cannot use SQL CLR for whatever reason, but for the benefit of future readers with a similar requirement, here is an example implementation.

The code uses a loopback connection for technical reasons, so the first couple of parameters specify the server/instance name and the database name. The third parameter is the table, which is expected to contain an integer id column, and an integer ordering column in the second position. The remaining columns are all assumed to be strings.

This sample implementation is limited to five such columns. It makes a single ordered pass over the table, and minimizes memory usage by only keeping the current group in memory at one time. It ought to be much faster than the XML PATH solutions.

Example calls and results

SELECT 
    GTD.id,
    GTD.col1, 
    GTD.col2, 
    GTD.col3, 
    GTD.col4, 
    GTD.col5
FROM dbo.GetTableData
(
    @@SERVERNAME,
    DB_NAME(),
    N'dbo.Person'
) AS GTD;

id	col1	col2	col3	col4	col5
12345	Basketball, Baseball, Boxing, Tennis	Bachelor of Science, Master of Science	NULL	NULL	NULL
22222	Golf	Bachelor of Science, Master of Science, Doctorate	NULL	NULL	NULL

SELECT 
    GTD.id,
    GTD.col1, 
    GTD.col2, 
    GTD.col3, 
    GTD.col4, 
    GTD.col5
FROM dbo.GetTableData
(
    @@SERVERNAME,
    DB_NAME(),
    N'dbo.Client'
) AS GTD;

id	col1	col2	col3	col4	col5
33333	North Carolina, New York	English, Spanish, Portuguese	Phone, Email	NULL	NULL
44444	California	English	Phone, Email	NULL	NULL

T-SQL

CREATE ASSEMBLY [DBA] AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300E8B986600000000000000000E00022200B013000001C00000006000000000000123A0000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000C03900004F00000000400000A802000000000000000000000000000000000000006000000C000000883800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000181A000000200000001C000000020000000000000000000000000000200000602E72737263000000A80200000040000000040000001E0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000002200000000000000000000000000004000004200000000000000000000000000000000F439000000000000480000000200050018260000701200000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000761FFE730400000625027D0500000425037D0700000425047D090000042A000013300300710000000100001102A50300001B0A03067B1000000A810D00000104067B1100000A16A30E000001810E00000105067B1100000A17A30E000001810E0000010E04067B1100000A18A30E000001810E0000010E05067B1100000A19A30E000001810E0000010E06067B1100000A1AA30E000001810E0000012A1E02281200000A2A6602281200000A02037D0100000402281300000A7D030000042A001B3002003400000002000011027B010000040A061FFC5917360606175917351F00061FFC2E08061759173602DE1100DE0E022808000006DC022807000006DC2A011C000002002300022500070000000002001500172C0007000000001B300500BD03000003000011027B010000040B074503000000070000008B02000083030000160ADD9B03000002157D01000004731400000A25027B040000046F1500000A25027B060000046F1600000A25176F1700000A251B6F1800000A25166F1900000A0C02086F1A00000A731B00000A7D0A000004021FFD7D01000004027B0A0000046F1C00000ADE0F130572010000701105731D00000A7A027239000070027B08000004725F000070281E00000A027B0A000004731F00000A7D0B000004021FFC7D01000004027B0B0000046F2000000A0D7E2100000A096F2200000A2C3B1B8D1F00000125167271000070A22517027B08000004A225187281000070A22519027B06000004A2251A72B3000070A2282300000A732400000A7A027B0B00000472B7000070027B0800000472D5000070281E00000A6F2500000A02027B0B0000046F2600000A7D0C00000402027B0C0000046F2700000A18597D0D000004027B0D0000041B312C72F50000707209010070027B08000004027B0D0000048C240000011B8C24000001282800000A732900000A7A02027B0D0000048D1A0000017D0E000004021B8D0E0000017D0F0000041613062B35027B0F00000411067E2A00000AA40E0000011106027B0D0000042F13027B0E00000411062000010000732B00000AA211061758130611061B32C67E2C00000A1304384201000002027B0C000004166F2D00000A7D10000004027C10000004282E00000A3A14010000027C1000000411048C0D000001FE160D0000016F2200000A3A8D0000001204282E00000A2D721613072B35027B0F0000041107027B0E00000411079A6F2F00000A733000000AA40E000001027B0E00000411079A6F3100000A261107175813071107027B0D00000432C1021104027B0F000004733200000A8C0300001B7D0200000402177D01000004170ADD17010000021FFC7D010000041204027C10000004283300000A283400000A1613082B5B027B0C000004110818586F3500000A13091209283600000A2D3B027B0E00000411089A6F3700000A173114027B0E00000411089A727D0100706F3800000A26027B0E00000411089A1209283900000A6F3800000A261108175813081108027B0D000004329B027C10000004FE150D000001027B0C0000046F3A00000A3AAEFEFFFF16130A2B26027B0F000004110A027B0E000004110A9A6F2F00000A733000000AA40E000001110A1758130A110A027B0D00000432D0021104027B0F000004733200000A8C0300001B7D0200000402187D01000004170ADE1F021FFC7D01000004160A022808000006022807000006DE07022805000006DC062A0000004134000000000000730000000D000000800000000F000000140000010400000000000000B4030000B403000007000000000000006E02157D01000004027B0A0000042C0B027B0A0000046F0800000A2A72021FFD7D01000004027B0B0000042C0B027B0B0000046F0800000A2A1E027B020000042A1A733B00000A7A133002004F00000006000011027B010000041FFE3318027B03000004281300000A330B02167D01000004020A2B071673040000060A06027B050000047D0400000406027B070000047D0600000406027B090000047D08000004062A1E02280C0000062A0042534A4201000100000000000C00000076342E302E33303331390000000005006C00000044050000237E0000B00500003007000023537472696E677300000000E00C00008401000023555300640E0000100000002347554944000000740E0000FC03000023426C6F6200000000000000020000015717A20B0902000000FA013300160000010000002600000003000000100000000D0000000B000000050000003B000000100000000600000001000000020000000200000007000000030000000100000002000000010000000000420301000000000006009F0203050600D102030506004002F0040F00230500000600840562030600FA0162030600540203050A008A0252040A008F0152040A00890152040600C1014E050A00BF0252040A004C0032050A00FC0232050600640062030600250203050600CD01620306006D044E050A003704B0050600C3036203060022001A01060014001A010A007B03B0050A007701B0050A000104B00506004404EF0606007202F0040600C60562030A001D04CD030A006E03CD0306001D0362030A006D01CD030600C00662030600BB0362030A00F403CD0306004F00620306009F03620306008903620300000000EF0000000000010001000100100061050000150001000100030110000100000015000100040001001A02E7000100A406980001005001E7000100A301E40006009E01E4000100ED01E4000600E801E4000100DE01E4000600D901E40001005500F80001008A00FC0001009D0000010100AF00E7000100C20004010100CB0009010100E600C100502000000000960004010E01010070200000000096000B0716010400ED20000000008618EA0406000B00F520000000008618EA0401000B00102100000000E101FF0106000C006C2100000000E101E60616000C006C250000000081003B0006000C0088250000000081007C0006000C00A52500000000E109370625000C00AD2500000000E1018B0506000C00A52500000000E109790625000C00B42500000000E10179042D010C000F2600000000E101BD0438000C0000000100A30100000200ED0100000300DE01000001001307020002006A0102000300300002000400710002000500980002000600AA0002000700BD00000001001A0203000A0003002D000300060003004500030049000900EA0401001100EA0406001900EA040A003900EA0410004100EA0406006100EA0406008100EA0406008900120206009100E60616000C00980620009100AA0506009100980625001400DC042F005900DC043800D900EA0406001C00350053001C00760057002900EA040600E10035015B009900EA0406009900B2017500990024037500990017077A009900D30601009900C8067A00E90006037F00B900EA047500F10069030600A100EA048300F90076058A00C100EA0491000101E6032500090150039800290047059B00F9007605A0001101EA0475000101FB067500C1000F04A6001901B106AB00F9007D05AF002901EA04B70071005D03BD00D100EA04010069005D03C100C9004900C50069005703160029001B037F007100EA047500D100E003CB001C00EA04D0006900EF02AB006900EA040100C900F902D800710057031600D1003703AB00D1008201DE007100EF027F001901650116003101EA040600200023006A0120002B00970124003300D5032E000B0039012E00130042012E001B00610144003300D50363003B00D00364003300E80380007B00D003A0007B00D00320017B00D00340017B00D00360017B00D00380017B00D003A0017B00D0033D005F006300E400E700EA00030001000000D205350100001006350102000900030002000B00050003000A00110003000C001300030012001500030014001700030016001900030018001B0003001A001D001A0029004900048000000000000000000000000000000000DC000000040000000000000000000000EF00110100000000040000000000000000000000EF00F800000000000300020000000000003C4765745461626C65446174613E645F5F300049456E756D657261626C6560310049456E756D657261746F72603100636F6C31004974656D31003C3E6D5F5F46696E616C6C79310047657453716C496E743332003C6C6F6F706261636B3E355F5F320056616C75655475706C65603200636F6C32004974656D32003C3E6D5F5F46696E616C6C7932003C636F6D6D616E643E355F5F3300636F6C33003C7265616465723E355F5F3400636F6C34003C636F6C756D6E733E355F5F3500636F6C35003C73623E355F5F36003C53716C537472696E67733E355F5F3700446174616261736537003C69643E355F5F38003C4D6F64756C653E0053797374656D2E44617461004765745461626C6544617461006D73636F726C69620053797374656D2E436F6C6C656374696F6E732E47656E65726963006765745F43757272656E744D616E616765645468726561644964003C3E6C5F5F696E697469616C54687265616449640052656164006964004462436F6D6D616E640053716C436F6D6D616E6400417070656E640053797374656D446174614163636573734B696E64003C3E335F5F536572766572496E7374616E6365007365745F44617461536F757263650049456E756D657261626C650049446973706F7361626C65003C3E335F5F5461626C654E616D65003C3E335F5F44617461626173654E616D6500547970650053797374656D2E49446973706F7361626C652E446973706F7365003C3E315F5F737461746500436F6D70696C657247656E6572617465644174747269627574650044656275676761626C65417474726962757465004974657261746F7253746174654D616368696E6541747472696275746500446562756767657248696464656E4174747269627574650053716C46756E6374696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650053716C46616365744174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C75650047657453716C537472696E67006765745F436F6E6E656374696F6E537472696E6700546F537472696E67007365745F496E697469616C436174616C6F67006765745F4C656E677468004461746162617365372E646C6C0044424E756C6C006765745F49734E756C6C0053797374656D004F70656E004462436F6E6E656374696F6E0053716C436F6E6E656374696F6E004E6F74537570706F72746564457863657074696F6E00417267756D656E744F75744F6652616E6765457863657074696F6E00417267756D656E74457863657074696F6E0053797374656D2E446174612E436F6D6D6F6E00436C65617200457865637574655363616C6172004462446174615265616465720053716C446174615265616465720045786563757465526561646572004462436F6E6E656374696F6E537472696E674275696C6465720053716C436F6E6E656374696F6E537472696E674275696C646572004D6963726F736F66742E53716C5365727665722E5365727665720049456E756D657261746F720053797374656D2E436F6C6C656374696F6E732E47656E657269632E49456E756D657261626C653C53797374656D2E4F626A6563743E2E476574456E756D657261746F720053797374656D2E436F6C6C656374696F6E732E49456E756D657261626C652E476574456E756D657261746F72002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C547970657300457175616C730053797374656D2E436F6C6C656374696F6E730055736572446566696E656446756E6374696F6E7300436F6E63617400466F726D6174004F626A6563740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E52657365740053797374656D2E446174612E53716C436C69656E7400456E7669726F6E6D656E740053797374656D2E436F6C6C656374696F6E732E47656E657269632E49456E756D657261746F723C53797374656D2E4F626A6563743E2E43757272656E740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E43757272656E740053797374656D2E436F6C6C656374696F6E732E47656E657269632E49456E756D657261746F723C53797374656D2E4F626A6563743E2E6765745F43757272656E740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E6765745F43757272656E74003C3E325F5F63757272656E74006765745F4669656C64436F756E7400436F6E76657274007365745F456E6C697374007365745F436F6E6E65637454696D656F7574004D6F76654E6578740053797374656D2E54657874007365745F436F6D6D616E64546578740046696C6C526F7700726F77007365745F496E7465677261746564536563757269747900000000374C006F006F0070006200610063006B00200063006F006E006E0065006300740069006F006E0020006600610069006C00650064002E000025530045004C0045004300540020004F0042004A004500430054005F004900440028002700011127002C00200027005500270029003B00010F5400610062006C006500200027000131270020006E006F007400200066006F0075006E006400200069006E00200064006100740061006200610073006500200001032E00001D530045004C0045004300540020002A002000460052004F004D002000001F20004F005200440045005200200042005900200031002C00200032003B0000135400610062006C0065004E0061006D00650000735400610062006C00650020007B0030007D00200068006100730020007B0031007D0020006100640064006900740069006F006E0061006C00200063006F006C0075006D006E0073002E00200054006800650020006D006100780069006D0075006D0020006900730020007B0032007D002E0000052C00200000002361A2CFA2AF2F4BB45B977B78FEFDCA000420010108032000010520010111110520010112190320000205151255011C04200013000320001C05151259011C08200015125501130004200012490B070115113D0211351D11390915113D0211351D11390306130003061301030000080307010811070B0208124D1C11351251080808113908042001010E04200101020320000E062002010E12510600030E0E0E0E062002010E125D02061C042001021C0500010E1D0E0420001265032000080700040E0E1C1C1C052002010E0E03061139030611350520011135080420001269072002011300130105200111390805200112690E02060E020608040701120C08B77A5C561934E0890306125D030612610306126504061D126904061D1139070003122D0E0E0E160007011C101135101139101139101139101139101139072000151255011C0328001C0801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000200000000002C01002755736572446566696E656446756E6374696F6E732B3C4765745461626C65446174613E645F5F3000008237010004005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730100000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D4461746141636365737300000000540E1146696C6C526F774D6574686F644E616D650746696C6C526F77540E0F5461626C65446566696E6974696F6E80E10D0A202020202020202020202020696420696E7465676572204E554C4C2C0D0A202020202020202020202020636F6C31206E76617263686172286D617829204E554C4C2C0D0A202020202020202020202020636F6C32206E76617263686172286D617829204E554C4C2C0D0A202020202020202020202020636F6C33206E76617263686172286D617829204E554C4C2C0D0A202020202020202020202020636F6C34206E76617263686172286D617829204E554C4C2C0D0A202020202020202020202020636F6C35206E76617263686172286D617829204E554C4C0D0A20202020040100000012010001005408074D617853697A658000000012010001005408074D617853697A65010100000000000000E8B9866000000000020000001C010000A4380000A41A0000525344533A0A7AF47A862C41B751DA4E601A730601000000433A5C55736572735C7061756C775C4F6E6544726976655C446F63756D656E74735C736F757263655C7265706F735C4461746162617365375C4461746162617365375C6F626A5C52656C656173655C4461746162617365372E706462000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000E83900000000000000000000023A0000002000000000000000000000000000000000000000000000F4390000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000004C02000000000000000000004C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004AC010000010053007400720069006E006700460069006C00650049006E0066006F0000008801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E00300000003C000E00010049006E007400650072006E0061006C004E0061006D00650000004400610074006100620061007300650037002E0064006C006C0000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000044000E0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004400610074006100620061007300650037002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000143A00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
CREATE OR ALTER FUNCTION dbo.GetTableData
(
    @ServerInstance [nvarchar](128), 
    @DatabaseName [nvarchar](128), 
    @TableName [nvarchar](257)
)
RETURNS TABLE 
(
    id integer NULL,
    col1 nvarchar(max) NULL,
    col2 nvarchar(max) NULL,
    col3 nvarchar(max) NULL,
    col4 nvarchar(max) NULL,
    col5 nvarchar(max) NULL
)
ORDER (id)
AS EXTERNAL NAME [DBA].[UserDefinedFunctions].[GetTableData];

C# Source

using Microsoft.SqlServer.Server;
using System;
using System.Collections;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;

public partial class UserDefinedFunctions
{
    [SqlFunction(
        DataAccess = DataAccessKind.Read,
        SystemDataAccess = SystemDataAccessKind.None,
        FillRowMethodName = "FillRow",
        TableDefinition = @"
            id integer NULL,
            col1 nvarchar(max) NULL,
            col2 nvarchar(max) NULL,
            col3 nvarchar(max) NULL,
            col4 nvarchar(max) NULL,
            col5 nvarchar(max) NULL
    ")]
    public static IEnumerable GetTableData
    (
        [SqlFacet(MaxSize = 128)] string ServerInstance,
        [SqlFacet(MaxSize = 128)] string DatabaseName,
        [SqlFacet(MaxSize = 257)] string TableName
    )
    {
        const string COMMA_SPACE = ", ";
        const int MAX_OUTPUT_COLS = 5;
        const int FIXED_COLS = 2;

        // Establish loopback connection
        var csb = new SqlConnectionStringBuilder
        {
            DataSource = ServerInstance,
            InitialCatalog = DatabaseName,
            IntegratedSecurity = true,
            ConnectTimeout = 5,
            Enlist = false
        };

        using var loopback = new SqlConnection(csb.ConnectionString);
        try
        {
            loopback.Open();
        }
        catch (Exception e)
        {
            throw new Exception("Loopback connection failed.", e);
        }

        // Check supplied table name exists and is accessible
        using var command = new SqlCommand($@"SELECT OBJECT_ID('{TableName}', 'U');", loopback);

        object object_id = command.ExecuteScalar();

        if (Convert.DBNull.Equals(object_id))
        {
            throw new ArgumentException($@"Table '{TableName}' not found in database {DatabaseName}.");
        }

        // Read table in the required order
        command.CommandText = $@"SELECT * FROM {TableName} ORDER BY 1, 2;";
        SqlDataReader reader = command.ExecuteReader();

        // Number of columns to process (skipping id and position)
        int columns = reader.FieldCount - FIXED_COLS;

        if (columns > MAX_OUTPUT_COLS)
        {
            throw new ArgumentOutOfRangeException(
                nameof(TableName),
                $@"Table {TableName} has {columns} additional columns. The maximum is {MAX_OUTPUT_COLS}.");
        }

        // Create a string builder and SqlString array element for each output column
        StringBuilder[] sb = new StringBuilder[columns];
        SqlString[] SqlStrings = new SqlString[MAX_OUTPUT_COLS];

        for (int i = 0; i < MAX_OUTPUT_COLS; i++)
        {
            SqlStrings[i] = SqlString.Null;
            if (i < columns)
            {
                sb[i] = new StringBuilder(256);
            }
        }

        // Remember the current id value
        SqlInt32 previous_id = SqlInt32.Null;

        // Process each row
        while (reader.Read())
        {
            SqlInt32 id = reader.GetSqlInt32(0);

            if (!id.IsNull)
            {
                if (!id.Equals(previous_id))
                {
                    if (!previous_id.IsNull)
                    {
                        // Completed a group
                        for (int i = 0; i < columns; i++)
                        {
                            SqlStrings[i] = new SqlString(sb[i].ToString());
                            sb[i].Clear();
                        }

                        // Output the completed group
                        yield return (previous_id, SqlStrings);
                    }
                    previous_id = new SqlInt32(id.Value);
                }

                // Add the current row to the string builders
                for (int i = 0; i < columns; i++)
                {
                    var s = reader.GetSqlString(i + FIXED_COLS);
                    if (!s.IsNull)
                    {
                        if (sb[i].Length > 1)
                        {
                            sb[i].Append(COMMA_SPACE);
                        }
                        sb[i].Append(s.Value);
                    }
                }
            }
        }

        // Final group
        for (int i = 0; i < columns; i++)
        {
            SqlStrings[i] = new SqlString(sb[i].ToString());
        }

        // Output the completed group
        yield return (previous_id, SqlStrings);
    }

    // Called by SQL Server to populate each row returned from the TVF
    public static void FillRow
        (
        object row,
        out SqlInt32 id,
        out SqlString col1,
        out SqlString col2,
        out SqlString col3,
        out SqlString col4,
        out SqlString col5
        )
    {
        (SqlInt32 id, SqlString[] cols) v = ((SqlInt32, SqlString[]))row;
        id = v.id;
        col1 = v.cols[0];
        col2 = v.cols[1];
        col3 = v.cols[2];
        col4 = v.cols[3];
        col5 = v.cols[4];
    }
}

Paul, thank you! I appreciate the response. A couple of things. First, You say a function isn’t the right tool for the job. If it’s not, what is? Second, I should’ve been clearer in my requirements. It’s not that it has to be a function...it’s that I have to be able to use it in a select query...so that’s why I had excluded SPs. Third, in my example of what I would like the rolled up values to look like, I can see how it looks like one large text-qualified, delimited string, but I meant for them to be rolled up within their existing columns, so id, pos, col1, col2, etc. Sorry about that. — Mike S
– Mike S, Commented Apr 25, 2021 at 16:26
You should add those important clarifications to your question. All the answers here assumed the same single rolled-up column because a select query must have a defined output shape. You'll need to rethink that, or specify a maximum number of columns and be prepared to handle unused columns containing null or whatever, and generic column names. You can't have a select query return a variable number of columns with different names. Perhaps you could work with xml or json output idk. Another option would be to split the delimited string we are all returning yourself. — Paul White
– Paul White ♦, Commented Apr 26, 2021 at 0:05
Paul, you're right--I should (and am going to) add those clarifications. I see exactly what you mean about the output shape, and maybe I'll need to employ a solution with the max # of columns and handle the extra nulls or split the delimited string. I realize none of these are ideal, and if I weren't working within these constraints, I would just get this done with a simple stored procedure. In any case, I (and I'm sure future readers) appreciate your response and general contributions to the SQL Server community. Thanks again. — Mike S
– Mike S, Commented Apr 26, 2021 at 12:48

Stack Exchange Network

How can parse column names using ordinal position (not name) from XML data in SQL Server?

3 Answers 3

Example calls and results

T-SQL

C# Source

Your Answer

Linked

Hot Network Questions

How can parse column names using ordinal position (not name) from XML data in SQL Server?

3 Answers 3

Example calls and results

T-SQL

C# Source

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions