SQL STUFF函数汇总多行

问题描述：

我试图在同一天输入项目时合并一个字段。用户可以在同一天为给定的源多次输入消息。结果表是继 -SQL STUFF函数汇总多行

我想这样做的是使一个项目在同一天为SourceID结合MessageText。

我已经在哪里创建了SourceID和当天的记录，不过无论日期如何，它都会为该SourceID放置每个MessageText。它确实在同一天给出一行。例如，SourceID在2017年11月11日的2012-11-08上有2个条目。它为2012-11-08创建了一行，2017-07-11创建了一个行，但是它将所有3 MessageText排在同一行。

我的代码 -

SELECT distinct s.SourceID, stuff ((select ', ' + rtrim(x.MessageText) 
              from [AVData].[dbo].[LogCentralMessageData] x 
              inner join AVData.[dbo].[Source] a on a.SourceID = t.SourceID 
              inner join(select distinct max(m.CreatedOn)over (partition by r.SourceSiteID, Convert(date, m.CreatedOn)) as maxDate, r.SourceSiteID 
               from [AVData].[dbo].[LogCentralMessageData] m 
               left join AVData.[dbo].[Source] r on r.SourceID = m.SourceID 
               ) t on t.SourceSiteID = a.SourceSiteID and convert(date, t.maxDate) = Convert(date, x.CreatedOn) 

              where x.SourceID = a.SourceID 

              for XML path('')), 1, 1, '') message_text 
     ,convert(date, t.CreatedOn) as CreatedDate 


from [AVData].[dbo].[LogCentralMessageData] t 

left join AVData.[dbo].[Source] s on s.SourceID = t.SourceID 
order by SourceID, CreatedDate

答

下应该做的伎俩......

SELECT 
    st1.SourceID, 
    CAST(st1.CreatedOn AS DATE) 
    message_text = STUFF(
       (SELECT 
        CONCAT(', ', st2.MessageText) 
       FROM 
        dbo.SomeTable st2 
       WHERE 
        st1.SourceID = st2.SourceID 
        AND CAST(st1.CreatedOn AS DATE) = CAST(st2.CreatedOn AS DATE) 
       ORDER BY 
        st2.CtreatedOn 
       FOR XML PATH ('') 
       ), 1, 2, '') 
FROM 
    dbo.SomeTable st1 
GROUP BY 
    st1.SourceID, 
    CAST(st1.CreatedOn AS DATE);

由于某些原因，它无法识别以'st1.'开头的任何内容，然后我得到 - 关键字FOR –

附近的语法错误，它在CAST语句后缺少“，”。当我把这些陈述落实到位时，谢谢！ –

好的交易，很高兴它会为你工作。 –

答

SELECT SourceID, cast(CreatedOn as date) as CreatedDate, message_text = STUFF(
      (SELECT ',' + MessageText 
       FROM yourtable t1 
       WHERE t1.id = t2.id 
       FOR XML PATH ('')) 
      , 1, 1, '') from yourtable t2 
group by SourceID, cast(CreatedOn as date);

我收到了同样的结果，因为我之前做过。 –

SQL STUFF函数汇总多行

相关推荐