Merge the records using COALESCE in SQL

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Dear Friends,

I have table My_Table and columns - Date, Message
The Message can be any no. of Message in a Day.
Eg: Date Message
01-08-2012 MSG1
01-08-2012 MSG2
31-07-2012 MSG3
30-07-2012 MSG4
Now I was to display as for the Day : 01-08-2012 - MSG1, MSG2

I have tried like this

SQL

SELECT MSG=COALESCE(MAX(CASE WHEN GENT = 1 THEN Message END), '') + 
COALESCE(MAX(CASE WHEN GENT = 2 THEN Message END), '')  + 
COALESCE(MAX(CASE WHEN GENT = 3 THEN Message END), '') ,SDATE FROM (
SELECT TOP 10 GENT=RANK() OVER(PARTITION BY DATE ORDER BY DATE DESC),* FROM
My_Table WITH (NOLOCK) ORDER BY DATE DESC)O GROUP BY DATE ORDER BY DATE DESC

But this static and I ve mentioned as 3.
I want to make it as dynamic. Kindly guide me on this.

Posted 1-Aug-12 0:10am

Arunprasath Natarajan

Add a Solution

2 solutions

Solution 1

I don't think this can work. I think you'll find it easier to get the data, ordered by date, and then step through it to build the result set you want, in code. I am sure it can be done in SQL, but I suspect a code solution will be easier.

How does MAX work on a string ?

Posted 1-Aug-12 0:13am

Christian Graus

Comments

Arunprasath Natarajan 1-Aug-12 6:18am

It works fine. But i dont know how to merge it according to rows present in the table (Dynamic).

Christian Graus 1-Aug-12 6:30am

Really ? I don't see how Max is doing anything good in here. The only way I can see to do what you want, is to build a SQL string an execute it, I don't see any other way to get it to look up values you don't know at the time the proc is written. Of course, you can just write it up to a huge number and hope you never get that high ?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santhosh Kumar Jayaraman · Accepted Answer · 2012-08-01T00:22:00

Solution 2

Why cant you try this

SQL

Select distinct Date,
(
SELECT SUBSTRING(
(
SELECT '+' + T2.Message
FROM EMptest T2
where T2.Date=T1.Date
FOR XML PATH('')),2,200000) AS Message
) as Message
from EMptest as T1

Posted 1-Aug-12 0:22am

Santhosh Kumar Jayaraman

Comments

Arunprasath Natarajan 1-Aug-12 7:16am

Tan Q, Santhosh. Its works fine. If you dont mind can you explain how you have used this.

Santhosh Kumar Jayaraman 1-Aug-12 7:28am

The inner sub-query, concatenates a + in front of each additional result for the message obtained when the Date of the sub-query matches the distinct Date of the parent query. This gives us a string with +.

You can replace this + with Comma.

Then we are substring it from second character , so that we can exclude the leading + symbol.

Arunprasath Natarajan 1-Aug-12 8:10am

Yeah I understand that, but FOR XML PATH('') wat is the use of it.

Santhosh Kumar Jayaraman 1-Aug-12 8:22am

Its similar to COALESCE. XMLpath is a new builtin function came with SQL server 2005, It will concatenate values with comma separated or any symbols

Arunprasath Natarajan 1-Aug-12 9:46am

Tan q so much Santhosh...

Santhosh Kumar Jayaraman 1-Aug-12 9:46am

ya welcome