在SQL中使用COALESCE合并记录 [英] Merge the records using COALESCE in SQL

问题描述

亲爱的朋友，

我有表My_Table和列-日期，消息
消息可以是任何数字.一天中的消息之争.
例如:日期消息
2012年1月8日MSG1
2012年1月8日MSG2
2012年7月31日MSG3
30-07-2012 MSG4
现在我要显示的日期是:2012年1月8日-MSG1，MSG2

我已经尝试过了

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

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

但是这个静态的，我已经提到了3.
我想让它充满活力.请对此进行指导.

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

推荐答案

我认为这行不通.我认为您会发现，按日期排序获取数据更容易，然后逐步遍历以使用代码构建所需的结果集.我确信可以在SQL中完成，但是我怀疑代码解决方案会更容易.

MAX如何在字符串上工作?

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 ?

为什么不能尝试这个

Why cant you try this

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

这篇关于在SQL中使用COALESCE合并记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！