How to create a concatenated string of row values based on flags in SQL Server

Question

I am using SQL Server 2008r2.

Here is what I am trying to accomplish:

I have a table with the design:

Flag  Text
________________________
0     'No Error'
1     'Bad Data'
2     'Bad Header'
4     'Unknown error'

My second table is designed:

ID   Flags
_______________________
500  0
501  3
502  4
504  6
550  0

The flags in the second table represent a bitwise combination of the flags in the first table (e.g. Flags = 3 is 'Bad Data' AND 'Bad Header', Flags = 6 is 'Bad Header' AND 'Unknown error').

I want a query that will produce the following:

ID   ConcatText
____________________________
500  'No Error'
501  'Bad Data, Bad Header'
502  'Unknown error'
504  'Bad Header, Unknown error'
550  'No Error'

What is the best way to achieve this without the use of user-defined functions, or user-defined stored procedures?

Thanks for any help.

Is there a reason you are using a bitwise operation instead of creating a true mapping table (501|1 and 501|2 instead of 501|3). This often creates a greater maintainability issue. Also, it can add unnecessary complexity as you are running into. — Justin Pihony, Mar 20 '12 at 04:30
Two reasons. First, it is what I was given to deal with, and second, I am curious how this would be best accomplished. Thanks. — Sako73, Mar 20 '12 at 04:39

Justin Pihony · Accepted Answer · 2012-03-20T04:54:00.830

This article explains exactly how to accomplish this. It puts it together step by step so that you understand what is going on, too. It basically combines the bitwise operators in SQL, and then the rest is accomplished similar to what hkf posted. Hopefully, this is helpful to you :)

I believe this will translate out to be:

SELECT a.id,
    REPLACE(REPLACE(REPLACE(
    ( 
        SELECT TEXT
        FROM FlagTable AS b 
        WHERE a.flags & b.flag <> 0 
        ORDER BY b.text FOR XML Raw
    )
    , '"/><row value="', ', '), '<row value="', ''), '"/>', '') 
    AS 'attributes'
FROM FlagMappingTable AS a
ORDER BY a.id;

score 0 · Answer 2 · edited May 23 '17 at 10:34

0

You need a combination of CROSS APPLY and FOR_XML_PATH()

See Simulating group_concat MySQL function in Microsoft SQL Server 2005?

edited May 23 '17 at 10:34

Community

1
1

answered Mar 20 '12 at 04:30

hkf

4,440
1
30
44

This will not solve the problem that the OP has due to using bitwise operators. My comment elaborates on this. If he can change the schema, then this solution will work better – Justin Pihony Mar 20 '12 at 04:31

score 0 · Answer 3 · edited May 23 '17 at 12:02

Oh I LOVE bitwise, truely, not sarcasm. I think this is the simplest. You've got CTE's available to you, I say use 'em! Try this. I borrowed from Concatenate many rows into a single text string? with my own flavor of a join for bitwise. *I apologize for mistakes, this is untested and written in Notepad.

WITH lines AS
( 
    SELECT
        row_number() over(order by ID) lineid,
        FlagMap.ID
        , Flag.Text AS ConcatText
    FROM
        FlagMap
    LEFT JOIN
        Flags
            ON FlagMap.Flags & Flags.Flag = Flags.Flag
                OR (FlagMap.Flags = 0 AND Flag.Flag = 0)
), 
result_lines AS
(
    SELECT
        lineid,
        cast(ConcatText as nvarchar(max)) ConcatText
    FROM
        lines
    WHERE
        lineid = 1

    UNION ALL

    SELECT
        l.lineid, 
        cast(r.ConcatText + N', ' + l.ConcatText AS nvarchar(max))
    FROM
      lines l 
    INNER JOIN
        result_lines r 
            on 
                l.lineid = r.lineid + 1 
) 
SELECT
    ID
    , ConcatText
FROM
    result_lines
ORDER BY
    ID DESC

How to create a concatenated string of row values based on flags in SQL Server

3 Answers3