Select Dsitinct

[erics55]

Hi
I need to select a distinct view of a table so group on all columns except one. Is there a way of doing this without writing all of the column names out?

Thanks in advance

 

[johnv20]

here's how you could do it

but considering how long the code is it's almost certainly not worth the effort

/* create a @gp variable to hold the various col name */
declare @gp varchar(500)
select @gp = ' '

/* create a cursor to retrieve all col names from a table except the one we don't want to group by */
declare @tmpCol varchar(20)
declare Cols cursor LOCAL FORWARD_ONLY READ_ONLY for
        select b.name from sysobjects a, syscolumns b
	where a.name = 'MyTable'
	and a.id = b.id 
	and a.name != 'BarCode'
open Cols
fetch next from Cols into @tmpCol
 while @@FETCH_STATUS=0
  begin
	/* add the new col name to the already existing list */
	select @gp =  @tmpCol + ',' + @gp

fetch next from Cols into @tmpCol
  end
close Cols
deallocate Cols


/* remove the trailing comma from the list */
select @gp = substring(@gp,1,len(@gp)-1)



/* run the query */
declare @query varchar(1000)
set @query = 
'
select * from MyTable group by '+@gp+'
'
exec(@query)