Help SQL Squery

Question

Help SQL Squery

mxwebb

SSC Eights!

Points: 886
More actions
December 17, 2008 at 8:53 am

#199011

I have SQL Query I want to return one row with all the notes Concatenated.
SQL
Select
LC.Maint_Log_Contract_ID, LC.Maint_Log_Client_ID, LC.Cont_ID, LC.Cont_Type_ID, LC.Client_ID,
ND.EditDate, ND.UserID, ND.Note_Text
From _ut_PCA_Client_Admin_Maint_Log_Contract LC
LEFT JOIN _ut_PCA_Client_Admin_Maint_Log_Notes_Detail ND
on LC.Maint_Log_Contract_ID = ND.Maint_Log_Contract_ID
Group by LC.Maint_Log_Contract_ID, LC.Maint_Log_Client_ID, LC.Cont_ID, LC.Cont_Type_ID, LC.Client_ID,
ND.UserID, ND.Note_Text, ND.EditDate
having ND.Editdate = max(ND.EditDate)
Results
Maint_Log_Contract_ID,Maint_Log_Client_ID,Cont_ID,Cont_Type_ID,Client_ID,EditDate,UserID,Note_Text
310,8,550,1,2,12/16/2008 12:00:00 AM,211,"Test"
310,8,550,1,2,12/16/2008 1:11:30 PM,211,"Test 2"
310,8,550,1,2,12/16/2008 1:19:04 PM,211,"Test 3"

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

dva2007 SSCertifiable Points: 7707 More actions · Answer 1

U can write cursor (might not be the best solution) and concate the note_text into one big string separated by comma. It will have limitation of 8000 character though.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

mxwebb (12/17/2008)
I have SQL Query I want to return one row with all the notes Concatenated.

Writing a query to concatenate all of the notes in one row is simple - but your results list has three rows. Can you explain how you would like the data aggregated?

If you read the link at the bottom of this post, it will show you how to write scripts for creating the tables and some sample data which will help people to help you.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Plan A would be to do that client side... assuming you are not violating the rule to send back more data than needed.

dva2007 SSCertifiable Points: 7707 More actions · Answer 4

Please refer the following link.

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

regards,

vijay