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.

Answer 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

Answer 2

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

Answer 3

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('')