(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
DarrenTraveling and swimming
JohnTraveling, eating out and swimming
PeterDone not like anything
JillSwimming only
Please! Can anyone help!!
Thank you in advance.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
