Tutorial :Can I order the results of a concatenation in tsql?



Question:

SQL Server 2008 - I want to concatenate four columns into delimited values, but I want them to be ordered alphabetically. Is this possible?

**UPDATE:**More info... This will be used on approx 700k-1M rows per day in an ETL job via SSIS. If there is an easier way to do it within SSIS, please let me know (script task, etc). It could also be done within a Stored Proc.

Also keep in mind that these can be NULL - which is throwing some issues in with some of these solutions.


Solution:1

This requirement might indicate a problem with your design. If the values in the 4 columns are semantically equivalent you will likely find putting it into first normal form and refactoring the repeating columns out into a new table will make this sort of problem easier.

A monster CASE statement will probably be much more efficient but here's one way without

WITH t AS  (  SELECT 1 as rowid, 'cat' as C1, 'apple' As C2,         'bear' AS C3, 'fox' AS C4 UNION ALL  SELECT 2 as rowid, 'B' as C1, 'D' As C2, 'E' AS C3, 'G' AS C4   )    SELECT rowid,   STUFF((SELECT ',' + C FROM   (  SELECT C1 AS C FROM t t2 WHERE  t.rowId = t2.rowId  UNION ALL  SELECT C2 AS C FROM t t2 WHERE  t.rowId = t2.rowId  UNION ALL  SELECT C3 AS C FROM t t2 WHERE  t.rowId = t2.rowId  UNION ALL  SELECT C4 AS C FROM t t2 WHERE  t.rowId = t2.rowId  ) D  ORDER BY C          FOR XML PATH('')),1,1,'') X  

Gives

rowid       X  1           apple,bear,cat,fox  2           B,D,E,G  


Solution:2

Unpivot the columns into rows, then order the rows. Use whatever row string concatenation technique you favor, like FOR XML trick:

with cte as (  select *  from (values ('A' ,'C', 'B' ,'D')) as T (c1, c2, c3, c4))  select Value + ',' as [*]  from cte  unpivot (Value for c in (c1, c2, c3, c4)) as u  order by Value  for xml path('')  


Solution:3

Complex, but this will work:

   Select Case            When a>b And a>c And a>d Then a + ',' +               Case When b>c And b>d Then b + ',' +                        Case When c>d Then c Else d End                     When c>b And c>d Then c + ',' +                        Case When b>d Then b Else d End                     When d>b And d>c Then d + ',' +                        Case When b>c Then b Else c End End +           When b>a And b>c And b>d Then b + ',' +               Case When a>c And a>d Then a + ',' +                        Case When c>d Then c Else d End                     When c>a And c>d Then c + ',' +                        Case When a>d Then a Else d End                     When d>a And d>c Then a + ',' +                        Case When a>c Then a Else c End End + ',' +            etc...       End  

but I'd do this in code not in database...

EDIT: (as a computed column):

  Alter Table MyTable Add Column SortedABCD As          Case                When a>b And a>c And a>d Then a + ',' +                   Case When b>c And b>d Then b + ',' +                            Case When c>d Then c Else d End                         When c>b And c>d Then c + ',' +                            Case When b>d Then b Else d End                         When d>b And d>c Then d + ',' +                            Case When b>c Then b Else c End End +               When b>a And b>c And b>d Then b + ',' +                   Case When a>c And a>d Then a + ',' +                            Case When c>d Then c Else d End                         When c>a And c>d Then c + ',' +                            Case When a>d Then a Else d End                         When d>a And d>c Then a + ',' +                            Case When a>c Then a Else c End End + ',' +                etc...           End  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »