October 17, 2003 at 11:55 am
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.
October 19, 2003 at 2:57 am
Try using the crosstab wizard
Keith Henry
DBA/Developer/BI Manager
Keith Henry
October 20, 2003 at 10:19 am
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
October 20, 2003 at 7:32 pm
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
begin
--reset @Allgrp variable
set @Allgrp = NULL
--select next name to work with
select
@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
end
--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