[newbie] Get (multi-record) results back on 1 row.

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

SQL Server Development (2000)

[newbie] Get (multi-record) results back on 1 row.

Author

Topic

Wout3
Starting Member

1 Post

Posted - 2004-02-26 : 06:40:24

I'm planning on using a datagrid but then I need to get all the results in one row to display it properly.

A member can have multiple settings. I want to write a statement that returns one row with the 2 (or 3) setting values...

My tables:
* Table Members
MemberID, Name
01, John

* Table Settings
SettingID, MemberID, SettingValue
555, 01, "blue"
444, 01, "Arial"
333, 01, "12"

The result I want:
01 John blue Arial 12

Can somebody give me some tips/hints?

Thanks,
Wout

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-02-26 : 07:17:30

Assuming colors have a settingid of 555 and fonts of 444 and size of 333 Then this will work.

--******************************************************
CREATE TABLE #Members(MemberID INT, Name VARCHAR(50));
CREATE TABLE #Settings(SettingID INT, MemberID Int, SettingValue VARCHAR(100));

INSERT INTO #Members VALUES(01, 'John')

INSERT INTO #Settings VALUES(555, 01, 'blue')
INSERT INTO #Settings VALUES(444, 01, 'Arial')
INSERT INTO #Settings VALUES(333, 01, '12')

SELECT m.MemberID, m.Name, c.SettingValue, f.SettingValue, s.SettingValue
FROM #Members m
JOIN (SELECT MemberID, SettingValue
FROM #Settings
WHERE SettingID = 555) c
ON m.MemberID = c.MemberID
JOIN (SELECT MemberID, SettingValue
FROM #Settings
WHERE SettingID = 444) f
ON m.MemberID = f.MemberID
JOIN (SELECT MemberID, SettingValue
FROM #Settings
WHERE SettingID = 333) s
ON m.MemberID = s.MemberID

Duane.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-26 : 07:24:04

Here is another method:

SELECT 	m.memberid,
	name,
	MAX(CASE WHEN settingid = 555 THEN settingvalue ELSE '' END) fontcolor,
	MAX(CASE WHEN settingid = 444 THEN settingvalue ELSE '' END) font,
	MAX(CASE WHEN settingid = 333 THEN settingvalue ELSE '' END) fontsize
FROM #members m
JOIN #settings s ON s.memberid = m.memberid
GROUP BY m.memberid, m.name

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-02-26 : 07:28:18

quote:

Originally posted by ehorn

Here is another method:

SELECT 	m.memberid,
	name,
	MAX(CASE WHEN settingid = 555 THEN settingvalue ELSE '' END) fontcolor,
	MAX(CASE WHEN settingid = 444 THEN settingvalue ELSE '' END) font,
	MAX(CASE WHEN settingid = 333 THEN settingvalue ELSE '' END) fontsize
FROM #members m
JOIN #settings s ON s.memberid = m.memberid
GROUP BY m.memberid, m.name

Nice!

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources