CODEWITHSUNDEEP
sqlsql-server
Mohammed Salah
Mohammed Salah

Reputation: 11

Sql query for merge two rows into one row if id is same

I have table named TICKET_LAB I have table named TICKET_LAB as shown as shown TICKET_LAB table

enter image description here

and table named test and table named test as shown as shown Test table:

enter image description here

I need a SQL query to get the result like that :

I need a SQL query to get the result like that :

TLAB_TICKETID | TEST_NAME
------------- | -------------
    29        | blood, stool,...etc

any idea

Upvotes: 1

Views: 68

Answers (2)

CathalMF
CathalMF

Reputation: 10055

SELECT tckt.TLAB_TICKETID, STUFF(
     (SELECT ',' + TEST_NAME 
      FROM [hos].[dbo].[test]
      WHERE tckt.TLAB_TESTID = Test_ID
      FOR XML PATH (''))
     , 1, 1, '')
FROM [hos].[dbo].[test] tst
    JOIN [hos].[dbo].[Ticket_Lab] tckt
    on tst.Test_ID = tckt.TLAB_TESTID
GROUP BY tckt.TLAB_TICKETID

I think this should work.

Upvotes: 1

Denis Reznik
Denis Reznik

Reputation: 964

In SQL Server it may looks like this:

CREATE TABLE #TICKET_LAB(Id int)
INSERT INTO #TICKET_LAB VALUES(1), (2)

CREATE TABLE #TEST(TicketId int, Name nvarchar(100))
INSERT INTO #TEST VALUES(1, 'blood'), (1, 'etc.'), (2, 'test')

SELECT Id, STUFF((
        SELECT ', ' + t.Name FROM #TEST t WHERE tl.Id = t.TicketId FOR XML PATH('')), 1, 2, ''
    )
FROM #TICKET_LAB tl

Upvotes: 1

Related Questions