Skip to main content
AI Assist is now on Stack Overflow. Start a chat to get instant answers from across the network. Sign up to save and share your chats.

Return to Answer

Handle special case
Source Link
Lieven Keersmaekers
Lieven Keersmaekers
  • 58.9k
  • 15
  • 117
  • 151
DECLARE @YourTable TABLE ( A VARCHAR(2) , B VARCHAR(2) , C VARCHAR(2) , D VARCHAR(2)) INSERT INTO @YourTable VALUES (NULL, 'd0', 'd0', NULL) INSERT INTO @YourTable VALUES (NULL, 'd0', 'd1', NULL) INSERT INTO @YourTable VALUES (NULL, 'd0', 'd2', 'a0') INSERT INTO @YourTable VALUES ('d0', 'd1', 'd1', NULL) INSERT INTO @YourTable VALUES ('d0', 'd2', 'd2', 'a0') SELECT A, B, C = MIN(C), D FROM @YourTable GROUP BY A, B, D 
SELECT A, B, CASE WHEN MIN(C) = MAX(C) THEN MIN(C) ELSE NULL END, D FROM @YourTable GROUP BY A, B, D 
SELECT A, B, CASE WHEN MIN(COALESCE(C, 'dx')) = MAX(COALESCE(C, 'dx')) THEN MIN(C) ELSE NULL END, D FROM @YourTable GROUP BY A, B, D 
DECLARE @YourTable TABLE ( A VARCHAR(2) , B VARCHAR(2) , C VARCHAR(2) , D VARCHAR(2)) INSERT INTO @YourTable VALUES (NULL, 'd0', 'd0', NULL) INSERT INTO @YourTable VALUES (NULL, 'd0', 'd1', NULL) INSERT INTO @YourTable VALUES (NULL, 'd0', 'd2', 'a0') INSERT INTO @YourTable VALUES ('d0', 'd1', 'd1', NULL) INSERT INTO @YourTable VALUES ('d0', 'd2', 'd2', 'a0') SELECT A, B, C = MIN(C), D FROM @YourTable GROUP BY A, B, D 
SELECT A, B, CASE WHEN MIN(C) = MAX(C) THEN MIN(C) ELSE NULL END, D FROM @YourTable GROUP BY A, B, D 
DECLARE @YourTable TABLE ( A VARCHAR(2) , B VARCHAR(2) , C VARCHAR(2) , D VARCHAR(2)) INSERT INTO @YourTable VALUES (NULL, 'd0', 'd0', NULL) INSERT INTO @YourTable VALUES (NULL, 'd0', 'd1', NULL) INSERT INTO @YourTable VALUES (NULL, 'd0', 'd2', 'a0') INSERT INTO @YourTable VALUES ('d0', 'd1', 'd1', NULL) INSERT INTO @YourTable VALUES ('d0', 'd2', 'd2', 'a0') SELECT A, B, C = MIN(C), D FROM @YourTable GROUP BY A, B, D 
SELECT A, B, CASE WHEN MIN(C) = MAX(C) THEN MIN(C) ELSE NULL END, D FROM @YourTable GROUP BY A, B, D 
SELECT A, B, CASE WHEN MIN(COALESCE(C, 'dx')) = MAX(COALESCE(C, 'dx')) THEN MIN(C) ELSE NULL END, D FROM @YourTable GROUP BY A, B, D
Alternative solution
Source Link
Lieven Keersmaekers
Lieven Keersmaekers
  • 58.9k
  • 15
  • 117
  • 151
DECLARE @YourTable TABLE ( A VARCHAR(2) , B VARCHAR(2) , C VARCHAR(2) , D VARCHAR(2))  INSERT INTO @YourTable VALUES (NULL, 'd0', 'd0', NULL) INSERT INTO @YourTable VALUES (NULL, 'd0', 'd1', NULL) INSERT INTO @YourTable VALUES (NULL, 'd0', 'd2', 'a0') INSERT INTO @YourTable VALUES ('d0', 'd1', 'd1', NULL) INSERT INTO @YourTable VALUES ('d0', 'd2', 'd2', 'a0')   SELECT A, B, C = MIN(C), D FROM @YourTable GROUP BY A, B, D  
SELECT A, B, CASE WHEN MIN(C) = MAX(C) THEN MIN(C) ELSE NULL END, D FROM @YourTable GROUP BY A, B, D 
DECLARE @YourTable TABLE ( A VARCHAR(2) , B VARCHAR(2) , C VARCHAR(2) , D VARCHAR(2))  INSERT INTO @YourTable VALUES (NULL, 'd0', 'd0', NULL) INSERT INTO @YourTable VALUES (NULL, 'd0', 'd1', NULL) INSERT INTO @YourTable VALUES (NULL, 'd0', 'd2', 'a0') INSERT INTO @YourTable VALUES ('d0', 'd1', 'd1', NULL) INSERT INTO @YourTable VALUES ('d0', 'd2', 'd2', 'a0') SELECT A, B, MIN(C), D FROM @YourTable GROUP BY A, B, D 
DECLARE @YourTable TABLE ( A VARCHAR(2) , B VARCHAR(2) , C VARCHAR(2) , D VARCHAR(2)) INSERT INTO @YourTable VALUES (NULL, 'd0', 'd0', NULL) INSERT INTO @YourTable VALUES (NULL, 'd0', 'd1', NULL) INSERT INTO @YourTable VALUES (NULL, 'd0', 'd2', 'a0') INSERT INTO @YourTable VALUES ('d0', 'd1', 'd1', NULL) INSERT INTO @YourTable VALUES ('d0', 'd2', 'd2', 'a0')   SELECT A, B, C = MIN(C), D FROM @YourTable GROUP BY A, B, D  
SELECT A, B, CASE WHEN MIN(C) = MAX(C) THEN MIN(C) ELSE NULL END, D FROM @YourTable GROUP BY A, B, D
Source Link
Lieven Keersmaekers
Lieven Keersmaekers
  • 58.9k
  • 15
  • 117
  • 151

DECLARE @YourTable TABLE ( A VARCHAR(2) , B VARCHAR(2) , C VARCHAR(2) , D VARCHAR(2)) INSERT INTO @YourTable VALUES (NULL, 'd0', 'd0', NULL) INSERT INTO @YourTable VALUES (NULL, 'd0', 'd1', NULL) INSERT INTO @YourTable VALUES (NULL, 'd0', 'd2', 'a0') INSERT INTO @YourTable VALUES ('d0', 'd1', 'd1', NULL) INSERT INTO @YourTable VALUES ('d0', 'd2', 'd2', 'a0') SELECT A, B, MIN(C), D FROM @YourTable GROUP BY A, B, D