comparing every record in a recordset

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)

comparing every record in a recordset

Author

Topic

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2004-09-08 : 16:41:49

I have a problem, which I can explain but am struggling to translate into
SQL syntax.

myTable has 3 columns, with some example values
ID NAME NUMBER_ITEMS
1 john 4
2 tim 3
3 john 2
4 tim 1
5 roger 6
6 roger 7

Essentially, I am trying to derive every possible combination between NAME
and NUMBER_ITEMS , but as a set.
The set being "john,tim,roger".which conceptually become the columns
e,g
JOHN TIM ROGER
combination1 4 3 6
combination 2 4 1 6
combination3 4 3 7
combination4 4 1 7

Ultimately, I just need them as a coma delimited string. But , most
importantly I need to be able to get past STEP 1

Thanks
Jack

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-09-08 : 16:59:09

I have no idea how you could do this with a single sql statement.

Is there anyway you can get all of NAMES and NUMBER_ITEMS from the database and once this is done do processing in your application get to combination? This may be your only hope.

Dustin Michaels

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-09-08 : 17:02:59

Well, here's about half the solution:


DECLARE @MyTable TABLE (FirstName VARCHAR(20), Number_Items INT)

INSERT INTO @MyTable(FirstName, Number_Items) VALUES('John', 4)
INSERT INTO @MyTable(FirstName, Number_Items) VALUES('John', 2)
INSERT INTO @MyTable(FirstName, Number_Items) VALUES('Tim', 3)
INSERT INTO @MyTable(FirstName, Number_Items) VALUES('Tim', 1)
INSERT INTO @MyTable(FirstName, Number_Items) VALUES('Roger', 6)
INSERT INTO @MyTable(FirstName, Number_Items) VALUES('Roger', 7)

SELECT a.FirstName, a.Number_Items, b.Number_Items, c.Number_Items
FROM @MyTable a
	CROSS JOIN @MyTable b
	CROSS JOIN @MyTable c
WHERE a.FirstName <> b.FirstName 
	AND a.FirstName <> c.FirstName
	AND b.FirstName <> c.FirstName
	AND b.Number_Items <> c.Number_Items
ORDER BY a.FirstName

Where is Dr. Cross Join?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-08 : 17:07:59

Why couldn't you do:


DECLARE @MyTable TABLE (FirstName VARCHAR(20), Number_Items INT)

INSERT INTO @MyTable(FirstName, Number_Items) VALUES('John', 4)
INSERT INTO @MyTable(FirstName, Number_Items) VALUES('John', 2)
INSERT INTO @MyTable(FirstName, Number_Items) VALUES('Tim', 3)
INSERT INTO @MyTable(FirstName, Number_Items) VALUES('Tim', 1)
INSERT INTO @MyTable(FirstName, Number_Items) VALUES('Roger', 6)
INSERT INTO @MyTable(FirstName, Number_Items) VALUES('Roger', 7)


Select *
From (Select John = Number_Items From @myTable Where firstName = 'John') A,
(Select Tim = Number_Items From @myTable Where firstName = 'Tim') B,
(Select Roger = Number_Items From @myTable Where firstName = 'Roger') C

Corey

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-09-08 : 17:34:49

Nice! Something tells me that it's not a fixed number of people. I bet you could dynamic cross tab that out and get the solution though.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2004-09-09 : 05:11:16

You are right, in practise , it is not a fixed amount of people.
What do you mean by "dynamic cross tab" ?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-09-09 : 07:46:04

searching gives below....amongst many others!!...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6216

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-09 : 07:50:07

[code]
Create Table MyTable (FirstName VARCHAR(20), Number_Items INT)

INSERT INTO MyTable(FirstName, Number_Items) VALUES('John', 4)
INSERT INTO MyTable(FirstName, Number_Items) VALUES('John', 2)
INSERT INTO MyTable(FirstName, Number_Items) VALUES('Tim', 3)
INSERT INTO MyTable(FirstName, Number_Items) VALUES('Tim', 1)
INSERT INTO MyTable(FirstName, Number_Items) VALUES('Roger', 6)
INSERT INTO MyTable(FirstName, Number_Items) VALUES('Roger', 7)
INSERT INTO MyTable(FirstName, Number_Items) VALUES('Kevin', 2)
INSERT INTO MyTable(FirstName, Number_Items) VALUES('Kevin', 5)

Declare @str varchar(2000),
@pattern varchar(200),
@count int

Set @str = ''
Set @pattern = '(Select [name] = Number_Items From myTable Where firstName = [name]) S[count], '
Set @count = 1

Select
@str = @str + replace(replace(@pattern,'[name]',''''+Firstname+''''),'[count]',@count),
@count = @count + 1
From (Select distinct FirstName From MyTable) A

Select @str = 'Select * From ' + left(@str,len(@str)-1)

Exec(@str)

Drop Table MyTable
[/code]

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources