CODEWITHSUNDEEP
sql-serverxml
SQL_Noob
SQL_Noob

Reputation: 1291

SQL STUFF and XML Path - Can't get space between results

SQL Server 2012

Goal: Combine multiple money rows into one string with a space between.

I have tried a variety of edits and modifications and I can't seem to get this correct.

Here is the data I'm looking at:

Table1.[Misc Amount]
Table2.Taxes

My column name has a space which really made the xml path angry I guess.

Statement used:

DECLARE @Misc nvarchar(30)
SET @Misc = (SELECT [Misc Amount] FROM Table1 WHERE Order=33532) 

SELECT @Misc + ' '+ STUFF((SELECT ' ' + SUM(TaxAmount ) AS [text()]
FROM Table2 
WHERE Order=33532 
GROUP BY Code FOR XML Path('')), 1,0,'')

I also tried as string value, [node()], playing with the positions like 1,2, etc.

My resulting data is:

10.00 0.006.084.50

What I want is

10.00 0.00 6.08 4.50

I'd love to exclude the 0.00 but at the moment I'd be happy with just my spaces.... I really hate stuff statements. Any of you kind gurus have a way to help me?

Upvotes: 2

Views: 1284

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

You've got a solution already, that's fine, but I think the following might be more succinct and it is fully set-based and inlinable:

(Borrowed the DDL from Chris Albert's solution, thx for that!)

CREATE TABLE #MiscAmount ([Order] int, [Misc Amount] money)
CREATE TABLE #Taxes ([Order] int, Code int, TaxAmount money)

INSERT INTO #MiscAmount ([Order], [Misc Amount]) VALUES 
 (33532, 10.00)
,(1111, 11.00);

INSERT INTO #Taxes ([Order], Code, TaxAmount) VALUES
 (33532, 1, 0.00)
,(33532, 2, 6.08)
,(33532, 2, 1.00) --2 of Code=2 (as you are using `GROUP BY Code`)
,(33532, 3, 4.50)
,(1111, 1, 1.11);

SELECT m.[Misc Amount]
     ,STUFF(
     (
        SELECT ' ' + CAST(SUM(t.TaxAmount) AS VARCHAR(100))
        FROM #Taxes AS t
        WHERE t.[Order]=m.[Order]
        GROUP BY t.Code
        FOR XML PATH('')
      ),1,1,'') AS CombinedTaxAmount
FROM #MiscAmount AS m


GO
DROP TABLE #MiscAmount
DROP TABLE #Taxes

The result

Misc Amount CombinedTaxAmount
10,00       0.00 7.08 4.50
11,00       1.11

Upvotes: 3

Chris Albert
Chris Albert

Reputation: 2507

Your TaxAmount column is probably of a numerical data type. Therefore your string with the space in it is being omitted. Cast it to a string data type and your in business.

CREATE TABLE #MiscAmount ([Order] int, [Misc Amount] money)
CREATE TABLE #Taxes ([Order] int, Code int, TaxAmount money)

INSERT INTO #MiscAmount ([Order], [Misc Amount]) VALUES (33532, 10.00)
INSERT INTO #Taxes ([Order], Code, TaxAmount) VALUES
(33532, 1, 0.00),
(33532, 2, 6.08),
(33532, 3, 4.50)

DECLARE @Misc nvarchar(30)
SET @Misc = (SELECT [Misc Amount] FROM #MiscAmount WHERE [Order] = 33532) 

SELECT @Misc + ' '+ STUFF((SELECT ' ' + CAST(SUM(TaxAmount) AS varchar(50)) AS [text()]
FROM #Taxes
WHERE [Order] = 33532 
GROUP BY Code FOR XML Path('')), 1,0,'') 

DROP TABLE #MiscAmount
DROP TABLE #Taxes

Upvotes: 2

Related Questions