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 2005 Forums
 Other SQL Server Topics (2005)
 Viewing Data

Author  Topic 

DennisMollet
Starting Member

4 Posts

Posted - 2010-08-05 : 16:38:14
I would like to know if there is a way to separate data in a certain way using a view or a function. I am trying to give my users the ability to create their own custom combo boxes in a Visual Studio application

Here's my generic SQL Server 2005 Table setup:

Table1: People (ID, name, etc)
Table2: PeopleComboBoxValues (ID, PersonID, ComboBoxID, Value)
Table3: PeopleComboBoxes (ID, ComboBoxName, etc)

People has a one to many relationship to PeopleComboBoxValues.
PeopleComboBoxes has a one to many relationship to PeopleComboBoxValues.

Here is some data:
People: Joe, John, Jeff
PeopleComboBoxes: Favorite Color, Height Category, Favorite Number
PeopleComboBoxValues:
Joe: Color-Red
Joe: Height-Tall
Joe: Number 3
John: Color-Green
John: Height-Medium
John: Number 3
Jeff: Color-Red
Jeff: Height-Medium
Jeff: Number 7

Is there a way to create a view that would show something like the following:

Name, Favorite Color, Height Category, Favorite Number
Joe, Red, Tall, 3
John, Green, Medium, 3
Jeff, Red, Medium, 7


Is that possible? Is my database set up poorly?
I have a way to do this using VB code, but it would run much faster if SQL Server could create this view.

Thanks!

Dennis

MSquared
Yak Posting Veteran

52 Posts

Posted - 2010-08-18 : 13:03:50
you can try this

declare @People table (ID int, name varchar(20))
declare @PeopleComboBoxesValues table (ID int, ComboBoxID int, Value varchar(20))
declare @PeopleComboBoxes table (ComboBoxID int, ComboBoxName varchar(20))

insert into @People
select 1, 'Joe' union all
select 2, 'John' union all
select 3, 'Jeff'

insert into @PeopleComboBoxes
select 1, 'Color' union all
select 2, 'Height' union all
select 3, 'Number'

insert into @PeopleComboBoxesValues
select 1, 1, 'Red' union all
select 1, 2, 'Tall' union all
select 1, 3, '3' union all
select 2, 1, 'Green' union all
select 2, 2, 'Medium' union all
select 2, 3, '3' union all
select 3, 1, 'Red' union all
select 3, 2, 'Medium' union all
select 3, 3, '3'


declare @User varchar(10)

set @User = 'Joe'

select p.name, cb.ComboBoxName ,cbv.Value
from @People p
inner join @PeopleComboBoxesValues cbv
on p.ID = cbv.ID
inner join @PeopleComboBoxes cb
on cbv.ComboBoxID = cb.ComboBoxID
where p.name = @User
Go to Top of Page
   

- Advertisement -