alternative to stuff function in sql server

Question

i have table Tab1 like below( my actual table has upto 20,000 rows

a     b      c    statecode
-------------------------------------------
A1   abc   def    FL          
B1   abc   def    AZ
C1   abc   def    MI
D1   abc   def    CO
E1   abc   def    IL
F1   abc   def    CA

Here is what i am trying to do: for a given combination of columns b and c if there exists multiple rows then i want to concatenate a comma delimited "a - statecode".

i need a result like below...

a     b      c    statecode   output
-------------------------------------------
A1   abc   def      FL          A1-FL,B1-AZ,C1-MI,D1-CO,E1-IL,F1-CA

What i tried: I tried sql stuff function for this

SELECT a, b,c,
  STUFF((SELECT  distinct ', '+ t1.a +'-'+ t1.statecode
             FROM Tab1 t1
             where  t2.b = t1.a
             and    t2.c= t1.c 
             FOR XML PATH('')), 1, 1,'') AS output
from Tab1 t2;

This works fine for relatively small tables, my actual table has 20,000 rows and 15 columns, i have to check equality for a combination of 10 columns(like b,c in this example there are 8 more).

Is there a better way to do this in sql?or improve performance of stuff function?

Solution

The issue here isn't the stuff() function. It's the subquery and the XML processing.

There really isn't an alternative in SQL Server. I mean, there are two other possible ways to do aggregate string concatenation: write your own UDF or, if you only have a handful of values, use row_number() and string concatenation.

Before going down that route, try to optimize your query. The first thing to do is to create an index on tab1(a, c).

As a note: it is better to write this using an explicit XML tag. The syntax is very similar:

SELECT a, b,c,
  STUFF((SELECT  distinct ', '+ t1.a +'-'+ t1.statecode
             FROM Tab1 t1
             where  t2.b = t1.a and
                    t2.c= t1.c 
             FOR XML PATH('concat'), Type
         ).Value('/concat[1]', 'varchar(max)'), 1, 1,'') AS ACCEPTED_SYMBOLS
from Tab1 t2;

What this does is it create the string concatenation in XML and then converts it back from XML to a string. The benefit is that the characters &, <, and > are not turned into their XML equivalent (∓, for instance) and remain as what you expect.

Comments

[okkko]

.Value should be lowercase: .value