Stuff Function

• jagjitsingh

  Ten Centuries

  Points: 1059

  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

• Phil Parkin

  SSC Guru

  Points: 246961

  More actions

  December 15, 2021 at 8:44 am

  #3963793

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

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• jagjitsingh

  Ten Centuries

  Points: 1059

  More actions

  December 15, 2021 at 8:59 am

  #3963797

  Hi Phil

  Can u pls share some example

  Thanks

• Phil Parkin

  SSC Guru

  Points: 246961

  More actions

  December 15, 2021 at 9:09 am

  #3963801

  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.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• jagjitsingh

  Ten Centuries

  Points: 1059

  More actions

  December 15, 2021 at 9:34 am

  #3963809

  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: 246961

  More actions

  December 15, 2021 at 9:56 am

  #3963815

  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';

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• jagjitsingh

  Ten Centuries

  Points: 1059

  More actions

  December 15, 2021 at 10:30 am

  #3963825

  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