Fake records ? - 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)

Fake records ?

Author

Topic

Stalknecht
Starting Member

22 Posts

Posted - 2003-08-29 : 03:59:35

Imagine i have created the table beyond:

COLOR TYPE NUMBER
----------------------
black car 3
blue bike 2
red bike 6

I must create an report like this:

COLOR TYPE NUMBER
----------------------
black bike 0
black car 3
blue bike 2
blue car 0
red bike 6
red car 0

So i must see all possible combinations of colors and types and the corresponding numbers.
How do i create the recordset of this report?

mr_mist
Grunnio

1870 Posts

Posted - 2003-08-29 : 04:15:30

To be honest this would be easier if you had tables containing possible colours and vehicle types. Anyway..

create table #moo (colour varchar(10), type varchar(10), number int)
insert into #moo values ('black','car', 3)
insert into #moo values ('blue','bike', 2)
insert into #moo values ('red','bike', 6 )

select distinct colour, b.type
into #allstyles
from #moo cross join (select type from #moo) b

Select a.colour, a.type, isnull (number,0) as number from #allstyles a
left outer join #moo b
on a.colour = b.colour and a.type = b.type

drop table #moo
drop table #allstyles

-------
Moo. :)

Stalknecht
Starting Member

22 Posts

Posted - 2003-08-29 : 04:37:50

Thanks it works!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources