SQL查询以选择特定的列值并将它们连接起来

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

能不能帮我实现这个查询:我需要携带所有的id，每个字母的值都是1:

3 个解决方案

#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:'

然后您需要将这个结果中的ID连接起来:'

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

示例SQL小提琴

#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

#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".

然后你需要在一个字段中填入多个id。您可以使用(伪装命名)“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.

我想那行得通。