CODEWITHSUNDEEP
sqlsql-servert-sql
Gyuzal
Gyuzal

Reputation: 1591

Concat several rows into one using grouping in T-SQL

I'd appreciate if someone could advise on following: My table looks like this:

ID    SEQ   ACCOUNT  AMOUNT   DESCRIPTION           ...          ....
0719  1     8019     222,2    this is the
0719  1     NULL     NULL     description of
0719  1     NULL     NULL     account with
0719  1     NULL     NULL     amount= 222,2
0719  1     NULL     NULL     NULL
0719  1     NULL     NULL     NULL
0719  2     8019     111,1    this is the
0719  2     NULL     NULL     description of
0719  2     NULL     NULL     account with
0719  2     NULL     NULL     amount= 111,1

As you can see there is one ID and one ACCOUNT with several AMOUNTS. I need to combine the DESCRIPTION of each entry grouped by SEQ column.

My goal is:

ID    SEQ   ACCOUNT  AMOUNT   DESCRIPTION         ...           ...
0719  1     8019     222,2    this is the description of account with amount= 222,2
0719  2     8019     111,1    this is the description of account with amount= 111,1

I tried to use COALESCE or FOR XML operators, but cannot add grouping by SEQ there:

DECLARE @Desc NVARCHAR(8000) 
SELECT @Desc = COALESCE(@Desc + ', ', '') + [DESCRIPTION]
FROM [TABLE]
WHERE MDC_ID = '0719'
      AND (ACCOUNT = '8019' or ACCOUNT IS NULL) 
      AND (AMOUNT= 222,2 OR AMOUNT is null)    
--GROUP BY SEQ -- DESCRIPTION is invalid in the select list because it is not contained in 
                --either an aggregate function or the GROUP BY clause
SELECT @Desc

How can I change my script?

Upvotes: 1

Views: 95

Answers (2)

Jayvee
Jayvee

Reputation: 10875

SELECT id, seq, MAX(account),MAX(amount),
(SELECT DESCRIPTION+' ' FROM yourtable b WHERE b.id=a.id AND b.seq=a.seq FOR XML PATH(''))
FROM yourtable a
GROUP BY id, seq

Upvotes: 1

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this:

SELECT T.ID, T.SEQ, MAX(T.AMOUNT) AMOUNT, D.DS
FROM tbl T
CROSS APPLY
(
    SELECT [DESCRIPTION] + ' '
    FROM tbl B
        WHERE T.ID = B.ID
            AND T.SEQ = B.SEQ
            AND [DESCRIPTION] IS NOT NULL
    FOR XML PATH('')
) D(DS)
GROUP BY ID, SEQ, DS

SQL FIDDLE DEMO

Upvotes: 1

Related Questions