April 5, 2018 at 7:50 am
The following SQL works fine ( run it please and you will see the exact output I am looking for )
However it only works fine when the table has records for one member ( Just one member )
Desired Output: I need the records for each member paired with the records for the same member ( I don't care about the order )
Example: If a member has 3 records ( Lets say 1,2,3 ) I like them paired as follows:
1,2
1,3
2,3
Note: I don't need 1,2 and 2,1 ( Since they both represent the same pair )
Question: Please modify the SQL so that we it works for many members
Note; you can only pair a record with another record belonging to the same member
IF OBJECT_ID('tempdb..#T') IS NOT NULL
DROP TABLE #T;
Create table #T(
[Name] [varchar](15)
,[CAR] [VARCHAR](15)
,ID INT IDENTITY(1,1)
);
INSERT INTO #T
([Name],[CAR])
VALUES
( '123','Nissan')
,( '123','Chevy')
,( '123','Toyota')
--,( '456','Chevy')
--,( '456','Toyota')
--,( '456','Subaru')
SELECT
CAST(A.ID as VARCHAR) as AID, CAST(B.ID as VARCHAR) as BID, A.[Name] as AName, A.[CAR] as ACAR,
B.[Name],B.[CAR]
FROM
( Select A.* FROM #t A ) A
,
( Select B.* FROM #t B ) B
WHERE
A.ID <> B.ID
AND
A.ID < B.ID
ORDER BY 1
April 5, 2018 at 7:53 am
Sorry folks! Please ignore this ... I found the solution. It was that simple....
Sorry about my ignorance
IF OBJECT_ID('tempdb..#T') IS NOT NULL
DROP TABLE #T;
Create table #T(
[Name] [varchar](15)
,[CAR] [VARCHAR](15)
,ID INT IDENTITY(1,1)
);
INSERT INTO #T
([Name],[CAR])
VALUES
( '123','Nissan')
,( '123','Chevy')
,( '123','Toyota')
,( '456','Chevy')
,( '456','Toyota')
,( '456','Subaru')
SELECT * FROM
(
SELECT
CAST(A.ID as VARCHAR) as AID, CAST(B.ID as VARCHAR) as BID, A.[Name] as AName, A.[CAR] as ACAR,
B.[Name],B.[CAR]
FROM
( Select A.* FROM #t A ) A
,
( Select B.* FROM #t B ) B
WHERE
A.ID <> B.ID
AND
A.ID < B.ID
)X
WHERE
X.ANAME = X.NAME
April 5, 2018 at 8:15 am
mw_sql_developer - Thursday, April 5, 2018 7:53 AMSorry folks! Please ignore this ... I found the solution. It was that simple....
Sorry about my ignorance
IF OBJECT_ID('tempdb..#T') IS NOT NULL
DROP TABLE #T;
Create table #T(
[Name] [varchar](15)
,[CAR] [VARCHAR](15)
,ID INT IDENTITY(1,1)
);
INSERT INTO #T
([Name],[CAR])
VALUES
( '123','Nissan')
,( '123','Chevy')
,( '123','Toyota')
,( '456','Chevy')
,( '456','Toyota')
,( '456','Subaru')
SELECT * FROM
(
SELECT
CAST(A.ID as VARCHAR) as AID, CAST(B.ID as VARCHAR) as BID, A.[Name] as AName, A.[CAR] as ACAR,
B.[Name],B.[CAR]
FROM
( Select A.* FROM #t A ) A
,
( Select B.* FROM #t B ) B
WHERE
A.ID <> B.ID
AND
A.ID < B.ID
)X
WHERE
X.ANAME = X.NAME
Looked like something fun, so I played with the query a little... it can be shortened... IF OBJECT_ID('tempdb..#T') IS NOT NULL
BEGIN
DROP TABLE #T;
END;
GO
CREATE TABLE #T (
Name varchar(15),
CAR varchar(15),
ID int IDENTITY(1,1)
);
INSERT INTO #T (Name, CAR)
VALUES ( '123','Nissan'),
( '123','Chevy'),
( '123','Toyota'),
( '456','Chevy'),
( '456','Toyota'),
( '456','Subaru');
SELECT
X.AID,
X.BID,
X.AName AS Name,
X.ACAR + ', ' + X.BCAR AS CARS
FROM (
SELECT
CAST(A.ID AS varchar) AS AID,
CAST(B.ID as varchar) as BID,
A.Name AS AName,
A.CAR AS ACAR,
B.Name AS BName,
B.CAR AS BCAR
FROM #t AS A
CROSS APPLY #t AS B
WHERE A.ID < B.ID
) AS X
WHERE X.ANAME = X.BNAME
ORDER BY X.AName;
DROP TABLE #T;
Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)
April 5, 2018 at 8:34 am
sgmunson - Thursday, April 5, 2018 8:15 AMmw_sql_developer - Thursday, April 5, 2018 7:53 AMSorry folks! Please ignore this ... I found the solution. It was that simple....
Sorry about my ignorance
IF OBJECT_ID('tempdb..#T') IS NOT NULL
DROP TABLE #T;
Create table #T(
[Name] [varchar](15)
,[CAR] [VARCHAR](15)
,ID INT IDENTITY(1,1)
);
INSERT INTO #T
([Name],[CAR])
VALUES
( '123','Nissan')
,( '123','Chevy')
,( '123','Toyota')
,( '456','Chevy')
,( '456','Toyota')
,( '456','Subaru')
SELECT * FROM
(
SELECT
CAST(A.ID as VARCHAR) as AID, CAST(B.ID as VARCHAR) as BID, A.[Name] as AName, A.[CAR] as ACAR,
B.[Name],B.[CAR]
FROM
( Select A.* FROM #t A ) A
,
( Select B.* FROM #t B ) B
WHERE
A.ID <> B.ID
AND
A.ID < B.ID
)X
WHERE
X.ANAME = X.NAMELooked like something fun, so I played with the query a little... it can be shortened...
IF OBJECT_ID('tempdb..#T') IS NOT NULL
BEGIN
DROP TABLE #T;
END;
GO
CREATE TABLE #T (
Name varchar(15),
CAR varchar(15),
ID int IDENTITY(1,1)
);
INSERT INTO #T (Name, CAR)
VALUES ( '123','Nissan'),
( '123','Chevy'),
( '123','Toyota'),
( '456','Chevy'),
( '456','Toyota'),
( '456','Subaru');
SELECT
X.AID,
X.BID,
X.AName AS Name,
X.ACAR + ', ' + X.BCAR AS CARS
FROM (
SELECT
CAST(A.ID AS varchar) AS AID,
CAST(B.ID as varchar) as BID,
A.Name AS AName,
A.CAR AS ACAR,
B.Name AS BName,
B.CAR AS BCAR
FROM #t AS A
CROSS APPLY #t AS B
WHERE A.ID < B.ID
) AS X
WHERE X.ANAME = X.BNAME
ORDER BY X.AName;
DROP TABLE #T;
Same thought "Gosh this is noisy":
SELECT
CAST(A.ID as VARCHAR(2)) as AID,
CAST(B.ID as VARCHAR(2)) as BID,
A.[Name] as AName,
A.[CAR] as ACAR,
B.[Name],
B.[CAR]
FROM #t A
INNER JOIN #t B
ON A.[Name] = B.[Name]
AND A.ID < B.ID
AND A.ID <> B.ID
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy