Need a simple T-SQL Query

Question

I have the following table:

PrimaryKeyColumn1 |PrimaryKeyColumn2 |   Column_A | Column_B | Column_C|
ID1                     Key1                 0           1         0
ID2                     Key2                 1           0         1      
ID3                     Key3                 1           1         0      

Basically, I need to figure out a T-SQL query to get the results like this:

ID1 Key1 B
ID2 Key2 A,C
ID3 Key3 A,B

Can someone please help me figure this out? The name of the columns include the letter (A,b,c...z) at the end of the column name after a underscore. The 0 or 1 indicates if that letter applies to the ID or not. If 0 it means not and if 1 it means yes. so, it needs to be next to the id in the results if it is 1 otherwise not needed if it is 0.

Thank you!

Answer 1

I think the easiest way is just to use CASE and some string logic:

select PrimaryKeyColumn1, PrimaryKeyColumn2,
       stuff(((case when column_a = 1 then ',A' else '' end) +
              (case when column_b = 1 then ',B' else '' end) +
              (case when column_c = 1 then ',C' else '' end)
             ), 1, 1, '') as vals
from . . .

Answer 2

i've done something like this

    CREATE TABLE #test
    (
        primaryKey nvarchar(150)
        , columnA bit
        , columnB bit
        , columnC bit
    ) -- create some test table

    INSERT INTO #test
    VALUES 
    ('ID1',1,0,0)
    ,('ID2',1,1,0)
    ,('ID3',1,0,1) -- as well as the test values

    SELECT
        *
    into #tmpUnpiv
    FROM #test
    UNPIVOT
    (
        truefalse
        for columnName in (columnA, columnB, columnC)
    ) unpiv; -- save the un-pivoted result as new table #tmpUnpiv

    SELECT
        DISTINCT
            primaryKey + ' ' + (SELECT replace(columnName,'column','') + ',' AS [text()]  from #tmpUnPiv WHERE primaryKey = p.primaryKey AND truefalse = 1 FOR XML PATH(''))
    FROM #tmpUnPiv p -- then combine results through XML PATH

    DROP TABLE #tmpUnPiv
    DROP TABLE #test
    -- drop temp tables