SQL Query - Merge two types of objects with the same value

Question

I want to find PC model pairs with the same speed and memory. These pairs are listed only once. Here is my data and desired results:

• desired results: https://i.sstatic.net/TkvWV.png

• data: https://i.sstatic.net/zuog8.png

I did get results but the query is too long, I know there is a shorter way. Hope everyone help me.

Here is my query

DECLARE @FOR INT = 1
DECLARE @SPEED INT
DECLARE @RAM INT
DECLARE @MODEL INT
DECLARE @LIST TABLE(SPEED INT, RAM INT)
DECLARE @LISTMODEL TABLE(MODEL INT)
DECLARE @RESULT TABLE(PC1 INT, PC2 INT)
DECLARE @RESULTREAL TABLE(COUPLE NVARCHAR(20), SPEED INT, RAM INT)
DECLARE @COUNT INT
    WHILE(1=1)
    BEGIN
        IF(NOT EXISTS(SELECT TOP(1) SPEED FROM @LIST))
        BEGIN
            INSERT @LIST(SPEED,RAM)
            SELECT speed,ram
            FROM VW_count
        END
        BREAK
    END
    SET @COUNT = (SELECT COUNT(SPEED) FROM @LIST) 
    WHILE @FOR <= @COUNT
    BEGIN
        SET @SPEED = (SELECT KETQUA.SPEED FROM (SELECT ROW_NUMBER() OVER (ORDER BY SPEED) AS STT, SPEED FROM @LIST) AS KETQUA WHERE KETQUA.STT = 1)
        SET @RAM = (SELECT KETQUA.RAM FROM (SELECT ROW_NUMBER() OVER (ORDER BY RAM) AS STT, RAM FROM @LIST) AS KETQUA WHERE KETQUA.STT = 1)
        IF @SPEED IS NULL
        BEGIN
            BREAK
        END
        ELSE
            BEGIN
                IF(EXISTS(SELECT speed FROM PC WHERE speed = @SPEED AND ram = @RAM))
                BEGIN
                    INSERT @LISTMODEL(MODEL)
                    SELECT model FROM PC WHERE speed = @SPEED AND ram = @RAM
                    INSERT @RESULT(PC1,PC2)
                    SELECT DISTINCT L1.MODEL, L2.MODEL FROM @LISTMODEL AS L1 , @LISTMODEL AS L2
                    INSERT @RESULTREAL(COUPLE,SPEED,RAM)
                    SELECT CONCAT(R1.PC1, ', ', R1.PC2), @SPEED, @RAM FROM @RESULT R1 WHERE R1.PC1 > R1.PC2 OR NOT EXISTS (SELECT * FROM @RESULT R2 WHERE R2.PC1 = R1.PC2 AND R1.PC2 = R2.PC1)
                END
                DELETE @RESULT
                DELETE @LISTMODEL
            END
    SET @FOR = @FOR + 1
    DELETE TOP(1) FROM @LIST 
    CONTINUE
    END
    SELECT * FROM @RESULTREAL

Answer 1

It's normally a bad idea to use procedural statements when you could also use set-based logic.

You could make a SELECT query using a self-join on table [PC] based on the value of the [speed] and [ram] fields, but where the [model] value of the second table is larger than that of the first table. Something like this:

DECLARE @RESULTREAL TABLE(COUPLE NVARCHAR(20), SPEED INT, RAM INT);

INSERT INTO @RESULTREAL
SELECT
  CAST(T1.[model] AS NVARCHAR) + N', ' + CAST(T2.[model] AS NVARCHAR),
  T1.[speed],
  T1.[ram]
FROM
  [PC] AS T1
  INNER JOIN [PC] AS T2 ON
    T2.[speed] = T1.[speed] AND
    T2.[ram] = T1.[ram]
WHERE
  T2.[model] > T1.[model];