SQL Query to Select specific column values and Concatenate them

Question

Can someone please help me to achieve this query: I need to carry all the IDs for each letter that has the value 1:

Answer 1

This is a two step process. First you need to unpivot your columns to rows:

SELECT  upvt.ID, Letters
FROM    T
        UNPIVOT
        (   Value
            FOR Letters IN ([A], [B], [C], [D], [E], [F])
        ) upvt
WHERE   upvt.Value = 1;

This gives:

ID  Letters
10  A
10  C
10  E
10  F
...

Then you need to Concatenate the ID's From this result:'

WITH Unpivoted AS
(   SELECT  upvt.ID, Letters
    FROM    T
            UNPIVOT
            (   Value
                FOR Letters IN ([A], [B], [C], [D], [E], [F])
            ) upvt
    WHERE   upvt.Value = 1
)
SELECT  u.Letters,
        IDs = STUFF((   SELECT  ', ' + CAST(u2.ID AS VARCHAR(10))
                        FROM    Unpivoted u2
                        WHERE   u.Letters = u2.Letters
                        FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
FROM    Unpivoted u
GROUP BY u.Letters;

Which gives:

Letters IDs
A       10, 20, 50
B       20, 40
C       10, 20, 30, 40, 50
D       30, 40
E       10, 50
F       10, 20, 40

Example on SQL Fiddle

Answer 2

select distinct 'A',
(select cast(id as varchar)+',' from letters where a=1 for xml path('')) ids
 from letters where a=1
union all 
select distinct 'B',
(select cast(id as varchar)+',' from letters where b=1 for xml path('')) ids
from letters where b=1
union all
select distinct 'C',
(select cast(id as varchar)+',' from letters where c=1 for xml path('')) ids
 from letters where c=1
union all 
select distinct 'D',
(select cast(id as varchar)+',' from letters where d=1 for xml path('')) ids
from letters where D=1
union all
select distinct 'E',
(select cast(id as varchar)+',' from letters where e=1 for xml path('')) ids
 from letters where e=1
union all 
select distinct 'F',
(select cast(id as varchar)+',' from letters where f=1 for xml path('')) ids
from letters where f=1

Answer 3

There are two problems here: First, the table is not normalized, so you really need to first do an extra step to create a temporary table that normalizes the data:

The first step:

select id, 'A' as letter
from mytable where a=1
union
select id, 'B'
from mytable where b=1
union
select id, 'C'
from mytable where c=1
union
select id, 'D'
from mytable where d=1
union
select id, 'E'
from mytable where e=1
union
select id, 'F'
from mytable where f=1

Then you need to get multiple IDs crammed into one field. You can do this with the (deceptively named) "For XML".

Something like:

select letter, id + ', ' as [text()] 
from 
(    
select id, 'A' as letter
from mytable where a=1
union
select id, 'B'
from mytable where b=1
union
select id, 'C'
from mytable where c=1
union
select id, 'D'
from mytable where d=1
union
select id, 'E'
from mytable where e=1
union
select id, 'F'
from mytable where f=1
) q
group by letter
for XML path(''))

I think that would work.