CODEWITHSUNDEEP
sqlsql-serverselectconcatenation
Ryan K.
Ryan K.

Reputation: 67

Can I select column values both separately and as one concatenation in the same query and avoid redundant code?

I have a scenario in which I need to query values for an SSRS report and display them each once in their separate state and once all concatenated together. To avoid making future changes to the SSRS report, I want the concatenation to be done in the query. However, my current query has quite a bit of redundant code, as I have repeated the operations I perform on each of the columns inside the CONCAT() function:

SELECT
    CASE
        WHEN a.TwoPartColumn LIKE '%-%'
        THEN LEFT(a.TwoPartColumn, CHARINDEX('-', a.TwoPartColumn) - 1)
        ELSE a.TwoPartColumn
    END AS FirstPart,
    CASE
        WHEN a.TwoPartColumn LIKE '%-%'
        THEN RIGHT(a.TwoPartColumn, LEN(a.TwoPartColumn) - CHARINDEX('-', a.TwoPartColumn))
        ELSE '001'
    END AS SecondPart,
    LEFT(rtrim(b.RightPadThis) + '0000000000', 10) AS RightPadded,
    RIGHT('00000000'+ rtrim(b.LeftPadThis,0), 8) AS LeftPadded,
    b.Units AS Units,
    CONCAT
    (
        CASE
            WHEN a.TwoPartColumn LIKE '%-%'
            THEN LEFT(a.TwoPartColumn, CHARINDEX('-', a.TwoPartColumn) - 1)
            ELSE a.TwoPartColumn
        END,
        CASE
            WHEN a.TwoPartColumn LIKE '%-%'
            THEN RIGHT(a.TwoPartColumn, LEN(a.TwoPartColumn) - CHARINDEX('-', a.TwoPartColumn))
            ELSE '001'
        END,
        LEFT(rtrim(b.ItemCd) + '0000000000', 10),
        RIGHT('00000000'+ rtrim(b.LeftPadThis,0))), 8),
        b.Units
    ) AS Concatenated
FROM TableA AS a
JOIN TableB AS b ON b.Id = a.Id
WHERE b.Param = @Param

Being a SQL novice, I tried passing the the columns' aliases into the CONCAT() function, to no avail.

While this query does achieve what I'm after, I want to know if I could clean it up or restructure it to avoid such redundant code.

Upvotes: 1

Views: 70

Answers (2)

user1443098
user1443098

Reputation: 7635

Using CROSS APPLY:

SELECT
    part.FirstPart,
    part.SecondPart,
    LEFT(rtrim(b.RightPadThis) + '0000000000', 10) AS RightPadded,
    RIGHT('00000000'+ rtrim(b.LeftPadThis,0), 8) AS LeftPadded,
    b.Units AS Units,
    CONCAT
    (
        part.FirstPart,part.SecondPart,
        LEFT(rtrim(b.ItemCd) + '0000000000', 10),
        RIGHT('00000000'+ rtrim(b.LeftPadThis,0))), 8),
        b.Units
    ) AS Concatenated
FROM TableA AS a
JOIN TableB AS b ON b.Id = a.Id
CROSS APPLY (
SELECT CASE
        WHEN a.TwoPartColumn LIKE '%-%'
        THEN LEFT(a.TwoPartColumn, CHARINDEX('-', a.TwoPartColumn) - 1)
        ELSE a.TwoPartColumn
    END AS FirstPart,
    CASE
        WHEN a.TwoPartColumn LIKE '%-%'
        THEN RIGHT(a.TwoPartColumn, LEN(a.TwoPartColumn) - CHARINDEX('-', a.TwoPartColumn))
        ELSE '001'
    END AS SecondPart) part
)
WHERE b.Param = @Param

You can use the same technique for the other repeated expressions

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33571

Using a CTE is the most simple way to deal with this kind of thing. Something like this.

with MyData as
(
    SELECT
        CASE
            WHEN a.TwoPartColumn LIKE '%-%'
            THEN LEFT(a.TwoPartColumn, CHARINDEX('-', a.TwoPartColumn) - 1)
            ELSE a.TwoPartColumn
        END AS FirstPart,
        CASE
            WHEN a.TwoPartColumn LIKE '%-%'
            THEN RIGHT(a.TwoPartColumn, LEN(a.TwoPartColumn) - CHARINDEX('-', a.TwoPartColumn))
            ELSE '001'
        END AS SecondPart,
        LEFT(rtrim(b.RightPadThis) + '0000000000', 10) AS RightPadded,
        RIGHT('00000000'+ rtrim(b.LeftPadThis,0), 8) AS LeftPadded,
        b.Units AS Units
    FROM TableA AS a
    JOIN TableB AS b ON b.Id = a.Id
    WHERE b.Param = @Param
)
select FirstPart
    , SecondPart
    , RightPadded
    , LeftPadded
    , Units
    , concat(FirstPart, SecondPart, RightPadded, LeftPadded, Units)
from MyData

Upvotes: 2

Related Questions