Make A Query That Show Result In Another Column

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

Solution 1:

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

SELECTdistinct
        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/

Solution 2:

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

SELECTROW_NUMBER() OVER(ORDERBY (SELECTNULL)) AS Id, 
       referred_id, 
       referred_name, 
       STUFF((SELECT', '+CAST(name ASVARCHAR(10)) [n]
         FROM TestTable 
         WHERE referred_id = t.referred_id
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') name
FROM TestTable t
GROUPBY referred_id, referred_name;

Output:

Id  referred_id     referred_name   name
1100             nick            mark, david, mat
2101             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:

WHEREreferred_name='nick'

Solution 3:

SELECT
    referred_id
    ,referred_name
    ,(
        SELECTSUBSTRING((
            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 NEEDEDGROUPBY referred_id, referred_name

Solution 4:

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

SQL:

SELECTdistinct
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.