July 28, 2009 at 11:42 am
Apologies for the rather vague subject; I was struggling to state the question in a succinct one-liner.
The situation is as follows:
I have two fields; name (varchar) and ID (int). And I want a query that will give me all names that 'share' the same ID value. See code below:
create table #temp1
(
name varchar(10),
ID int
)
insert into #temp1
select 'John',1 union
select 'Jane',1 union
select 'Fred',2 union
select 'Adam',3 union
select 'Paul',4 union
select 'Lisa',5 union
select 'Phil',3
----------------------
select
'1stName' = t1.name,
'ID' = t1.ID,
'2ndName' = t2.name
from
#temp1 t1
inner join #temp1 t2 on t1.ID = t2.ID
where
t1.name <> t2.name
The query above almost gives me what I want - it will return 4 records, because Jane and John both have and ID of 1, and Phil and Adam both have an ID value of 3.
However, the query returns 2 records for each pairing, i.e.
Jane 1 John
John 1 Jane
And what I want to achieve is to only return one record for each pairing. Using the above Jane/John example I'm not concerned about which record to return; just whatever is easiest.
I'm using SqlServer 2000 so can't do anything clever with ROWNUMBER unfortunately.
Any help greatly appreciated.
Regards.....Jason
July 28, 2009 at 11:50 am
Will this do what you need:
create table #temp1
(
name varchar(10),
ID int
)
insert into #temp1
select 'John',1 union
select 'Jane',1 union
select 'Fred',2 union
select 'Adam',3 union
select 'Paul',4 union
select 'Lisa',5 union
select 'Phil',3;
select ID, min(name) as Name
from #temp1
group by ID
having count(*) > 1;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 28, 2009 at 12:49 pm
create table #temp1
(
name varchar(10),
ID int
)
insert into #temp1
select 'John',1 union
select 'Jane',1 union
select 'Fred',2 union
select 'Adam',3 union
select 'Paul',4 union
select 'Lisa',5 union
select 'Phil',3
----------------------
SELECT MIN(NAME), [id], MAX(NAME) FROM #temp1
WHERE [ID] IN (
SELECT [ID] FROM #temp1
GROUP BY [id] HAVING COUNT(*) > 1)
GROUP BY [ID]
I am assuming that there will only be 2 possible names per group but I think this is what you are looking for.
July 29, 2009 at 1:20 am
Thanks guys.
Of the two suggestions Matt's gives me the required output when using the sample data.
Although I think I may have over-simplified the scenario by using the word 'pairs'.
Matt, your use of MIN and MAX pre-supposes there will only ever be two names with the same ID (as you mention).
What if the maximum number of names with the same ID is variable and not known?
How can I return just one row for each ID that has multiple related names?, so let's assume we add another record to the sample data, the name 'Dave' and ID of 1. How would I query to return:
1 Dave Jane John
3 Adam Phil
....in 4 columns
But with a solution that would handle more related names (i.e. return 5 columns if an ID has 4 related names)
Thanks in advance,
Jay
July 29, 2009 at 6:49 am
I am not sure this satisfies your requirements but it does get you one row per ID. The names would be in a comma separated list. I couldn't figure out a good way of having dynamic columns so here is my current solution. I am creating a function named testCombiningRows but you are can name it whatever you like. If it does have to be dynamic rows I am not sure how to handle it.
IF (SELECT OBJECT_ID('tempTable')) IS NOT NULL
DROP TABLE tempTable
CREATE TABLE tempTable ([id] VARCHAR(10), NAME VARCHAR(10))
INSERT INTO tempTable VALUES ('1', 'Jessi')
INSERT INTO tempTable VALUES ('1', 'Derek')
INSERT INTO tempTable VALUES ('1', 'Derek2')
INSERT INTO tempTable VALUES ('1', 'Derek3')
INSERT INTO tempTable VALUES ('2', 'Gerald')
INSERT INTO tempTable VALUES ('2', 'Gerald2')
INSERT INTO tempTable VALUES ('3', 'Gerald3')
INSERT INTO tempTable VALUES ('4', 'Test');
GO
IF (SELECT OBJECT_ID('testCombiningRows')) IS NOT NULL
DROP FUNCTION testCombiningRows
go
CREATE FUNCTION [dbo].[testCombiningRows]
(
@id int
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @temp AS VARCHAR(100)
SELECT @temp = COALESCE(@temp+',','')+[NAME] FROM tempTable
WHERE [ID] = @id
RETURN @temp
END
GO
SELECT DISTINCT id, dbo.testCombiningRows(tempTable.id) AS commaSeparated
FROM tempTable
July 29, 2009 at 7:02 am
Jay
I have been looking at your problem, how many names to you expect to have to return and may we know why?
Kev
July 29, 2009 at 8:49 am
Thanks for the answers guys. It isn't a real-world problem as such. It just relates to a query I was writing a few days ago, which made me wonder whether it was at all possible to:
a) not return two rows per 'pairing' (which was achieved with min/max...how did I miss that!)
b) have a dynamic, changing number of fields depending on the source data
The commaseparated solution was one I was also considering as a round-a-bout way of achieving the same output.
Cheers,
Jay
--
July 29, 2009 at 4:35 pm
jason (7/29/2009)
b) have a dynamic, changing number of fields depending on the source data
See the following... http://qa.sqlservercentral.com/articles/cross+tab/65048/
--Jeff Moden
July 30, 2009 at 7:29 am
Okay, I may be under-thinking this, or I may just be to lazy, but wouldn't this work?:
create table #temp1
(
name varchar(10),
ID int
)
insert into #temp1
select 'John',1 union
select 'Jane',1 union
select 'Fred',2 union
select 'Adam',3 union
select 'Paul',4 union
select 'Lisa',5 union
select 'Phil',3
----------------------
select
'1stName' = t1.name,
'ID' = t1.ID,
'2ndName' = t2.name
from
#temp1 t1
inner join #temp1 t2 on t1.ID = t2.ID
where
t1.name > t2.name
July 31, 2009 at 6:28 am
Timothy J Hartford (7/30/2009)
Okay, I may be under-thinking this, or I may just be to lazy, but wouldn't this work?:
Not if there were more than two with one ID
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
July 31, 2009 at 6:48 am
jcrawf02
Then I am missing the intent. This lists every unique combination only once.
create table #temp1
(
name varchar(10),
ID int
)
insert into #temp1
select 'John',1 union
select 'Jane',1 union
select 'Fred',2 union
select 'Adam',3 union
select 'Paul',4 union
select 'Lisa',5 union
select 'Jarrod',5 union
select 'Loves Subs',5 union
select 'Phil',3
----------------------
select
'1stName' = t1.name,
'ID' = t1.ID,
'2ndName' = t2.name
from
#temp1 t1
inner join #temp1 t2 on t1.ID = t2.ID
where
t1.name > t2.name
gives:
1stName, ID, 2ndName
Phil, 3, Adam
John, 1, Jane
Lisa, 5, Jarrod
Loves Subs, 5, Jarrod
Loves Subs, 5, Lisa
Isn't that what was asked for? Each unique pairing?
July 31, 2009 at 6:54 am
Yes Tim, that was what was originally asked, but the discussion evolved into the possibility of there being more than two names per id. So you are correct, that answers the original question, but does not answer the subsequent one.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
July 31, 2009 at 7:06 am
In my solution I show 3 names for ID 5.
July 31, 2009 at 7:07 am
Tim,
What if there were two people of the same name sharing the same ID?
Your WHERE criteria is what achieves the 'one row per pairing', but it
would have a problem if two people of the same name shared an ID.
And just changing the where clause operator from > to >= obviously
won't work.
Actually, that's quite interesting? How to handle this scenario?
Without another field to somehow differentiate them it could be awkward,
particularly using SS2K and not having any ROWNUMBER functionality.
Thoughts anyone? Assuming 'John' in the sample data is changed to 'Jane',
how could this be handled, with no other fields to differentiate the records?
Hmmm?
Jay
__
July 31, 2009 at 7:16 am
Okay... that might be what I missed. Wouldn't a simple CTE solve that? And if you wanted only a distinct "John - Jane" add a distinct keyword?
create table #temp1
(
name varchar(10),
ID int
)
insert into #temp1
select 'John',1 union ALL
select 'Jane',1 union ALL
select 'Fred',2 union ALL
select 'Adam',3 union ALL
select 'Paul',4 union ALL
select 'Lisa',5 union ALL
select 'Jarrod',5 union ALL
select 'Loves Subs',5 union ALL
select 'Phil',3 union ALL
select 'John',1
;WITH NList
AS (SELECT Name, ID, ROW_NUMBER() OVER (ORDER BY Name) AS relID FROM #temp1)
select
'1stName' = t1.name,
'ID' = t1.ID,
'2ndName' = t2.name
from
NList t1
inner join NList t2 on t1.ID = t2.ID
where
t1.relID > t2.relID
Drop table #temp1
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply