Dynamic field question

Darren Jensen

Can someone point me in the right direction to solve the following
(basic) SQL problem below using SQL Server:

Let's say I have a table like this that lists people's likes:

CREATE TABLE likes (
myname VARCHAR (60),
travel BIT,
eatingout BIT,
disco BIT,
swimming BIT);

Let's say I put the following data inside this table:

INSERT INTO likes VALUES ('Darren', 1,0,0,1)
INSERT INTO likes VALUES ('John',1,1,0,1)
INSERT INTO likes VALUES ('Peter',0,0,0,0)
INSERT INTO likes VALUES ('Jill',0,0,0,1)

Then what I want is to create a (view? Or function? – I am not sure),
called ‘likes_details' that when I send this query:

SELECT myname, likes FROM likes_details

Returns the following:

Myname likes
Darren Traveling and swimming
John Traveling, eating out and swimming
Peter Done not like anything
Jill Swimming only

Please! Can anyone help!!

Thank you in advance.

Jul 20 '05 #1

Anith Sen

Darren,

The mess-ed up short term workaround to your problem with the existing
schema is:

SELECT myName,
COALESCE( NULLIF (
CASE travel WHEN 1 THEN 'Travelling, '
ELSE SPACE(0) END +
CASE eatingout WHEN 1 THEN 'eating out, '
ELSE SPACE(0) END +
CASE disco WHEN 1 THEN 'disco, '
ELSE SPACE(0) END +
CASE swimming WHEN 1 THEN 'swimming, '
ELSE SPACE(0) END, SPACE(0)),
'Do not like anything')
FROM likes ;

Now, the real solution to your problem is that you need to overhaul your
schema, it has values as column names, under-normalized and thus unusable. A
good way of representing this information would be like:

CREATE TABLE Persons (
Person_id INT NOT NULL PRIMARY KEY,
PersonName VARCHAR(10) NOT NULL,
...);
CREATE TABLE Hobbies (
Hobby_id INT NOT NULL PRIMARY KEY,
HobbyDesc VARCHAR(20) NOT NULL,
...);
CREATE TABLE PersonHobbies(
Person_id INT NOT NULL
REFERENCES Persons(Person_id),
Hobby_id INT NOT NULL
REFERENCES Hobbies(Hobby_id)
PRIMARY KEY (Person_id, Hobby_id)) ;

The primary keys in the Persons table & Hobbies tables are assigned with the
assumption that there could be other relevant attributes associated with
these entities, otherwise using Name & Desc as keys are just fine. The data
for these tables, based on the information you provided could be like:

INSERT Persons SELECT 1, 'Darren' ;
INSERT Persons SELECT 2, 'John' ;
INSERT Persons SELECT 3, 'Peter' ;
INSERT Persons SELECT 4, 'Jill' ;
GO
INSERT Hobbies SELECT 1, 'travel' ;
INSERT Hobbies SELECT 2, 'eatingout' ;
INSERT Hobbies SELECT 3, 'disco' ;
INSERT Hobbies SELECT 4, 'swimming' ;
GO
INSERT PersonHobbies SELECT 1, 1 ;
INSERT PersonHobbies SELECT 1, 4 ;
INSERT PersonHobbies SELECT 2, 1 ;
INSERT PersonHobbies SELECT 2, 2 ;
INSERT PersonHobbies SELECT 2, 4 ;
INSERT PersonHobbies SELECT 4, 4 ;
GO

The above schema represents a m-to-m relationship between Persons and
Hobbies. It allows you to add persons and hobbies to the system without
having to alter the tables and facilitates efficient querying. Now, you can
have a SQL statement like:

SELECT p1.PersonName, h1.HobbyDesc, ...
FROM Persons p1
LEFT OUTER JOIN PersonHobbies ph1
ON p1.Person_id = ph1.Person_id
LEFT OUTER JOIN Hobbies h1
ON h1.Hobby_id = ph1.Hobby_id

Get the resultset to your client application & cross tab the data to the
format with comma, add words like "and" etc for the requirements for
display.

--
Anith

Jul 20 '05 #2