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.

Answer 1

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: