1

I have to do an insert into a single column/row from a source of multiple values. I'm using the stuff case and I'm so close, but I'm missing one of the expected returns, so I'm not sure what I need to do to make sure I get the results I'm looking for.

HERE is my statement:

SELECT STUFF((SELECT CASE WHEN TotalAmount <=0 THEN 'C' ELSE 'D' END FROM Charges WHERE ChargeNumber=123 FOR XML Path('')), 1,1,'')

The results are D D D

But, I actually have four rows, so I should see D D D D

How can I make sure that I don't delete my first return, if that's what it is doing. WHen I try 0,1 it fails.

Any advice is greatly appreciated, thank you! (Running in SQL Server 2012)

SQL_Noob
  • 1,161
  • 1
  • 9
  • 18

2 Answers2

1

For your query you don't need STUFF(). Just do:

SELECT (CASE WHEN TotalAmount <= 0 THEN 'C' ELSE 'D' END)
FROM Charges
WHERE ChargeNumber = 123
FOR XML Path('');

STUFF() is only needed when you have a separator. It removes the separator at the beginning of the result string. Without a separator it removes the first value, which is why you are missing one of the 'D's.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

If you change the 2nd 1 to a 0 it works. For example,

DECLARE @Charges TABLE (ChargeNumber int, TotalAmount int)

INSERT INTO @Charges VALUES (123, 100), (123, 200), (123,100), (123, 300)

SELECT STUFF((SELECT CASE WHEN TotalAmount <=0 
                           THEN 'C' ELSE 'D' 
                     END 
               FROM @Charges 
              WHERE ChargeNumber = 123 FOR XML Path('')), 1,0,'')

Gives output:

DDDD
Zorkolot
  • 1,899
  • 1
  • 11
  • 8