Help SQL Squery

Question

Post reply

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

dva2007

SSCertifiable

Points: 7707

December 17, 2008 at 9:46 am

#913979

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