Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 57
  • Last Modified:

Query syntax

Hi,

I have two tables,

Table A has users' info, name, address.etc
Table B has notes that created by users in Table A, each user can create multiple notes.

Table A
userid
username
firstname
lastname
address

Table B
noteid
userid
note
date

I need to create an Excel report that contain all users and their notes. Since each user can create multiple notes, I want to combine individual user's notes into one cell.  Is there a way to do this in SQL stored procedure?  thanks
0
mcrmg
Asked:
mcrmg
2 Solutions
 
D PatelD Patel, Software EngineerCommented:
Use This syntax:

select
  tableA.userid as `Id`,
  tableA.username as `User`,
  GROUP_CONCAT(tableB.note) as `Notes`
from
  tableA,
  tableB
where
  tableA.userid=tableB.userid
group by
  tableA.userid
0
 
YZlatCommented:
whuch databas edo you use? MS SQL? mySQL?
0
 
mcrmgAuthor Commented:
thanks for the quick reply, I am getting

'GROUP_CONCAT' is not a recognized built-in function name.


I have SQL 2008, too old?  thanks
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
YZlatCommented:
GROUP_CONCAT is for mySQL
0
 
YZlatCommented:
        SELECT DISTINCT a.userid, notes = STUFF(( SELECT  ',' + note
	FROM TableB as b
	WHERE b.userid=a.userid	
	ORDER BY note
	FOR XML	PATH('')), 1, 1, '')
FROM TableA as a

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
YZlat's solution of XML PATH is correct.   For a demo of the XML path method of making comma-separated values as a single value check out T-SQL:  Normalized data to a single comma delineated string and back
0
 
mcrmgAuthor Commented:
thank you very much. Learned something new today
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now