Help on query statement for Search

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)

Help on query statement for Search

Author

Topic

froggy
Starting Member

14 Posts

Posted - 2006-10-17 : 03:53:36

Hi,
I would like to display records on a webpage and am unsure of the query statement and would like to seek some advise.
I have a lists of contact numbers(home, mobile) and they are from various Sports groups and different teams. Captains from the different sports group will be able to view the details of their teammates of the respective sports they belong to.
An example of what should be returned is shown in the attached file

Name of Captain: Toh, Danny
Category: Tennis

Team 1 Team 2 Team 3 Home Number Mobile Number
Lim, David 1111 7777
Chen, Justin 3333 9999
Koh, Johnnny 5555 1212
**Note: Justin suppose to be under Team 2 column and Johhny under Team 3 column.

Database Info:
Surname
GivenName
Sports
Team
HomeNum
MobileNum

Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-17 : 04:05:17

Attached file? Where?

Peter Larsson
Helsingborg, Sweden

froggy
Starting Member

14 Posts

Posted - 2006-10-17 : 04:07:30

Hi,
sorry i meant to say as shown below.
Name of Captain: Toh, Danny
Category: Tennis

Team 1 Team 2 Team 3 Home Number Mobile Number
Lim, David 1111 7777
Chen, Justin 3333 9999
Koh, Johnnny 5555 1212
**Note: Justin suppose to be under Team 2 column and Johhny under Team 3 column.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-17 : 04:44:50

[code]-- Prepare test data
DECLARE @Test TABLE (Surname VARCHAR(10), GivenName VARCHAR(10), Sports VARCHAR(10), Team INT, HomeNum VARCHAR(20), MobileNum VARCHAR(20))

INSERT @Test
SELECT 'Lim', 'David', NULL, 1, 'not listed', '1111 7777' UNION ALL
SELECT 'Chen', 'Justin', NULL, 2, '123 4567', '3333 9999' UNION ALL
SELECT 'Koh', 'Johnny', NULL, 3, '+46 739 149 666', '5555 1212'

-- Do the work
SELECT CASE
WHEN Team = 1 THEN Surname + ', ' + GivenName
ELSE ''
END 'Team 1',
CASE
WHEN Team = 2 THEN Surname + ', ' + GivenName
ELSE ''
END 'Team 2',
CASE
WHEN Team = 3 THEN Surname + ', ' + GivenName
ELSE ''
END 'Team 3',
HomeNum 'Home number',
MobileNum 'Mobile number'
FROM @Test[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources