Group by

  • I have a table:

    id Name grp

    12 sjoo 123

    13 sjoo 456

    14 sjoo 890

    15 abcd 123

    16 abcd 456

    17 abcd 890

    I need to create the following output:

    id Name groups

    12 sjoo 123,456,890

    15 abcd 123,456,890

    is there a sql command that can do this in MS Access.

  • Try using the crosstab wizard

    Keith Henry

    DBA/Developer/BI Manager

    Keith Henry

    According to everyone I know I "do something with computers?" for a living, so there you go.

  • Not knowing much about your data, I would still strongly recommend that you normalize your data in SQL and then copy the results to MS Access. SQL is a far more robust environment for manipulating data. The following code takes your sample data and pivots the results into the format you want:

    select id,name,

    case when id='12' then grp else ' ' end as fld1,

    case when id='13' then grp else ' ' end as fld2,

    case when id='14' then grp else ' ' end as fld3,

    case when id='15' then grp else ' ' end as fld4,

    case when id='16' then grp else ' ' end as fld5,

    case when id='17' then grp else ' ' end as fld6

    into test1

    from testtbl

    select min(id) id,name,

    max(fld1) fld1,max(fld2) fld2,max(fld3) fld3,

    max(fld4) fld4,max(fld5) fld5,max(fld6) fld6

    into test2

    from test1

    group by name

    select id,name,ltrim(fld1)+case when fld1=' ' then '' else ',' end +

    ltrim(fld2)+case when fld2=' ' then '' else ',' end +

    ltrim(fld3)+'' +

    ltrim(fld4)+case when fld4=' ' then '' else ',' end +

    ltrim(fld5)+case when fld5=' ' then '' else ',' end +

    ltrim(fld6) as grp

    into test3

    from test2

  • The problem with using a case statement is that it isn't dynamic. The easiest way to do this dynamically is to use a temp table (in SQL 2k you can use a @ table variable)

    PS: as far as I know there is no way to do this in Access, you should use SQL.


    Create Test Data


    if object_ID('tempdb..#Source') is not null drop table #Source

    Create TAble #Source (Id int, Name char(4), grp int)

    Insert #Source Values (12,'sjoo', 123)

    Insert #Source Values (13,'sjoo', 456)

    Insert #Source Values (14,'sjoo', 890)

    Insert #Source Values (15,'abcd', 123)

    Insert #Source Values (16,'abcd', 456)

    Insert #Source Values (17,'abcd', 890)


    Build output


    if object_ID('tempdb..#Output') is not null drop table #Output

    Create TAble #Output (Name char(4), Allgrp varchar(255))

    --Variables used during insert

    declare @Name char(4),

    @Allgrp varchar(255)

    While 1 = 1


    --reset @Allgrp variable

    set @Allgrp = NULL

    --select next name to work with


    @Name = [Name],

    --This is the trick; set's the value of @Allgrp to every matching value in Source table (with csv)

    @Allgrp = isnull(@Allgrp + ',', '') + cast(grp as varchar)

    From #Source

    Where [Name] =

    (select top 1 [Name]

    From #Source Where [Name] > isnull(@Name, '')

    order by [Name])

    order by [Name]

    --Breaks while loop when no more records

    if @@Rowcount = 0 Break

    --Creates output records

    Insert #Output

    select @Name, @Allgrp


    --selects output records

    select * from #Output

    Signature is NULL

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply