JOINSTR
Updated 2023-11-10 15:30:35.163000
Syntax
SELECT [westclintech].[wct].[JOINSTR] (
<@Separator, nvarchar(4000),>
,<@MaxItems, int,>
,<@Values_Tablename, nvarchar(4000),>
,<@ColumnName, nvarchar(4000),>
,<@GroupedColumnName, nvarchar(4000),>
,<@GroupedColumnValue, sql_variant,>)
Description
Use the scalar function JOINSTR to concatenate the text from multiple rows in a table.
Arguments
@GroupedColumnName
the name, as text, of the column in the table or view specified by @Values_Tablename which will be used for grouping the results.
@ColumnName
the name of the column in @Values_Tablename that contains the text to be concatenated. The @Column_tablename argument can be of data types that are implicitly convertible to nvarchar or ntext.
@Separator
is the text value to be used to connect the text to be concatenated. The @Separator argument can be of data types that are implicitly convertible to nvarchar or ntext.
@GroupedColumnValue
the column value to do the grouping on.
@MaxItems
is the maximum number of items to be included in the resultant text. The @MaxItems argument can be of data types that are implicitly convertible to int.
@Values_Tablename
the name of the table that contains the text to be concatenated. The @Values_tablename argument can be of data types that are implicitly convertible to nvarchar or ntext.
Return Type
nvarchar(max)
Remarks
For more complicated queries, consider using the JOINSTR_q function.
Examples
For this example, we have loaded up the Gettysburg address into a table and we are going to use JOINSTR to put it back together.
SELECT *
INTO #docs
FROM
(
VALUES
('Gettysburg Address', 1, 1, 1, 'Four'),
('Gettysburg Address', 2, 1, 2, 'score'),
('Gettysburg Address', 3, 1, 3, 'and'),
('Gettysburg Address', 4, 1, 4, 'seven'),
('Gettysburg Address', 5, 1, 5, 'years'),
('Gettysburg Address', 6, 1, 6, 'ago'),
('Gettysburg Address', 7, 1, 7, 'our'),
('Gettysburg Address', 8, 1, 8, 'fathers'),
('Gettysburg Address', 9, 1, 9, 'brought'),
('Gettysburg Address', 10, 1, 10, 'forth'),
('Gettysburg Address', 11, 1, 11, 'on'),
('Gettysburg Address', 12, 1, 12, 'this'),
('Gettysburg Address', 13, 1, 13, 'continent,'),
('Gettysburg Address', 14, 1, 14, 'a'),
('Gettysburg Address', 15, 1, 15, 'new'),
('Gettysburg Address', 16, 1, 16, 'nation,'),
('Gettysburg Address', 17, 1, 17, 'conceived'),
('Gettysburg Address', 18, 1, 18, 'in'),
('Gettysburg Address', 19, 1, 19, 'Liberty,'),
('Gettysburg Address', 20, 1, 20, 'and'),
('Gettysburg Address', 21, 1, 21, 'dedicated'),
('Gettysburg Address', 22, 1, 22, 'to'),
('Gettysburg Address', 23, 1, 23, 'the'),
('Gettysburg Address', 24, 1, 24, 'proposition'),
('Gettysburg Address', 25, 1, 25, 'that'),
('Gettysburg Address', 26, 1, 26, 'all'),
('Gettysburg Address', 27, 1, 27, 'men'),
('Gettysburg Address', 28, 1, 28, 'are'),
('Gettysburg Address', 29, 1, 29, 'created'),
('Gettysburg Address', 30, 1, 30, 'equal.'),
('Gettysburg Address', 31, 2, 1, 'Now'),
('Gettysburg Address', 32, 2, 2, 'we'),
('Gettysburg Address', 33, 2, 3, 'are'),
('Gettysburg Address', 34, 2, 4, 'engaged'),
('Gettysburg Address', 35, 2, 5, 'in'),
('Gettysburg Address', 36, 2, 6, 'a'),
('Gettysburg Address', 37, 2, 7, 'great'),
('Gettysburg Address', 38, 2, 8, 'civil'),
('Gettysburg Address', 39, 2, 9, 'war,'),
('Gettysburg Address', 40, 2, 10, 'testing'),
('Gettysburg Address', 41, 2, 11, 'whether'),
('Gettysburg Address', 42, 2, 12, 'that'),
('Gettysburg Address', 43, 2, 13, 'nation,'),
('Gettysburg Address', 44, 2, 14, 'or'),
('Gettysburg Address', 45, 2, 15, 'any'),
('Gettysburg Address', 46, 2, 16, 'nation'),
('Gettysburg Address', 47, 2, 17, 'so'),
('Gettysburg Address', 48, 2, 18, 'conceived'),
('Gettysburg Address', 49, 2, 19, 'and'),
('Gettysburg Address', 50, 2, 20, 'so'),
('Gettysburg Address', 51, 2, 21, 'dedicated,'),
('Gettysburg Address', 52, 2, 22, 'can'),
('Gettysburg Address', 53, 2, 23, 'long'),
('Gettysburg Address', 54, 2, 24, 'endure.'),
('Gettysburg Address', 55, 2, 25, 'We'),
('Gettysburg Address', 56, 2, 26, 'are'),
('Gettysburg Address', 57, 2, 27, 'met'),
('Gettysburg Address', 58, 2, 28, 'on'),
('Gettysburg Address', 59, 2, 29, 'a'),
('Gettysburg Address', 60, 2, 30, 'great'),
('Gettysburg Address', 61, 2, 31, 'battle-field'),
('Gettysburg Address', 62, 2, 32, 'of'),
('Gettysburg Address', 63, 2, 33, 'that'),
('Gettysburg Address', 64, 2, 34, 'war.'),
('Gettysburg Address', 65, 2, 35, 'We'),
('Gettysburg Address', 66, 2, 36, 'have'),
('Gettysburg Address', 67, 2, 37, 'come'),
('Gettysburg Address', 68, 2, 38, 'to'),
('Gettysburg Address', 69, 2, 39, 'dedicate'),
('Gettysburg Address', 70, 2, 40, 'a'),
('Gettysburg Address', 71, 2, 41, 'portion'),
('Gettysburg Address', 72, 2, 42, 'of'),
('Gettysburg Address', 73, 2, 43, 'that'),
('Gettysburg Address', 74, 2, 44, 'field,'),
('Gettysburg Address', 75, 2, 45, 'as'),
('Gettysburg Address', 76, 2, 46, 'a'),
('Gettysburg Address', 77, 2, 47, 'final'),
('Gettysburg Address', 78, 2, 48, 'resting'),
('Gettysburg Address', 79, 2, 49, 'place'),
('Gettysburg Address', 80, 2, 50, 'for'),
('Gettysburg Address', 81, 2, 51, 'those'),
('Gettysburg Address', 82, 2, 52, 'who'),
('Gettysburg Address', 83, 2, 53, 'here'),
('Gettysburg Address', 84, 2, 54, 'gave'),
('Gettysburg Address', 85, 2, 55, 'their'),
('Gettysburg Address', 86, 2, 56, 'lives'),
('Gettysburg Address', 87, 2, 57, 'that'),
('Gettysburg Address', 88, 2, 58, 'that'),
('Gettysburg Address', 89, 2, 59, 'nation'),
('Gettysburg Address', 90, 2, 60, 'might'),
('Gettysburg Address', 91, 2, 61, 'live.'),
('Gettysburg Address', 92, 2, 62, 'It'),
('Gettysburg Address', 93, 2, 63, 'is'),
('Gettysburg Address', 94, 2, 64, 'altogether'),
('Gettysburg Address', 95, 2, 65, 'fitting'),
('Gettysburg Address', 96, 2, 66, 'and'),
('Gettysburg Address', 97, 2, 67, 'proper'),
('Gettysburg Address', 98, 2, 68, 'that'),
('Gettysburg Address', 99, 2, 69, 'we'),
('Gettysburg Address', 100, 2, 70, 'should'),
('Gettysburg Address', 101, 2, 71, 'do'),
('Gettysburg Address', 102, 2, 72, 'this.'),
('Gettysburg Address', 103, 3, 1, 'But,'),
('Gettysburg Address', 104, 3, 2, 'in'),
('Gettysburg Address', 105, 3, 3, 'a'),
('Gettysburg Address', 106, 3, 4, 'larger'),
('Gettysburg Address', 107, 3, 5, 'sense,'),
('Gettysburg Address', 108, 3, 6, 'we'),
('Gettysburg Address', 109, 3, 7, 'can'),
('Gettysburg Address', 110, 3, 8, 'not'),
('Gettysburg Address', 111, 3, 9, 'dedicate—we'),
('Gettysburg Address', 112, 3, 10, 'can'),
('Gettysburg Address', 113, 3, 11, 'not'),
('Gettysburg Address', 114, 3, 12, 'consecrate—we'),
('Gettysburg Address', 115, 3, 13, 'can'),
('Gettysburg Address', 116, 3, 14, 'not'),
('Gettysburg Address', 117, 3, 15, 'hallow—this'),
('Gettysburg Address', 118, 3, 16, 'ground.'),
('Gettysburg Address', 119, 3, 17, 'The'),
('Gettysburg Address', 120, 3, 18, 'brave'),
('Gettysburg Address', 121, 3, 19, 'men,'),
('Gettysburg Address', 122, 3, 20, 'living'),
('Gettysburg Address', 123, 3, 21, 'and'),
('Gettysburg Address', 124, 3, 22, 'dead,'),
('Gettysburg Address', 125, 3, 23, 'who'),
('Gettysburg Address', 126, 3, 24, 'struggled'),
('Gettysburg Address', 127, 3, 25, 'here,'),
('Gettysburg Address', 128, 3, 26, 'have'),
('Gettysburg Address', 129, 3, 27, 'consecrated'),
('Gettysburg Address', 130, 3, 28, 'it,'),
('Gettysburg Address', 131, 3, 29, 'far'),
('Gettysburg Address', 132, 3, 30, 'above'),
('Gettysburg Address', 133, 3, 31, 'our'),
('Gettysburg Address', 134, 3, 32, 'poor'),
('Gettysburg Address', 135, 3, 33, 'power'),
('Gettysburg Address', 136, 3, 34, 'to'),
('Gettysburg Address', 137, 3, 35, 'add'),
('Gettysburg Address', 138, 3, 36, 'or'),
('Gettysburg Address', 139, 3, 37, 'detract.'),
('Gettysburg Address', 140, 3, 38, 'The'),
('Gettysburg Address', 141, 3, 39, 'world'),
('Gettysburg Address', 142, 3, 40, 'will'),
('Gettysburg Address', 143, 3, 41, 'little'),
('Gettysburg Address', 144, 3, 42, 'note,'),
('Gettysburg Address', 145, 3, 43, 'nor'),
('Gettysburg Address', 146, 3, 44, 'long'),
('Gettysburg Address', 147, 3, 45, 'remember'),
('Gettysburg Address', 148, 3, 46, 'what'),
('Gettysburg Address', 149, 3, 47, 'we'),
('Gettysburg Address', 150, 3, 48, 'say'),
('Gettysburg Address', 151, 3, 49, 'here,'),
('Gettysburg Address', 152, 3, 50, 'but'),
('Gettysburg Address', 153, 3, 51, 'it'),
('Gettysburg Address', 154, 3, 52, 'can'),
('Gettysburg Address', 155, 3, 53, 'never'),
('Gettysburg Address', 156, 3, 54, 'forget'),
('Gettysburg Address', 157, 3, 55, 'what'),
('Gettysburg Address', 158, 3, 56, 'they'),
('Gettysburg Address', 159, 3, 57, 'did'),
('Gettysburg Address', 160, 3, 58, 'here.'),
('Gettysburg Address', 161, 3, 59, 'It'),
('Gettysburg Address', 162, 3, 60, 'is'),
('Gettysburg Address', 163, 3, 61, 'for'),
('Gettysburg Address', 164, 3, 62, 'us'),
('Gettysburg Address', 165, 3, 63, 'the'),
('Gettysburg Address', 166, 3, 64, 'living,'),
('Gettysburg Address', 167, 3, 65, 'rather,'),
('Gettysburg Address', 168, 3, 66, 'to'),
('Gettysburg Address', 169, 3, 67, 'be'),
('Gettysburg Address', 170, 3, 68, 'dedicated'),
('Gettysburg Address', 171, 3, 69, 'here'),
('Gettysburg Address', 172, 3, 70, 'to'),
('Gettysburg Address', 173, 3, 71, 'the'),
('Gettysburg Address', 174, 3, 72, 'unfinished'),
('Gettysburg Address', 175, 3, 73, 'work'),
('Gettysburg Address', 176, 3, 74, 'which'),
('Gettysburg Address', 177, 3, 75, 'they'),
('Gettysburg Address', 178, 3, 76, 'who'),
('Gettysburg Address', 179, 3, 77, 'fought'),
('Gettysburg Address', 180, 3, 78, 'here'),
('Gettysburg Address', 181, 3, 79, 'have'),
('Gettysburg Address', 182, 3, 80, 'thus'),
('Gettysburg Address', 183, 3, 81, 'far'),
('Gettysburg Address', 184, 3, 82, 'so'),
('Gettysburg Address', 185, 3, 83, 'nobly'),
('Gettysburg Address', 186, 3, 84, 'advanced.'),
('Gettysburg Address', 187, 3, 85, 'It'),
('Gettysburg Address', 188, 3, 86, 'is'),
('Gettysburg Address', 189, 3, 87, 'rather'),
('Gettysburg Address', 190, 3, 88, 'for'),
('Gettysburg Address', 191, 3, 89, 'us'),
('Gettysburg Address', 192, 3, 90, 'to'),
('Gettysburg Address', 193, 3, 91, 'be'),
('Gettysburg Address', 194, 3, 92, 'here'),
('Gettysburg Address', 195, 3, 93, 'dedicated'),
('Gettysburg Address', 196, 3, 94, 'to'),
('Gettysburg Address', 197, 3, 95, 'the'),
('Gettysburg Address', 198, 3, 96, 'great'),
('Gettysburg Address', 199, 3, 97, 'task'),
('Gettysburg Address', 200, 3, 98, 'remaining'),
('Gettysburg Address', 201, 3, 99, 'before'),
('Gettysburg Address', 202, 3, 100, 'us—that'),
('Gettysburg Address', 203, 3, 101, 'from'),
('Gettysburg Address', 204, 3, 102, 'these'),
('Gettysburg Address', 205, 3, 103, 'honored'),
('Gettysburg Address', 206, 3, 104, 'dead'),
('Gettysburg Address', 207, 3, 105, 'we'),
('Gettysburg Address', 208, 3, 106, 'take'),
('Gettysburg Address', 209, 3, 107, 'increased'),
('Gettysburg Address', 210, 3, 108, 'devotion'),
('Gettysburg Address', 211, 3, 109, 'to'),
('Gettysburg Address', 212, 3, 110, 'that'),
('Gettysburg Address', 213, 3, 111, 'cause'),
('Gettysburg Address', 214, 3, 112, 'for'),
('Gettysburg Address', 215, 3, 113, 'which'),
('Gettysburg Address', 216, 3, 114, 'they'),
('Gettysburg Address', 217, 3, 115, 'gave'),
('Gettysburg Address', 218, 3, 116, 'the'),
('Gettysburg Address', 219, 3, 117, 'last'),
('Gettysburg Address', 220, 3, 118, 'full'),
('Gettysburg Address', 221, 3, 119, 'measure'),
('Gettysburg Address', 222, 3, 120, 'of'),
('Gettysburg Address', 223, 3, 121, 'devotion—that'),
('Gettysburg Address', 224, 3, 122, 'we'),
('Gettysburg Address', 225, 3, 123, 'here'),
('Gettysburg Address', 226, 3, 124, 'highly'),
('Gettysburg Address', 227, 3, 125, 'resolve'),
('Gettysburg Address', 228, 3, 126, 'that'),
('Gettysburg Address', 229, 3, 127, 'these'),
('Gettysburg Address', 230, 3, 128, 'dead'),
('Gettysburg Address', 231, 3, 129, 'shall'),
('Gettysburg Address', 232, 3, 130, 'not'),
('Gettysburg Address', 233, 3, 131, 'have'),
('Gettysburg Address', 234, 3, 132, 'died'),
('Gettysburg Address', 235, 3, 133, 'in'),
('Gettysburg Address', 236, 3, 134, 'vain—that'),
('Gettysburg Address', 237, 3, 135, 'this'),
('Gettysburg Address', 238, 3, 136, 'nation,'),
('Gettysburg Address', 239, 3, 137, 'under'),
('Gettysburg Address', 240, 3, 138, 'God,'),
('Gettysburg Address', 241, 3, 139, 'shall'),
('Gettysburg Address', 242, 3, 140, 'have'),
('Gettysburg Address', 243, 3, 141, 'a'),
('Gettysburg Address', 244, 3, 142, 'new'),
('Gettysburg Address', 245, 3, 143, 'birth'),
('Gettysburg Address', 246, 3, 144, 'of'),
('Gettysburg Address', 247, 3, 145, 'freedom—and'),
('Gettysburg Address', 248, 3, 146, 'that'),
('Gettysburg Address', 249, 3, 147, 'government'),
('Gettysburg Address', 250, 3, 148, 'of'),
('Gettysburg Address', 251, 3, 149, 'the'),
('Gettysburg Address', 252, 3, 150, 'people,'),
('Gettysburg Address', 253, 3, 151, 'by'),
('Gettysburg Address', 254, 3, 152, 'the'),
('Gettysburg Address', 255, 3, 153, 'people,'),
('Gettysburg Address', 256, 3, 154, 'for'),
('Gettysburg Address', 257, 3, 155, 'the'),
('Gettysburg Address', 258, 3, 156, 'people,'),
('Gettysburg Address', 259, 3, 157, 'shall'),
('Gettysburg Address', 260, 3, 158, 'not'),
('Gettysburg Address', 261, 3, 159, 'perish'),
('Gettysburg Address', 262, 3, 160, 'from'),
('Gettysburg Address', 263, 3, 161, 'the'),
('Gettysburg Address', 264, 3, 162, 'earth.')
) n (docname, wordno, parano, wordinpara, word);
select wct.JOINSTR(' ', NULL, '#docs', 'WORD', 'DOCNAME', 'Gettysburg Address');
This produces the following result.
{"columns":[{"field":"column 1"}],"rows":[{"column 1":"Four score and seven years ago our fathers brought forth on this continent, a new nation, conceived in Liberty, and dedicated to the proposition that all men are created equal. Now we are engaged in a great civil war, testing whether that nation, or any nation so conceived and so dedicated, can long endure. We are met on a great battle-field of that war. We have come to dedicate a portion of that field, as a final resting place for those who here gave their lives that that nation might live. It is altogether fitting and proper that we should do this. But, in a larger sense, we can not dedicate—we can not consecrate—we can not hallow—this ground. The brave men, living and dead, who struggled here, have consecrated it, far above our poor power to add or detract. The world will little note, nor long remember what we say here, but it can never forget what they did here. It is for us the living, rather, to be dedicated here to the unfinished work which they who fought here have thus far so nobly advanced. It is rather for us to be here dedicated to the great task remaining before us—that from these honored dead we take increased devotion to that cause for which they gave the last full measure of devotion—that we here highly resolve that these dead shall not have died in vain—that this nation, under God, shall have a new birth of freedom—and that government of the people, by the people, for the people, shall not perish from the earth."}]}