Make a query showing the 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 - robert
5 - mick - 101 - robert
I am using this query to show the result for the specified name:
SELECT member_id,f_name,l_name,active_status,refered_by From Act_Reg where refered_by='nick'
the request works well and returns:
id - name
1 - mark
2 - david
3 - mat
now, i want to show results in grid as rows 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 submitted as a report, for example: nickname, entered sign, david, checkmate I use access as a database, thanks everyone
source to share
SQL Server position. (Possibly more efficient way, but the way is 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 match your desired results.
courtesy of
source to share
Since you don't mention which RDBMS are, I provide a solution for 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:
If you want results for a specific refer_user, you need to add a clause WHERE
before GROUP BY
:
WHERE referred_name = 'nick'
source to share
Access to answers. Thanks to Combine values from linked strings into one 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 into the ConcatRelated Method which is listed in the link provided. http://allenbrowne.com/func-concat.html
I tested access in 2002-2003 and it worked.
source to share