Query to roll up two records into one

Question

I can't seem to figure this sql out. I'm thinking I may need some VBA but:

I have two tables related by ID where its a one to many relationship.

table1                     table2
ID                         ID, Date

Sample:

table1                     table2
1234                       1234, 12/31/2019
                           1234, 10/1/2021

The result I want back is one record with two columns:

ID              Date
1234            12/31/2019 | 10/1/2021

So I return one record with the two (or more) dates concatenated into one column.

Any ideas?

Also study [this answer](https://stackoverflow.com/questions/57266386/concatrelated-in-complex-database-parsing-problems/57267201#57267201). — Gustav, Aug 14 '19 at 15:02

score -1 · Answer 1 · answered Aug 14 '19 at 14:54

-1

This should work fine:

SELECT Table1.*
    , [MyDate] = Isnull( Stuff(
        (SELECT N' | ' + LTRIM(RIGHT(CONVERT(CHAR(20), Table2.MyDate, 22), 11)) FROM Table2
        WHERE Table2.ID = Table1.ID
        FOR XML PATH(''),TYPE ).value('text()[1]', 'nvarchar(max)')
    , 1, 2, N''), N'')
FROM Table1

answered Aug 14 '19 at 14:54

AHeyne

3,377
2
11
16

1

Not for Access, it won't. – Gord Thompson Aug 14 '19 at 14:55
1

Oh, Access-only. Sorry, that's indeed Transact-SQL (Microsoft SQL Server). – AHeyne Aug 14 '19 at 14:56

Query to roll up two records into one

1 Answers1