Create string - all listed element for multiple fields grouping using MS Access SQL

Question

For MSACCESS query, I have a table with 3 fields as following:

ID1 ID2 ID3
101 1001 1
102 1001 1
101 1002 1
102 1003 2
102 1004 3
103 1004 3
104 1004 4

I would like to create a string with all recorded ID1 for each ID2, my desired output is following:

ID2 ID3 output
1001 1 101-102
1002 1 101
1003 2 102
1004 3 102-103
1004 4 104

There are 2 possible ID1 value for ID2=1001, which is 101 and 102. Thus the desired string is 101-102 (assume no duplication by considering both fields ID1, ID2)

My attempt Based on @Erik suggestion, i tried below while have error message on type mismatch.

Select T.[ID2] + T.[ID3]
  , GetList("Select [ID 1] From tblCEPAlert As T1 Where T1.[ID2] + T1.[ID3]  = " & T.[ID2] + T.[ID3],"",", ") as output
From tblCEPAlert AS T
Group By T.[ID2] + T.[ID3];

Possible duplicate of [Ms Access Query: Concatenating Rows through a query](https://stackoverflow.com/questions/5517233/ms-access-query-concatenating-rows-through-a-query) (There's probably another canonical dupe for MS SQL) — Erik A, Nov 13 '17 at 07:21
Thanks Erik for your refer. I have edit the post to suit my situation. I have more than 2 variables. — useR, Nov 13 '17 at 07:40
But do edit your title, which still says that this is about SQL Server, not Access. — Gustav, Nov 13 '17 at 07:41
[here is a VBA solution](http://allenbrowne.com/func-concat.html) from Allen Borwne. — marlan, Nov 13 '17 at 08:00

score 1 · Answer 1 · answered Nov 13 '17 at 07:20

Please check following SQL Concatenation query where I used FOR XML PATH to concatenate strings

SELECT
    distinct
    i.ID2,
  STUFF(
    (
    SELECT
      '-' + convert(varchar(3),ID1)
    FROM IDs
    where ID2 = i.ID2
    FOR XML PATH(''),TYPE
    ).value('.','VARCHAR(MAX)'
    ), 1, 1, ''
  ) As IDList
  from IDs i

using the following metadata

create table IDs (ID1 int, ID2 int)
insert into IDs select 101, 1001
insert into IDs select 102, 1001
insert into IDs select 101, 1002
insert into IDs select 102, 1003
insert into IDs select 102, 1004
insert into IDs select 103, 1004
insert into IDs select 104, 1004

Output is as follows

DineshDB · Answer 2 · 2017-11-13T07:41:57.407

Refer the Link, Concatenate many rows into a single text string?

Hope it useful to to you, Try this:

DECLARE @Tab TABLE(ID1 VARCHAR(100), ID2 INT)
INSERT INTO @Tab VALUES(101,1001),(102,1001),(101,1002),(102,1003),(102,1004),(103,1004),(104,1004)

Select Main.ID2,
       Left(Main.[Outs],Len(Main.[Outs])-1) As "Students"
From
    (
        Select distinct ST2.ID2, 
            (
                Select ST1.ID1 + '-' AS [text()]
                From @Tab ST1
                Where ST1.ID2 = ST2.ID2
                ORDER BY ST1.ID2
                For XML PATH ('')
            ) [Outs]
        From @Tab ST2
    ) [Main]

For MS-ACCESS Can you read this answer ConcatRelated(), hope it was the one you need...

Combine values from related rows into a single concatenated string value

thanks for your prompt reply. sorry that i didn't mention clearly i m using MS access query. — useR, Nov 13 '17 at 07:38

score 1 · Answer 3 · answered Nov 13 '17 at 07:23

1

You could use STUFF() function in your T-SQL Script which could help you :

SELECT DISTINCT 
        T.ID2,
        [output] = STUFF(
                      (select  '-'+CONVERT(VARCHAR, ID1) from <table> where ID2 = T.ID2 FOR XML PATH('')),
                       1, 1, '')
FROM <table> T

Result :

ID2     output
1001    101-102
1002    101
1003    102
1004    102-103-104

answered Nov 13 '17 at 07:23

Yogesh Sharma

49,870
5
26
52

thanks for your prompt reply. sorry that i didn't mention clearly i m using MS access query. Or is it also applicable to mssql? or just ms sql server? – useR Nov 13 '17 at 07:38

Create string - all listed element for multiple fields grouping using MS Access SQL

3 Answers3