抓狂的问题,在线等。

[subxli]

table_A
ID NAME
1   joe
2   tom 
3   jack

table_B  --关联表
ID stu_id  pro_id
1     1     3

--现在想一行显示  joe jack

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj
-- Date    :2013-01-08 16:26:45
-- Version:
--      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 
--	Jun 17 2011 00:54:03 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据：[table_A]
if object_id('[table_A]') is not null drop table [table_A]
go 
create table [table_A]([ID] int,[NAME] varchar(4))
insert [table_A]
select 1,'joe' union all
select 2,'tom' union all
select 3,'jack'
--------------开始查询--------------------------

--select * from [table_A]
if object_id('[table_B]') is not null drop table [table_B]
go 
create table [table_B]([ID] int,[stu_id] int,[pro_id] int)
insert [table_B]
select 1,1,3
--------------开始查询--------------------------

select b.*,A.NAME,c.name from [table_B] b INNER JOIN [table_A] a ON a.id=b.stu_id
INNER JOIN [table_A] c ON b.pro_id=c.id

/*
ID          stu_id      pro_id      NAME name
----------- ----------- ----------- ---- ----
1           1           3           joe  jack

(1 行受影响)

*/

[subxli]

引用 1 楼 DBA_Huangzj 的回复:

SQL code?123456789101112131415161718192021222324252627282930313233343536373839------------------------------------------------------------------ Author :DBA_Huangzj-- Date :2013-01-08……

帅气！太帅气！

[subxli]

没理清楚关系，脑子太凌乱了。

[subxli]

引用 1 楼 DBA_Huangzj 的回复:

table_A
ID NAME
1   joe
2   tom 
3   jack
 
table_B  --关联表
ID stu_id  pro_id
1     1     2
1     1     3
 
--这种情况呢？现在想一行显示  joe tom jack

[發糞塗牆]

引用 4 楼 subxli 的回复:

引用 1 楼 DBA_Huangzj 的回复: SQL code ? 12345678910111213 table_A ID NAME1 joe 2 tom 3 jack table_B --关联表 ID stu_id pro_id 1 1 2 1 1 3 --这种情况呢？现在想一行显示 j……

刚才消防培训，下班，回去再看

[發糞塗牆]

刚准备走又“被加班”了，我特意加了几个数据，免得等下你又问，可能看的有点吃力，你自己消化一下吧

----------------------------------------------------------------
-- Author  :DBA_Huangzj
-- Date    :2013-01-08 16:26:45
-- Version:
--      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 
--    Jun 17 2011 00:54:03 
--    Copyright (c) Microsoft Corporation
--    Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据：[table_A]
if object_id('[table_A]') is not null drop table [table_A]
go 
create table [table_A]([ID] int,[NAME] varchar(4))
insert [table_A]
select 1,'joe' union all
select 2,'tom' union all
select 3,'jack'
 union all
select 4,'F'
--------------开始查询--------------------------
 
--select * from [table_A]
if object_id('[table_B]') is not null drop table [table_B]
go 
create table [table_B]([ID] int,[stu_id] int,[pro_id] int)
insert [table_B]
select 1,1,3
UNION ALL 
select 1,1,2
UNION ALL 
select 1,1,4
--------------开始查询--------------------------
 
 
 
select A.NAME NAME1,c.NAME  INTO #t
from [table_B] b INNER JOIN [table_A] a ON a.id=b.stu_id
INNER JOIN [table_A] c ON b.pro_id=c.id
 
 
 DECLARE @s NVARCHAR(4000)
 SET @s = ''
 SELECT @s = @s + ',' + QUOTENAME(name) + '=max(case when [name]='
        + QUOTENAME(name, '''') + ' then [name] else null  end)'
 FROM   #t
 EXEC('select [name1]'+@s+' from #t group by [name1]')
/*
name1 tom  jack F
----- ---- ---- ----
joe   tom  jack F
警告: 聚合或其他 SET 操作消除了 Null 值。

(1 行受影响)
 

 
*/