根据日期时间为每个唯一值组合行

Question

我有一张三栏的桌子

TICKET_ID   ASSIGN  ASSIGN_DATE
5692    ASSIGN-5    2013-07-17 19:37:09.000
5740    ASSIGN-5    2013-07-17 19:37:09.000
5741    ASSIGN-5    2013-07-17 19:37:09.000
5742    ASSIGN-5    2013-07-17 10:40:15.000
5742    ASSIGN-4    2013-07-17 19:37:09.000

我需要根据日期时间提升结果将ASSIGN行组合为每个TICKET_ID的一行，如下所示

TICKET_ID   ASSIGN  
5692    ASSIGN-5    
5740    ASSIGN-5    
5741    ASSIGN-5    
5742    ASSIGN-4 ASSIGN-5   

我怎样才能建立结果？

Answer 1

您可以使用FOR XML PATH('')

WITH SampleData(TICKET_ID, ASSIGN, ASSIGN_DATE) AS(
    SELECT 5692, 'ASSIGN-5', CAST('2013-07-17 19:37:09.000' AS DATETIME) UNION ALL
    SELECT 5740, 'ASSIGN-5', '2013-07-17 19:37:09.000' UNION ALL
    SELECT 5741, 'ASSIGN-5', '2013-07-17 19:37:09.000' UNION ALL
    SELECT 5742, 'ASSIGN-5', '2013-07-17 10:40:15.000' UNION ALL
    SELECT 5742, 'ASSIGN-4', '2013-07-17 19:37:09.000'
)
SELECT
    TICKET_ID,
    ASSIGN = STUFF((
        SELECT ' ' + ASSIGN
        FROM SampleData
        WHERE TICKET_ID = sd.TICKET_ID
        ORDER BY ASSIGN_DATE ASC
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'
    ), 1, 1, '')
FROM SampleData sd
GROUP BY sd.TICKET_ID

阅读此文章由亚伦伯特兰获得更多细节。

Answer 2

这是所需的结果集。给出两种解决方案，一是简单地在票证id的基础上连接两个表，二是在分配行的基础上连接，它将给出13行。

​

----------1st-------------------

Select T.*,A.* from Tikets T with(nolock)
join
Assignement A with(nolock)
on a.TICKET_ID = t.TICKET_ID
order by t.ASSIGN_DATE

-----------2nd-----------------

Select T.*,A.* from Tikets T with(nolock)
join
Assignement A with(nolock)
on a.ASSIGN like t.ASSIGN +'%' 
order by t.ASSIGN_DATE

​

在这里输入链接描述

谢谢