Stuff Function

Question

Stuff Function

jagjitsingh

Ten Centuries

Points: 1072
More actions
December 15, 2021 at 7:45 am

#3963767

Hi
How to use stuff function with 2 select statement . I want union all like condition .
Result to be stored in Code
Code=STUFF
(
(
SELECT ', '+ CAST(A0.[SCode] AS VARCHAR(MAX))
FROM tbl1 A0
INNER JOIN tbl2 A1 ON A0.[Id] = A1.[Id]
WHERE A1.docentry = A0.DocEntry
FOR XMl PATH('')
),1,1,''
)
Thanks

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247158 More actions · Answer 1

This probably is not going to give you the results you want. Instead, you should include a STUFF() for each individual SELECT.

jagjitsingh Ten Centuries Points: 1072 More actions · Answer 2

jagjitsingh

Ten Centuries

Points: 1072

December 15, 2021 at 8:59 am

#3963797

Hi Phil

Can u pls share some example

Thanks

Phil Parkin SSC Guru Points: 247158 More actions · Answer 3

jagjitsingh wrote:

Hi Phil
Can u pls share some example
Thanks

Given the limited amount of information you have provided, that is not easy. You mentioned using a 'UNION ALL' condition, but there is none in your code. What are you really trying to do, can you explain again, perhaps with some examples?

If you are trying to create a comma-delimited list of items returned from multiple sources, your best best is to UNION ALL of these items in a CTE (or add them to a temp table) and then use the FOR XML PATH hack on that.

jagjitsingh Ten Centuries Points: 1072 More actions · Answer 4

Hi Phil

In this example i have one select statement . I want to stuff on a condition.

Suppose if Field1 has value 'Y' then above select statement should be used

else another select statement

Thanks

Phil Parkin SSC Guru Points: 247158 More actions · Answer 5

jagjitsingh wrote:

Hi Phil
In this example i have one select statement . I want to stuff on a condition.
Suppose if Field1 has value 'Y' then above select statement should be used
else another select statement
Thanks

DECLARE @Field1 VARCHAR(20) = 'Y';
--Modify the above to get the value of field1 in your case

IF @Field1 = 'Y'
    SELECT 'True';
ELSE
    SELECT 'False';

jagjitsingh Ten Centuries Points: 1072 More actions · Answer 6

Hi Phil

if field1 = 'Y' then

Code=STUFF

(

SELECT ', '+ CAST(A0.[SCode] AS VARCHAR(MAX))

FROM tbl1 A0

INNER JOIN tbl2 A1 ON A0.[Id] = A1.[Id]

WHERE A1.docentry = A0.DocEntry

FOR XMl PATH('')

),1,1,''

)

else

Code=STUFF

(

SELECT ', '+ CAST(A0.[ACode] AS VARCHAR(MAX))

FROM tbl5 A0

INNER JOIN tbl2 A1 ON A0.[Id] = A1.[Id]

WHERE A1.docentry = A0.DocEntry

FOR XMl PATH('')

),1,1,''

)

Thanks

Stuff Function

Cookies on SQLServerCentral