help me with building the SQL statement

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  April 5, 2018 at 7:50 am

  #344584

  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

• Thom A

  SSC Guru

  Points: 99126

  More actions

  April 5, 2018 at 7:53 am

  #1985670

  So, with the extra sample data uncommented, what output are you expecting? Could you explain the logic behind it?

  Thanks.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  April 5, 2018 at 7:53 am

  #1985671

  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

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  April 5, 2018 at 8:15 am

  #1985675

  mw_sql_developer - Thursday, April 5, 2018 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


  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)

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  April 5, 2018 at 8:34 am

  #1985682

  sgmunson - Thursday, April 5, 2018 8:15 AM

  mw_sql_developer - Thursday, April 5, 2018 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


  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;

  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

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  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