Recursively concat columns in sql

Question

I have a table containing values as follows

╔═══╦════╦════╦══════╦══════╗
║ b ║ l1 ║ l2 ║  l3  ║  l4  ║
╠═══╬════╬════╬══════╬══════╣
║ a ║ b1 ║ c1 ║  d1  ║  e1  ║
║ d ║ x1 ║ y1 ║ null ║ null ║
╚═══╩════╩════╩══════╩══════╝

The output should be:

╔═══════════╗
║ ab1c1d1e1 ║
║ ab1c1d1   ║
║ ab1c1     ║
║ ab1       ║
║ dx1y1     ║
║ dx1       ║
╚═══════════╝

Is it possible? I see a pattern here but able to figure it out how to do it. P.S: ROLLUP can't be used as the server doesn't support it.

Felix Pamittan · Accepted Answer · 2015-10-16T08:42:40.233

6

Using UNION ALL:

SELECT * FROM(
    SELECT  b + l1 + l2 + l3 + l4 FROM tbl UNION ALL
    SELECT  b + l1 + l2 + l3 FROM tbl UNION ALL
    SELECT  b + l1 + l2 FROM tbl UNION ALL
    SELECT  b + l1 FROM tbl
) AS t(a)
WHERE a IS NOT NULL

Execution plan:

Here is another way to UNPIVOT, this will scan the table only once:

SELECT x.a
FROM tbl t
CROSS APPLY(VALUES
    (b + l1 + l2 + l3 + l4),
    (b + l1 + l2 + l3),
    (b + l1 + l2),
    (b + l1)
) AS x(a)
WHERE a IS NOT NULL

Execution plan:

edited Oct 16 '15 at 08:42

answered Oct 16 '15 at 08:28

Felix Pamittan

31,544
7
41
67

Out of curiosity, is there a way of doing this without hard-coding the columns and rows to `SELECT` (I starred this question, by the way). – Tim Biegeleisen Oct 16 '15 at 08:29
1

It scans the table everytime the select is done. And since its an xml in the back. Its going to be expensive. I need to get the output in say 3 to 5 ms to the max. – techno Oct 16 '15 at 08:30
@TimBiegeleisen, dynamic SQL maybe. – Felix Pamittan Oct 16 '15 at 08:32
@TejalKarande, I don't think there is a faster way. The other solution will involve having to `UNPIVOT` each row and then `JOIN` which I believe would be more expensive. I'll leave it for you to test. – Felix Pamittan Oct 16 '15 at 08:35
@TejalKarande, see my `CROSS APPLY` version. I believe it'll be faster than the `UNION ALL` – Felix Pamittan Oct 16 '15 at 08:43
I have tried UNPIVOT too. but its taking 30 to 100ms depending on the load on the server. So thats out. – techno Oct 16 '15 at 08:46
1

Well, the display itself is gonna take time. Still, I believe the `CROSS APPLY` would be the fastest method – Felix Pamittan Oct 16 '15 at 08:55
Till I dont get another alternative, will use CROSS APPLY . Thanks :) – techno Oct 16 '15 at 09:06
hi there! going for the union all solution, reason : the (estimated) number of rows returned by the cross apply query are too much thus Sql server decides to bring almost all the rows from the master table with which I am joining this table, whereas the previous query enables it to estimate the #rows of the xml. – techno Nov 30 '15 at 08:31

Shnugo · Answer 2 · 2015-10-16T09:20:41.007

0

EDIT: This enhanced solution delivers the data as naked strings. If this is fast - I don't know. Please let me know...

DECLARE @tbl TABLE( b varchar(100),l1 varchar(100),l2 varchar(100),l3 varchar(100), l4 varchar(100));
INSERT INTO @tbl VALUES
 ('a','b1','c1','d1','e1')
,('d','x1','y1',null,null);


SELECT u.v.value('.','varchar(max)')
FROM
(
    SELECT   '' + b + l1 + l2 + l3 + l4 + ';' AS x
            ,'' + b + l1 + l2 + l3 + ';'      AS x
            ,'' + b + l1 + l2 + ';'           AS x
            ,'' + b + l1 + ';'                AS x
    FROM @tbl
    FOR XML PATH(''),TYPE
) AS XMLList(x)
CROSS APPLY XMLList.x.nodes('/x') AS a(b) 
CROSS APPLY(SELECT CAST('<r>' + REPLACE(a.b.value('.','varchar(max)'),';','</r><r>') + '</r>' AS XML)) AS m(n)
CROSS APPLY m.n.nodes('/r') AS u(v)
WHERE LEN(u.v.value('.','varchar(max)'))> 0

/* Result

ab1c1d1e1
ab1c1d1
ab1c1
ab1
dx1y1
dx1

*/

edited Oct 16 '15 at 09:20

answered Oct 16 '15 at 09:06

Shnugo

66,100
9
53
114

Hey if I am inserting the xml in a table the table creation itself takes time so thats not an option. Thanks :) – techno Oct 16 '15 at 09:09
@TejalKarande, you find an enhanced variation in my answer. The result is no XML anymore, but - to be honest - I do not expect this to be faster than Felix' answer. I'd just be curious if this works... – Shnugo Oct 16 '15 at 09:22
Hey.. when I am comparing it with cross apply, your query is slower.. Thanks :) – techno Oct 16 '15 at 11:50

Recursively concat columns in sql

2 Answers2