Concat several rows into one using grouping in T-SQL

Question

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?

well written questrion but its a duplicate. The answer is messy. http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 — Jodrell, Jan 27 '14 at 10:48
Voting to leave open as per http://meta.stackexchange.com/questions/139961/whats-the-policy-on-closing-unique-questions-with-overlapping-but-non-duplicat — Jan Doggen, Jan 27 '14 at 11:28

Hamlet Hakobyan · Accepted Answer · 2014-01-27T10:55:37.773

1

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

edited Jan 27 '14 at 10:55

answered Jan 27 '14 at 10:50

Hamlet Hakobyan

32,965
6
52
68

score 1 · Answer 2 · answered Jan 27 '14 at 10:59

1

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

answered Jan 27 '14 at 10:59

Jayvee

10,670
3
29
40

Concat several rows into one using grouping in T-SQL

2 Answers2