SQL 多条记录合并成一条记录

[gaoxingstyle]

请求大神帮助。

[gaoxingstyle]

详细

[gaoxingstyle]

select u.Id,u.NameCN,pos.NameCN PostionName from [Workflow].[tbl_OrgNodeUserPosition] oup inner join [Workflow].[tbl_User] u on oup.UserId = u.Id inner join [Workflow].[tbl_Position] pos on oup.PositionId = pos.Id where OrgNodeId = 2061

[中国风]

OrgNodeId？那一表的列

[中国风]

 SELECT u.Id ,
        u.NameCN ,
        STUFF(t.PostionName,1,1,'') AS PostionName
 FROM   [Workflow].[tbl_User] u        
		CROSS APPLY(  
		SELECT ','+pos.NameCN  
		FROM [Workflow].[tbl_OrgNodeUserPosition] oup 
			INNER JOIN [Workflow].[tbl_Position] pos ON oup.PositionId = pos.Id
		WHERE  oup.UserId = u.Id
		FOR XML PATH('')
		) AS t(PostionName)
 WHERE  u.OrgNodeId = 2061;

[gaoxingstyle]

引用 4 楼 roy_88 的回复:

 SELECT u.Id ,
        u.NameCN ,
        STUFF(t.PostionName,1,1,'') AS PostionName
 FROM   [Workflow].[tbl_User] u        
		CROSS APPLY(  
		SELECT ','+pos.NameCN  
		FROM [Workflow].[tbl_OrgNodeUserPosition] oup 
			INNER JOIN [Workflow].[tbl_Position] pos ON oup.PositionId = pos.Id
		WHERE  oup.UserId = u.Id
		FOR XML PATH('')
		) AS t(PostionName)
 WHERE  u.OrgNodeId = 2061;

牛！不过OrgNodeId是这张表的tbl_OrgNodeUserPosition

[中国风]

这样加

 SELECT u.Id ,
        u.NameCN ,
        STUFF(t.PostionName,1,1,'') AS PostionName
 FROM   [Workflow].[tbl_User] u        
		CROSS APPLY(  
		SELECT ','+pos.NameCN  
		FROM [Workflow].[tbl_OrgNodeUserPosition] oup 
			INNER JOIN [Workflow].[tbl_Position] pos ON oup.PositionId = pos.Id
		WHERE  oup.UserId = u.Id AND oup.OrgNodeId = 2061
		FOR XML PATH('')
		) AS t(PostionName)
 WHERE  t.PostionName>''

[gaoxingstyle]

引用 6 楼 roy_88 的回复:

这样加

 SELECT u.Id ,
        u.NameCN ,
        STUFF(t.PostionName,1,1,'') AS PostionName
 FROM   [Workflow].[tbl_User] u        
		CROSS APPLY(  
		SELECT ','+pos.NameCN  
		FROM [Workflow].[tbl_OrgNodeUserPosition] oup 
			INNER JOIN [Workflow].[tbl_Position] pos ON oup.PositionId = pos.Id
		WHERE  oup.UserId = u.Id AND oup.OrgNodeId = 2061
		FOR XML PATH('')
		) AS t(PostionName)
 WHERE  t.PostionName>''

学习，

[gaoxingstyle]

引用 6 楼 roy_88 的回复:

这样加

 SELECT u.Id ,
        u.NameCN ,
        STUFF(t.PostionName,1,1,'') AS PostionName
 FROM   [Workflow].[tbl_User] u        
		CROSS APPLY(  
		SELECT ','+pos.NameCN  
		FROM [Workflow].[tbl_OrgNodeUserPosition] oup 
			INNER JOIN [Workflow].[tbl_Position] pos ON oup.PositionId = pos.Id
		WHERE  oup.UserId = u.Id AND oup.OrgNodeId = 2061
		FOR XML PATH('')
		) AS t(PostionName)
 WHERE  t.PostionName>''

再追问一句，如果还需要其它表里的OrgNodeId里的字段改怎么写，谢谢。

[中国风]

其它表也是合并，用以上CROSS APPLY方法就行了