unusual group by problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

unusual group by problem

Author

Topic

billsox
Yak Posting Veteran

74 Posts

Posted - 2004-11-12 : 12:02:26

Hi -- I'm wondering if there's any easy way to take a table like this:


F1    F2    F3    F4
-----------------------
A     DS    NULL  NULL
A     NULL  GX    NULL
A     NULL  GX2   NULL
A     NULL  NULL  XMAS

and transform it so it looks like this:


F1    F2    F3    F4
----------------------
A     DS    GX    XMAS
A     NULL  GX2   NULL

Note that my actual tables have a lot more than 4 rows so the more efficient solution, the better. Any ideas?

Bill

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-12 : 15:16:34

try something like this:


Create Table #numbers (n int identity(1,1), a int)
Insert Into #numbers
Select 1 from master.dbo.syscolumns

Create Table #temp (f1 varchar(1), f2 varchar(5), f3 varchar(5), f4 varchar(5))
Insert Into #temp
Select 'A', 'DS', NULL, NULL
Union Select 'A', NULL, 'GX', NULL
Union Select 'A', NULL, 'GX2', NULL
Union Select 'A', NULL, NULL, 'XMAS'


Select * From #temp

Select
	A.f1,
	A.n,
	C2.f1,
	C2.f2,	
	C3.f3,
	C4.f4
From (Select distinct f1, n from #temp, #numbers B) A
Left Join
	(
	Select f1, f2, f2cnt = (select count(distinct f2) from #temp where f1 = Z.f1 and f2 < z.f2 and f2 is not null)+1
	From #temp Z Where f2 is not null
	) C2
On A.f1 = C2.f1
and A.n = C2.f2cnt
Left Join
	(
	Select f1, f3, f3cnt = (select count(distinct f3) from #temp where f1 = Z.f1 and f3 < z.f3 and f3 is not null)+1
	From #temp Z Where f3 is not null
	) C3
On A.f1 = C3.f1
and A.n = C3.f3cnt
Left Join
	(
	Select f1, f4, f4cnt = (select count(distinct f4) from #temp where f1 = Z.f1 and f4 < z.f4 and f4 is not null)+1
	From #temp Z Where f4 is not null
	) C4
On A.f1 = C4.f1
and A.n = C4.f4cnt
Where C2.f2 is not null
or C3.f3 is not null
or C4.f4 is not null

Drop Table #temp
Drop Table #numbers

Corey

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-12 : 15:20:33

nice one, corey -- i think that's the way to do it.

- Jeff

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-12 : 16:58:18

How about this,
it is easier to modify in case you get more columns f2,,,fn to group.

set nocount on

create table #temp (f1 varchar(1), f2 varchar(5), f3 varchar(5), f4 varchar(5))
insert #temp(f1,f2,f3,f4)
select 'a', 'ds', null, null
union select 'a', null, 'gx', null
union select 'a', null, 'gx2', null
union select 'a', null, 'gx3', null
union select 'a', null, null, 'xmas2'
union select 'a', null, null, 'xmas3'


create table #r (f1 varchar(1), f2 varchar(5), f3 varchar(5), f4 varchar(5))

select '...start grouping...'
while @@rowcount > 0
	insert #r(f1,f2,f3,f4)
	select f1,min(f2),min(f3),min(f4)
	from #temp
	where not exists(
		select * from #r
		where #r.f2 = #temp.f2
		or #r.f3 = #temp.f3
		or #r.f4 = #temp.f4 )
	group by f1

select * from #r

drop table #temp
drop table #r

set nocount off

rockmoose

billsox
Yak Posting Veteran

74 Posts

Posted - 2004-11-15 : 10:37:31

I like your solution, Rockmoose. Easy to understand and yes, I do have more columns to group. Thanks!

Bill

quote:

Originally posted by rockmoose

How about this,
it is easier to modify in case you get more columns f2,,,fn to group.

set nocount on

create table #temp (f1 varchar(1), f2 varchar(5), f3 varchar(5), f4 varchar(5))
insert #temp(f1,f2,f3,f4)
select 'a', 'ds', null, null
union select 'a', null, 'gx', null
union select 'a', null, 'gx2', null
union select 'a', null, 'gx3', null
union select 'a', null, null, 'xmas2'
union select 'a', null, null, 'xmas3'


create table #r (f1 varchar(1), f2 varchar(5), f3 varchar(5), f4 varchar(5))

select '...start grouping...'
while @@rowcount > 0
	insert #r(f1,f2,f3,f4)
	select f1,min(f2),min(f3),min(f4)
	from #temp
	where not exists(
		select * from #r
		where #r.f2 = #temp.f2
		or #r.f3 = #temp.f3
		or #r.f4 = #temp.f4 )
	group by f1

select * from #r

drop table #temp
drop table #r

set nocount off

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources