Geek Answers Handbook

SQL concatenates a row of data by level

I have a table like this

level|value
1    |ABC
1    |XYZ
1    |QWER
2    |1234
2    |7360
3    |zxcv
3    |0001

How can I append each value at level 1 to a level below? How:

ABC-1234-zxcv
ABC-1234-0001
ABC-7360-zxcv

...

+1
source share
2 answers

The number of levels is not fixed:

Declare @select varchar(max) = 'SELECT ',
        @from varchar(max) = 'FROM ',
        @where varchar(max) = 'WHERE ',
        @query varchar(max)= '';

SELECT  @select = @select + 't' + cast([level] as varchar(max)) + '.[value]+''-''+',
        @from = @from + 'yourTable t' + cast([level] as varchar(max)) + ',',
        @where = @where + 't' + cast([level] as varchar(max)) + '.[level] = ' + cast([level] as varchar(max)) + ' AND '
FROM yourTable 
GROUP BY [level]

Set @query  = SUBSTRING(@select, 1, len(@select) - 5) + ' ' + 
        SUBSTRING(@from, 1, len(@from) - 1) + ' ' +
        SUBSTRING(@where, 1, len(@where) - 4) + ' ORDER BY 1'

EXEC(@query)
+2
source

If you always have 3 levels, you can do it as follows:

select
  d1.value + '-' + d2.value + '-' +  d3.value
from
  data d1 
  cross join data d2
  cross join data d3
where
  d1.level = 1 and
  d2.level = 2 and
  d3.level = 3
order by
  1

If the number of levels is not set, you may have to use a recursive CTE

+2
source

More articles:


All Articles