SQL 连接表 [英] SQL Join Tables

问题描述

表一包含

ID|Name  
1  Mary  
2  John  

表二包含

ID|Color  
1  Red  
2  Blue  
2  Green  
2  Black  

我想结束的是

ID|Name|Red|Blue|Green|Black  
1  Mary Y   Y  
2  John     Y     Y     Y

感谢您的帮助.

感谢您的回复.我将重新发布这篇文章，并附上一些关于我正在尝试做的事情的额外信息，这可能会使事情变得复杂.有人可以关闭这个吗?

Thanks for the responses. I'm going to re-post this with some additional info about exactly what I'm trying to do that may complicate this. Can someone close this?

推荐答案

如果你使用 T-SQL 你可以使用 PIVOT (http://msdn.microsoft.com/en-us/library/ms177410.aspx)

If you use T-SQL you can use PIVOT (http://msdn.microsoft.com/en-us/library/ms177410.aspx)

这是我使用的查询:

declare @tbl_names table(id int, name varchar(100))
declare @tbl_colors table(id int, color varchar(100))

insert into @tbl_names
select 1, 'Mary'
union
select 2, 'John'


insert into @tbl_colors
select 1, 'Red'
union
select 1, 'Blue'
union
select 2, 'Green'
union
select 2, 'Blue'
union
select 2, 'Black'

select name,
        case when [Red] is not null then 'Y' else '' end as Red,
        case when [Blue] is not null then 'Y' else '' end as Blue,
        case when [Green] is not null then 'Y' else '' end as Green,
        case when [Black] is not null then 'Y' else '' end as Black

from
(
select n.id, name, color from @tbl_names n
inner join @tbl_colors c on n.id = c.id
) as subq
pivot 
(
    min(id)
    FOR color IN ([Red], [Blue], [Green], [Black])
) as pvt

这里是输出:

John        Y   Y   Y
Mary    Y   Y       

这篇关于SQL 连接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！