Combine multiple row's records in one row with MS Access Query?

Question

I have a table (name- tbl2) with 21 column but I want to combine multiple rows records in a row by using a unique reference number as below:

Table Field and Records Type

Expected Result:

Please help me to resolve this issue.

Answer 1

Try the below query will solve your problem:

SELECT ID, STUFF(
     (SELECT ', ' + CAST(Item AS VARCHAR(20)) [text]
     FROM TBL12
     WHERE ID = t.ID
     FOR XML PATH(''), TYPE)
    .value('.','NVARCHAR(MAX)'),1,2,' ') Items
FROM TBL12 t
GROUP BY ID

Since you add many tags in the post, I solved in MSSQL using STUFF.
Working fiddle: http://sqlfiddle.com/#!3/d805d/4

Answer 2

This is most easily accomplished in an Access query by using the ConcatRelated() function. For more information see the following question on Stack Overflow:

Combine values from related rows into a single concatenated string value