Myujth

I have a non-normalized table with several columns. I would like to return all columns that have a positive number along with a negative number of the same value.

Example:

ID | Value
-------------
1 | 10
1 | -10
3 | 15
3 | 15
4 | -1
5 | 4

Current Output:

ID | Values
-------------
1 | 10
1 | -10
3 | 15
3 | 15

Desired Output:

ID | Value
-------------
1 | 10
1 | -10

I have made a windows function as seen below that will select absolute values that are the same, but this includes pairs where there are a positive number.

select Count(*) Over (Partition By DVN, [Tran Date], [Reference Number],Description,Vendor, Abs([Maintenance Expense])) As cnt , * 
 From WorkTemp.dbo.Customer2700Combine
 Where [Maintenance Expense] Is Not Null
Order By 1 Desc,DVN, [Tran Date], [Reference Number],Description,Vendor, Abs([NonRental Total])

Not sure if your requirement is by [ID], looking at your example, description and desired output, this is how I would do it:

DROP TABLE IF EXISTS #sopg;

SELECT [ID],
 [VALUE]
INTO #sopg
FROM
(
 SELECT 1 AS ID,
 10 AS VALUE
 UNION
 SELECT 1 AS ID,
 -10 AS VALUE
 UNION
 SELECT 3 AS ID,
 15 AS VALUE
 UNION
 SELECT 3 AS ID,
 15 AS VALUE
 UNION
 SELECT 4 AS ID,
 -1 AS VALUE
 UNION
 SELECT 5 AS ID,
 4 AS VALUE
) x;

-- Assuming that one ID can only have maximum 2 rows (like your example above) and want this by ID

SELECT s.[ID],
 s.[VALUE]
FROM #sopg s
 INNER JOIN
 (
 SELECT ID,
 SUM(VALUE) SumZero
 FROM #sopg
 GROUP BY ID
 HAVING SUM(VALUE) = 0
 ) SumZero ON SumZero.ID = s.ID

 -- Another way, assuming that ID can have more than 2 rows and different values

DROP TABLE IF EXISTS #sopg2;

SELECT [ID],
 [VALUE]
INTO #sopg2
FROM
(
 SELECT 1 AS ID,
 10 AS VALUE
 UNION
 SELECT 1 AS ID,
 -10 AS VALUE
 UNION
 SELECT 1 AS ID,
 -9 AS VALUE
 UNION
 SELECT 3 AS ID,
 15 AS VALUE
 UNION
 SELECT 3 AS ID,
 15 AS VALUE
 UNION
 SELECT 4 AS ID,
 -1 AS VALUE
 UNION
 SELECT 5 AS ID,
 4 AS VALUE
) x

SELECT a.[ID],
 a.[VALUE]
FROM #sopg2 a
INNER JOIN #sopg b ON b.ID = a.ID AND a.VALUE = -b.VALUE