进行在另一列中显示结果的查询 [英] Make a query that show result in another column

问题描述

我有一张这样的桌子:

id - name - referred_id - referred_name
1  - mark -   100  -      nick
2  - david-   100  -      nick 
3  - mat -    100  -      nick
4  - patrik-  101  -      robert
5  - mick -   101  -      robert

我使用此查询来显示引用名称的结果:

i use this query to show result for referred name:

SELECT member_id,f_name,l_name,active_status,refered_by From Act_Reg where refered_by='nick'

查询运行良好并返回:

id - name
1  - mark
2  - david
3  - mat

现在，我想在网格中将结果显示为行和列:像这样

now, i want to show results in a grid as row and coloumn :like this

id - referred_id - referred_name - refers(name of refers)
1  -  100        - nick          - mark , david , mat
2  -  101        - robert        - patrik , mick

我的目标是任何介绍过作为报告的人，例如:尼克介绍了马克、大卫、垫子我使用访问作为数据库，谢谢大家

My goal is that anyone who has introduced as report,for example: nick introduced mark,david,mat I use a access as database, thanks all

推荐答案

既然你没有提到哪个 RDBMS，我正在为 SQL-Server 提供解决方案:

Since you are not mentioning about which RDBMS, I'm providing solution for the SQL-Server:

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Id, 
       referred_id, 
       referred_name, 
       STUFF((SELECT ', ' + CAST(name AS VARCHAR(10)) [n]
         FROM TestTable 
         WHERE referred_id = t.referred_id
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') name
FROM TestTable t
GROUP BY referred_id, referred_name;

输出:

Id  referred_id     referred_name   name
1   100             nick            mark, david, mat
2   101             robert          patrik, mick

工作演示:http://rextester.com/ICC58106

更新:

如果您想将结果发送给特定的referred_user，则需要在GROUP BY 之前添加WHERE 子句:

In case if you want to get the results to the specific referred_user then you need to add the WHERE clause before the GROUP BY:

WHERE referred_name = 'nick'

这篇关于进行在另一列中显示结果的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！