0

I have a query which has multiple joins and need case statements for multiple columns to get the correct data. Although I can manage to get the data, it creates a lot of duplicate rows. Seems like 1 row per case statement search. As an example the code is below. Any help will be appreciated.

CREATE TABLE
#TABLE1 (id INT,
VAL INT,
SAMP NVARCHAR(20))

INSERT INTO #TABLE1
VALUES (1,1,'1-1'),(1,2,'1-2'),(1,3,'1-3'),(2,1,'2-1'),(2,2,'2-2'),(3,1,'3-1')

CREATE TABLE #TABLE2 (id INT, VAL INT)
INSERT INTO #TABLE2
VALUES (1,1),(1,2),(1,3),(2,1),(3,1)

SELECT 
CASE WHEN T2.id = 2 AND T1.VAL = T2.VAL THEN T1.SAMP END
AS SAMPLE_2,    
CASE WHEN T2.id = 3 AND T1.VAL = T2.VAL THEN T1.SAMP END
AS SAMPLE_3    
FROM #TABLE1 T1 LEFT JOIN #TABLE2 T2
ON T1.id = T2.id

DROP TABLE #TABLE1
DROP TABLE #TABLE2

Result

-----------------
Sample2 | Sample3
NULL    | NULL
NULL    | NULL
NULL    | NULL
NULL    | NULL
NULL    | NULL
NULL    | NULL
NULL    | NULL
NULL    | NULL
2-1     | NULL
NULL    | NULL
NULL    | 3-1
=====================

Preferred Result would be 
-----------------#
Sample2 | Sample3
2-1     | 3-1
=====================

1 Answer 1

0
SELECT 
MAX(CASE WHEN T2.id = 2 AND T1.VAL = T2.VAL THEN T1.SAMP END)
AS SAMPLE_2,    
MAX(CASE WHEN T2.id = 3 AND T1.VAL = T2.VAL THEN T1.SAMP END)
AS SAMPLE_3    
FROM #TABLE1 T1 LEFT JOIN #TABLE2 T2
ON T1.id = T2.id
SAMPLE_2 SAMPLE_3
2-1 3-1

fiddle

As you have no more column with out aggregation, and you also want only 1 row back, you can use the aggregation as shown above

If you need more rows, you need to add a GROUP BY.

you can add always more sample to the mix all with the aggregation max and they also would show as you wished

2
  • Thanks for the Explanation. That worked very well with other columns added and then added those columns to GROUP BY.
    – Gibbs Khan
    Commented Apr 17 at 16:13
  • geat then accept it
    – nbk
    Commented Apr 17 at 16:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.