sql hash map? - SQL Server Forums

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

Transact-SQL (2005)

sql hash map?

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-12-02 : 11:57:42

Hi,

Do not know how to word this but ill go by example.

I have the following:

Student, CourseNo
Student1, 1, 2, 3, 4
Student2, 1, 2, 3,
Student3, 2, 3, 4,

How would I be able to identify these students as they are taking 3 out of the 4 courses which are the same. It will occur that students are taking 3 similar subjects and those are the ones I would like to identify. The course field has course numbers which are coma ( , ) seperated.

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 12:01:56

Here is proper sample data.

DECLARE	@Sample TABLE
	(
		Student VARCHAR(20),
		CourseNo VARCHAR(100)
	)

INSERT	@Sample
SELECT	'Student1', '1, 2, 3, 4' UNION ALL
SELECT	'Student2', '1, 2, 3' UNION ALL
SELECT	'Student3', '2, 3, 4'

SELECT	*
FROM	@Sample

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 12:09:08

[code]
SELECT p.Student,q.Student
FROM
(SELECT t1.Student,f1.Val
FROM Table t1
CROSS APPLY dbo.ParseValues(t1.CourseNo,',')f1
)p
JOIN
(
SELECT t2.Student,f2.Val
FROM Table t2
CROSS APPLY dbo.ParseValues(t2.CourseNo,',')f2
)q
ON q.Val=p.Val
AND p.Student<>q.Student
GROUP BY p.Student,q.Student
HAVING COUNT(*) >=3
[/code]

ParseValues can be seen below

[code]
CREATE FUNCTION ParseValues
(@String varchar(8000),
@Delimiter char(1)
)
RETURNS @RESULTS TABLE
(ID int identity(1,1),
Val int
)
AS
BEGIN
DECLARE @Value varchar(100)

WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN LEFT(@String,CHARINDEX(@Delimiter,@String)-1) ELSE @String END,
@String=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN SUBSTRING(@String,CHARINDEX(@Delimiter,@String)+1,LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END
[/code]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-02 : 13:08:46

quote:

Originally posted by Peso

Here is proper sample data.

DECLARE	@Sample TABLE
	(
		Student VARCHAR(20),
		CourseNo VARCHAR(100)
	)

INSERT	@Sample
SELECT	'Student1', '1, 2, 3, 4' UNION ALL
SELECT	'Student2', '1, 2, 3' UNION ALL
SELECT	'Student3', '2, 3, 4'

SELECT	*
FROM	@Sample

E 12°55'05.63"
N 56°04'39.26"

PATRON SAINT OF LOST YAKS

No, you're never too old to Yak'n'Roll if you're too young to die.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-12-02 : 13:25:36

interesting that noone said that your table design is completely wrong. Normalize your tables.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-02 : 13:28:32

quote:
Originally posted by spirit1

interesting that noone said that your table design is completely wrong. Normalize your tables.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 15:11:15

We are waiting for Madhi.

E 12°55'05.63"
N 56°04'39.26"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-12-02 : 15:13:59

my bad. it won't happen again

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources