SQL Server 2008: Return users in DISTINCT Group -

we have group table setup follows

groupid  inactive  name g1       0         person1 g2       0         person2 g3       0         person1 g1       1         person3 g2       0         person4 g4       0         person4 

i don't know front how many groupids there or called or how many names there or called.

i looking list of each person in each distinct group (who not have isactive state of 1) in table this

g1       g2       g3       g4       etc... person1  person2  person1  person4          person4 

is @ possible? have select access database creating temporary tables etc out.

thanks looking,

dt

here have example script pivots data way want it. does:

1. create temporary table #t sample data gave
2. determine groupid's pivot dynamically in @groupidcols
3. determine groupid's select dynamically in @groupidsel
4. pivots data in #t. first row number assigned rows in #t distinguish between persons groupid (see derived table t). pivot used on derived table result want.

the script:

create table #t(     groupid char(2),     inactive bit,     name varchar(16) );  insert #t     (groupid,inactive,name) values     ('g1',0,'person1'),     ('g2',0,'person2'),     ('g3',0,'person1'),     ('g1',1,'person3'),     ('g2',0,'person4'),     ('g4',0,'person4');  declare @groupidcols varchar(max); set @groupidcols=stuff(     (select distinct         ','+quotename(groupid)             #t             inactive=0              xml path('')         )     ,1     ,1     ,'' );  declare @groupidsel varchar(max); set @groupidsel=stuff(     (select distinct         ',coalesce('+quotename(groupid)+','''') '+quotename(groupid)             #t             inactive=0              xml path('')         )     ,1     ,1     ,'' );   declare @query varchar(max); set @query='     select         '+@groupidsel+'             (             select                 groupid,                 name,                 row_number() on (partition groupid order name) rn                             #t                             inactive=0         ) t         pivot(             max(name)             groupid in ('+@groupidcols+')         ) piv ';  exec (@query);  drop table #t; 

the result:

g1      g2      g3      g4 person1 person2 person1 person4         person4