Solved

Query syntax

Posted on 2016-09-14
7
33 Views
Last Modified: 2016-09-14
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
Comment
Question by:mcrmg
7 Comments
 
LVL 5

Expert Comment

by:D Patel
ID: 41797811
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
 
LVL 35

Expert Comment

by:YZlat
ID: 41797829
whuch databas edo you use? MS SQL? mySQL?
0
 

Author Comment

by:mcrmg
ID: 41797834
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 35

Expert Comment

by:YZlat
ID: 41797847
GROUP_CONCAT is for mySQL
0
 
LVL 35

Accepted Solution

by:
YZlat earned 450 total points
ID: 41797857
        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
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 50 total points
ID: 41797892
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
 

Author Closing Comment

by:mcrmg
ID: 41797945
thank you very much. Learned something new today
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now