How Stuff and 'For Xml Path' Work in SQL Server

how to use Stuff and 'For Xml Path' to unite rows in table

Don't group by summ if you want to sum it. Use sum() on it. And correlate the subquery. Otherwise you'll just get all accounts.

SELECT i1.invoice,
       sum(i1.summ) summ,
       stuff((SELECT DISTINCT
                     concat(',', i2.account)
                     FROM invoices i2
                     WHERE i2.invoice = i1.invoice
                     FOR XML PATH ('')),
             1,
             1,
             '') accounts
       FROM invoices i1
       GROUP BY i1.invoice;

Syntax for STUFF/FOR XML PATH

Your error has nothing to do with neither STUFF nor FOR XML. It's simply syntax error with selecting from subquery.

SELECT * FROM (SELECT N',' + N'first' UNION SELECT N',' + N'second') 

You need to give alias to your query:

SELECT * FROM (SELECT N',' + N'first' UNION SELECT N',' + N'second') AS x

After which you'll get error that you need to name a column 1 of 'x', so you should add that alias as well.

SELECT * FROM (SELECT N',' + N'first' as Col1 UNION SELECT N',' + N'second') x

Your whole query will now look like this:

SELECT STUFF((SELECT * FROM
 (SELECT N',' + N'first' AS Col1 UNION SELECT N',' + N'second') AS x FOR XML PATH('')), 1, 1, '')

It will work, but I am pretty sure the result it gives is not exactly what you are looking for.

(No column name)
--------------------------------------
Col1>,first</Col1><Col1>,second</Col1>

You are probably looking to concatenate the string from your sub-query?
That should look something like this:

WITH CTE_Source AS 
(
    SELECT N',' + N'first' AS Col1 UNION SELECT N',' + N'second'
)
SELECT DISTINCT STUFF
(
    (SELECT '' + Col1 FROM CTE_Source FOR XML PATH ('')), 1, 1, ''
) 
FROM CTE_Source

to get:

(No column name)
---------------
first,second

Using GROUP BY with FOR XML PATH in SQL Server 2016

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID int, Comment nvarchar(150));
INSERT INTO @tbl VALUES 
(1006, 'I'),
(1006, 'am'),
(1006, 'good'),
(2, 'You'),
(2, 'are'),
(2, 'awesome');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = SPACE(1);

SELECT p.ID
   , STUFF((SELECT @separator + Comment 
          FROM @tbl AS c
          WHERE c.ID = p.ID
          FOR XML PATH('')), 1, LEN(@separator), '') AS Result
FROM @tbl AS p
GROUP BY p.ID
ORDER BY p.ID;

Output

+------+-----------------+
|  ID  |     Result      |
+------+-----------------+
|    2 | You are awesome |
| 1006 | I am good       |
+------+-----------------+

Why FOR XML PATH() is used in this script?

FOR XML PATH('') is used in older versions of SQL server (pre 2017) to get values from multiple rows into one. Since you're setting a variable, @SQL, you're setting a single string and therefore need one row returned.

FOR XML PATH('') produces all the MAX(CASE FieldName [...] with data from the tblValuationSubGroup table. Each row in tblValuationSubGroup contains one field. FOR XML PATH('') adds the rows into one string, which is later turned into fields during execution.

The issue FOR XML PATH('') solves in older versions of SQL Server has been solved with the STRING_AGG() function since SQL Server 2017.
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver16

Stuff and 'For Xml Path' in Sql Server

The size of a string in SSMS is limited. But the Size of an XML is not

• right click into query window
• options
• Results in grid
• Set XML to "umlimited"

Then try this:

SELECT STUFF(
    (
      SELECT CHAR(13) + CHAR(10) + t.name
      FROM sys.objects t
      FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'), 1, 2, '')
FOR XML PATH('');

I cut away 2 characters with STUFF() due to CHAR(13)+CHAR(10). With CHAR(10) only you must change this to ...),1,1,'').

If the first line might stay blank you can go without STUFF

SELECT
    (
      SELECT CHAR(10) + t.name
      FROM sys.objects t
      FOR XML PATH(''),TYPE
    ).value('text()[1]','nvarchar(max)')
FOR XML PATH('');

Click the XML and you see a result which is limited by your machines capacity only...

& instead of & in STUFF FOR XML PATH

The wrong way:

SELECT  STUFF(
            (
                SELECT  ', ' + v.name
                FROM    (
                    VALUES
                        ('bonnie & clyde'),
                        ('thelma & louise')
                )v(NAME)
                FOR XML PATH('')
            ), 1, 2, '')

The jedi path:

SELECT  STUFF(
            (
                SELECT  ', ' + v.name
                FROM    (
                    VALUES
                        ('bonnie & clyde'),
                        ('thelma & louise')
                )v(NAME)
                FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)'), 1, 2, '')

“Stuff and 'For Xml Path'” or UNION to exclude duplicate rows

Your first resultset can be handled in two ways, depending on your version of SQL Server. Try the following examples in SSMS:

Create Data

DECLARE @invoices table (
    invoice VARCHAR(20) NOT NULL, id INT NOT NULL 
);

INSERT INTO @invoices (invoice, id) VALUES 
    ( 'ty20210110', 1 ), ( 'ty20210111', 2 ), ( 'ty20210112', 3 );

DECLARE @positions table (
    position VARCHAR(20) NOT NULL, quantity INT NOT NULL, price INT NOT NULL, summ INT NOT NULL, invoice INT NOT NULL
);

INSERT INTO @positions ( position, quantity, price, summ, invoice ) VALUES
    ( 'p1000001', 2, 100, 200, 1 ),
    ( 'p1000002', 3, 100, 300, 2 ),
    ( 'p1000003', 1, 250, 250, 2 ),
    ( 'p1000004', 2, 120, 240, 1 ),
    ( 'p1000005', 4, 100, 400, 1 ),
    ( 'p1000006', 3, 100, 300, 1 ),
    ( 'p1000007', 5, 80, 400, 3 ),
    ( 'p1000008', 5, 100, 500, 3 );

DECLARE @accounts table (
    account INT NOT NULL, invoice INT NOT NULL
);

INSERT INTO @accounts ( account, invoice ) VALUES 
    ( 1, 2 ), ( 2, 2 ), ( 3, 1 ), ( 4, 3 ), ( 5, 1 ), ( 6, 3 );

If you're using SQL Server 2017+

SELECT
    positions.position,
    positions.quantity,
    positions.price,
    positions.summ,
    invoices.invoice,
    STRING_AGG ( accounts.account, ',' ) AS account
FROM @positions positions
INNER JOIN @invoices invoices 
    ON invoices.id = positions.invoice
INNER JOIN @accounts accounts 
    ON invoices.id = accounts.invoice
GROUP BY
    position, quantity, price, summ, invoices.invoice
ORDER BY
    invoice;

For Pre-SQL Server 2017

SELECT
    positions.position,
    positions.quantity,
    positions.price,
    positions.summ,
    invoices.invoice,
    acct.account
FROM @positions positions
INNER JOIN @invoices invoices 
    ON invoices.id = positions.invoice
INNER JOIN @accounts accounts 
    ON invoices.id = accounts.invoice
OUTER APPLY (
    SELECT STUFF ( (
        SELECT ',' + CAST ( a.account AS varchar(50) ) AS "text()"
        FROM @accounts a
        WHERE
            a.invoice = invoices.id
        FOR XML PATH ( '' )
    ), 1, 1, '' ) AS account
) AS acct
GROUP BY
    position, quantity, price, summ, invoices.invoice, acct.account
ORDER BY
    invoice;

Both queries return

+----------+----------+-------+------+------------+---------+
| position | quantity | price | summ |  invoice   | account |
+----------+----------+-------+------+------------+---------+
| p1000001 |        2 |   100 |  200 | ty20210110 | 3,5     |
| p1000004 |        2 |   120 |  240 | ty20210110 | 3,5     |
| p1000005 |        4 |   100 |  400 | ty20210110 | 3,5     |
| p1000006 |        3 |   100 |  300 | ty20210110 | 3,5     |
| p1000002 |        3 |   100 |  300 | ty20210111 | 1,2     |
| p1000003 |        1 |   250 |  250 | ty20210111 | 1,2     |
| p1000007 |        5 |    80 |  400 | ty20210112 | 4,6     |
| p1000008 |        5 |   100 |  500 | ty20210112 | 4,6     |
+----------+----------+-------+------+------------+---------+

The second resultset you requested gets considerably more complicated and requires the use of the XML datatype. The following example makes liberal assumptions of your data, most notably that only two accounts are expected. Having said that, you can expand the [account n] columns in the PIVOT as necessary without having to resort to dynamic SQL.

SELECT DISTINCT
    positions.position,
    positions.quantity,
    positions.price,
    positions.summ,
    invoices.invoice
    , x.*
FROM @positions positions
INNER JOIN @invoices invoices 
    ON invoices.id = positions.invoice
INNER JOIN @accounts accounts 
    ON invoices.id = accounts.invoice
OUTER APPLY (

    -- Create an XML account list --
    SELECT CAST ( (
        SELECT 
            'account ' + CAST ( ROW_NUMBER() OVER ( ORDER BY a.invoice ) AS varchar(50) ) AS id,
            a.account
        FROM @accounts a
        WHERE
            a.invoice = invoices.id
        FOR XML PATH ( 'data' ), ROOT ( 'accounts' )
    ) AS xml ) AS account_xml

) AS acct
OUTER APPLY (

    -- PIVOT the account details --
    SELECT
        *
    FROM (
        SELECT
            x.f.value( 'id[1]', 'varchar(50)' ) AS id,
            x.f.value( 'account[1]', 'varchar(50)' ) AS act
        FROM acct.account_xml.nodes( '//accounts/data' ) x(f)
    ) AS d
    PIVOT (
        MAX ( act ) FOR id IN ( [account 1], [account 2] )
    ) AS p

) AS x
ORDER BY
    invoice;

Returns

+----------+----------+-------+------+------------+-----------+-----------+
| position | quantity | price | summ |  invoice   | account 1 | account 2 |
+----------+----------+-------+------+------------+-----------+-----------+
| p1000001 |        2 |   100 |  200 | ty20210110 |         3 |         5 |
| p1000004 |        2 |   120 |  240 | ty20210110 |         3 |         5 |
| p1000005 |        4 |   100 |  400 | ty20210110 |         3 |         5 |
| p1000006 |        3 |   100 |  300 | ty20210110 |         3 |         5 |
| p1000002 |        3 |   100 |  300 | ty20210111 |         1 |         2 |
| p1000003 |        1 |   250 |  250 | ty20210111 |         1 |         2 |
| p1000007 |        5 |    80 |  400 | ty20210112 |         4 |         6 |
| p1000008 |        5 |   100 |  500 | ty20210112 |         4 |         6 |
+----------+----------+-------+------+------------+-----------+-----------+

SQL Select FOR XML PATH - Combine ordering and concatenate string with increasing counter

You just need to order the rows:

DECLARE @t TABLE (Code VARCHAR(255), Filename VARCHAR(255), ImageOrder INT)

INSERT INTO @t VALUES
('xxxx', 'Noimage.jpg', 0),
('xxxx', 'yyyy.jpg', 2),
('xxxx', 'zzzz.jpg', 1),
('xxxx', 'aaaa.jpg', 3)
 
SELECT REPLACE(
  (SELECT CAST('&p' + CAST(ImageOrder AS varchar) + '=' + Filename AS VARCHAR(MAX))
  FROM @t
  WHERE ImageOrder <> 0
  ORDER BY ImageOrder
  FOR XML PATH (''))
  , 'amp;', '') AS Txt

Which will give you the result:

&p1=zzzz.jpg&p2=yyyy.jpg&p3=aaaa.jpg

---

Related Topics