Make a query that show result in another column

Question

i have a table like this :

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'

query works good and return:

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

You have some answers based on your sample data. I wonder if nick and robert also are in the table with their own Id? Ie. the referredId is an id of someone else in the same table? You may have alternative answers if this is the case — Rob White, May 23 '17 at 13:48
there is one table with this fields: id - name - referred_id - referred_name — user334681, May 23 '17 at 14:08

score 1 · Answer 1 · answered May 23 '17 at 13:39

SQL Server stance. (There is possibly a more efficient way, but a way just the same)

SELECT  distinct
        referred_id,
        referred_name,

        STUFF((SELECT ', ' + data1.[name] 
        FROM tablename data1
        WHERE data1.referred_id = data.referred_id
        FOR XML PATH('')), 1, 1, '') [refers (name of refers)]


FROM    tablename data

rename your tables and aliases where applicable. Your id column in your example doesn't fit with your desired results.

courtesy of

https://www.mssqltips.com/sqlservertip/2914/rolling-up-multiple-rows-into-a-single-row-and-column-for-sql-server-data/

Arulkumar · Answer 2 · 2017-05-23T13:52:13.963

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;

Output:

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

Working Demo: http://rextester.com/ICC58106

Update:

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'

Sorry, can you write query to compatable with access ? thanks — user334681, May 23 '17 at 14:12

score 0 · Answer 3 · answered May 23 '17 at 13:54

SELECT
    referred_id
    ,referred_name
    ,(
        SELECT SUBSTRING((
            SELECT 
                ', ' + NAME
            FROM Act_Reg 
            WHERE REFERRED_ID = A.REFERRED_ID        
            FOR XML PATH('')),
        3,1000)
    ) [REFERS [NAME OF REFERES]
FROM Act_Reg A
-- INSERT WHERE CLAUSE HERE, IF NEEDED
GROUP BY referred_id, referred_name

score 0 · Answer 4 · answered May 23 '17 at 15:24

0

Access answer. Thanks to Combine values from related rows into a single concatenated string value

SQL:

SELECT distinct
i.[referred_name],
ConcatRelated(
    "[name]",
    "names",
"[referred_name] = '" & [referred_name] & "'") AS Names
FROM [names] AS i;

create a module and paste in ConcatRelated Method which is mentioned in the link provided. http://allenbrowne.com/func-concat.html

I've tested on access 2002-2003 and works.

answered May 23 '17 at 15:24

Rob White

950
1
6
16

Thanks Rob White, But i use access 2003 as database and use vb6.vb6 does not recognized "ConcatRelated",what should i do ? is there way to reach my goal with another query ? – user334681 May 24 '17 at 05:33
Everything I've looked into would suggest a query is not an option, but it's not like I've read every single page online so who knows. I would suggest pulling the data as it is and then use vb to build your desired results. Depending on the size of the data it might be slow and unworkable but that's for your discretion. – Rob White May 24 '17 at 14:12

Make a query that show result in another column

4 Answers4

Linked