Concatenate Fields in Order - SQL Server

Question

I have a table consisting of 5 integer ids, and would like to add a column which takes these ids, orders them and concatenates them in some fashion similar to below.

id1       id2       id3      id4       id5      new_col
364       53        468      184       469      /53/184/364/468/469/
48        47        49       364       266      /47/48/49/266/364/

Is there a function which will make performing the ordering quicker and easier? God forbid if I have to code up the ordering by hand.

If you're storing the same "type" of data in multiple different columns , you're already missing the point of a relational database. If the data is all identical, it ought to be stored as multiple rows (and a single column). If the ordering of these columns is important (the 1-5 values), it ought to be modelled as data rather than metadata. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 9, 2016 at 19:02
Is there an actual identifier on the row? If so, an unpivot solution is going to be a whole lot easier… — Ben Thul
– Ben Thul, Commented Jul 9, 2016 at 22:04
@BenThul I doubt it will be simpler. What code do you have in mind? — Martin Smith
– Martin Smith, Commented Jul 9, 2016 at 23:35
I like your unpivot solution better than what I came up with. Still not a fan of this data model, though. :) — Ben Thul
– Ben Thul, Commented Jul 10, 2016 at 0:47

Martin Smith · Accepted Answer · 2016-07-09 19:24:29Z

4

You could also use XML PATH (Online Demo)

SELECT id1,
       id2,
       id3,
       id4,
       id5,
       new_col = CONCAT('/', ids)
FROM   YourTable
       CROSS APPLY (SELECT CONCAT(id, '/')
                    FROM   (VALUES (id1),
                                   (id2),
                                   (id3),
                                   (id4),
                                   (id5)) V(id)
                    ORDER  BY id
                    FOR XML PATH('')) C(ids)

edited Jul 9, 2016 at 19:24

answered Jul 9, 2016 at 19:10

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

IVNSTN Over a year ago

Such a nice pseudo uppivot! Available since mssql2008, but this is the first time I see this trick. Feeling again like if I know nothing)) Thanks!

Martin Smith Over a year ago

@IvanStarostin on 2005 you can do the same thing with a derived table and UNION ALL btw. Like this stackoverflow.com/a/4308905/73226

Gordon Linoff · Accepted Answer · 2016-07-09 19:01:32Z

3

This is a real pain in SQL Server. Here is one method:

select t.*, v.ids
from t outer apply
     (select ('/' + max(case when seqnum = 1 then id end) +
              '/' + max(case when seqnum = 2 then id end) +
              '/' + max(case when seqnum = 3 then id end) +
              '/' + max(case when seqnum = 4 then id end) +
              '/' + max(case when seqnum = 5 then id end) +
              '/') as ids
      from (select id, row_number() over (order by id) as seqnum
            from (values(id1), (id2), (id3), (id4), (id5)) v(id)
           ) v
     ) v;

answered Jul 9, 2016 at 19:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

IVNSTN · Accepted Answer · 2016-07-09 20:05:43Z

I hope there is some id column already in real table.

declare @data table (c1 int, c2 int, c3 int, c4 int, c5 int)

insert into @data (c1, c2, c3, c4, c5)
values
(364, 53, 468, 184, 469),
(48, 47, 49, 364, 266)


;with NumberedRows as
(
  select
    d.c1, d.c2, d.c3, d.c4, d.c5, 
    row_number() over(order by (select 1)) rn
  from @data d
)
select
  rn, r.c1, r.c2, r.c3, r.c4, r.c5,
  stuff(
    (
      select concat('/', p.num)
      from 
      (
        select rr.c1, rr.c2, rr.c3, rr.c4, rr.c5
        from NumberedRows rr
        where rr.rn = r.rn
      ) rr
      unpivot (num for cols in (c1, c2, c3, c4, c5)) p
      order by p.num
      for xml path(''), type
    ).value('.', 'varchar(max)')
  , 1, 1, '') value_list
from NumberedRows r
order by r.rn

And look how over-complicated it looks against that VALUES (), () trick by @Martin and @Gordon. Yep.

Collectives™ on Stack Overflow

Concatenate Fields in Order - SQL Server

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related