CODEWITHSUNDEEP
sqlsql-serverunicodestring-concatenation
B.C.
B.C.

Reputation: 23

How to concatenate unicode strings in SQL Server?

I have a unique situation where I need to concatenate rows based on 2 groups. The other trick is that the concatenation must be able to display bullets and add a line feed. This is what my business is requesting. I should also add the final destination will be Excel. I’ve tested exporting to Excel with SSIS using data that has bullets and line feeds and this piece does work.

How to go from:

pk   orgnl_pk   type   text
1    1          one    • Line One
2    1          one    • Line Two
3    1          one    • Line Three
4    1          two    • Line One
7    3          one    • Line One
8    3          two    • Line One
9    3          two    • Line Two

To:

orgnl_pk    type    text
1           one     • Line One
                    • Line Two
                    • Line Three
1           two     • Line One
3           one     • Line One
3           two     • Line One
                    • Line Two

Upvotes: 2

Views: 1604

Answers (1)

SqlZim
SqlZim

Reputation: 38023

As Lamak pointed out, this is best left to the presentation layer, but if you must do it in sql for now... then this uses the stuff() with select ... for xml path ('') method of string concatenation. 

select 
    orgnl_pk
  , [type]
  , [text]=stuff(
        (
        select char(10) +i.[text]
          from t as i
          where i.orgnl_pk = t.orgnl_pk
            and i.[type]=t.[type]
          order by i.pk 
        for xml path (''), type).value('.','nvarchar(max)')
        ,1,0,'')
from t
group by orgnl_pk, [type]

rextester demo: http://rextester.com/GPFIMO37322

returns:

+----------+------+--------------+
| orgnl_pk | type |     text     |
+----------+------+--------------+
|        1 | one  | • Line One   |
|          |      | • Line Two   |
|          |      | • Line Three |
|        1 | two  | • Line One   |
|        3 | one  | • Line One   |
|        3 | two  | • Line One   |
|          |      | • Line Two   |
+----------+------+--------------+

Update for character (0x0007) :

select 
    orgnl_pk
  , [type]
  , [text]=replace(stuff(
        (
        select char(10)+replace(i.[text],nchar(0x0007),'$BEL$')
          from t as i
          where i.orgnl_pk = t.orgnl_pk
            and i.[type]=t.[type]
          order by i.pk 
        for xml path (''), type).value('.','nvarchar(max)')
        ,1,1,''),'$BEL$',nchar(0x0007))
from t
group by orgnl_pk, [type]

Upvotes: 2

Related Questions