CODEWITHSUNDEEP
sql-server-2008t-sqlrow
rvphx
rvphx

Reputation: 2402

Combining two rows where 1 column has a value and another is null

I am trying to combine two rows (duplicates) where either

1) Null in 1st Row and Value in 2nd Row (Or Vice-a-Versa) 2) Null in both the rows.

I need to combine these two rows to produce 1 row where there are either values for that column (if any present in either of the rows) or a null.

For example.

issue

needs to be converted to Result

I have set up SQL Fiddle here for the same data. Please let me know if you need any more information.

So far I have tried using Full Outer Join (which does not work with so many columns having nulls and stuff) and Pivot (which I am not sure is the right fit for this problem or not).

SELECT A.EmployeeID, A.PostEditDate, 
A.In1
, A.Out2, A.In3, A.Out4, A.Out5, A.Out6, A.Out7, A.Out8, A.Out9, A.Out10  FROM #TempTableRV A
full outer JOIN #TempTableRV B
On A.EmployeeID = B.EmployeeID AND A.PostEditDate = B.PostEditDate
where 
A.PostEditDate = '2007-06-19 00:00:00.000' 
AND 
A.EmployeeID = 25690 and A.In1 IS NULL AND B.In1 IS NULL

Upvotes: 3

Views: 8075

Answers (3)

Koushik Nagarajan
Koushik Nagarajan

Reputation: 196

SELECT

EmployeeID,
MAX(PostEditDate) AS PostEditDate,
MAX(In1) AS In1,
MAX(Out2) AS Out2,
MAX(In3) AS In3,
MAX(Out4) AS Out4,
MAX(Out5) AS Out5,
MAX(Out6) AS Out6,
MAX(Out7) AS Out7,
MAX(Out8) AS Out8,
MAX(Out9) AS Out9,
MAX(Out10) AS Out10

FROM

#TempTableRV

GROUP BY
EmployeeID

Upvotes: 3

Anoop
Anoop

Reputation: 369

Try this

SELECT DISTINCT EmployeeId,PostEditDate,
(SELECT In1 FROM TempTableRV WHERE T.EmployeeId=EmployeeId AND T.PostEditDate=PostEditDate AND In1 IS NOT NULL) AS In1, 
(SELECT Out2 FROM TempTableRV WHERE T.EmployeeId=EmployeeId AND T.PostEditDate=PostEditDate AND Out2 IS NOT NULL) AS Out2, 
(SELECT In3 FROM TempTableRV WHERE T.EmployeeId=EmployeeId AND T.PostEditDate=PostEditDate AND In3 IS NOT NULL) AS In3, 
(SELECT Out4 FROM TempTableRV WHERE T.EmployeeId=EmployeeId AND T.PostEditDate=PostEditDate AND Out4 IS NOT NULL) AS Out4,
Out5,Out6,Out7,Out8,Out9,Out10
FROM TempTableRV AS T

You can do this to the remaining columns (Out5,Out6,Out7,Out8,Out9,Out10) if required.

Upvotes: 1

banderkat
banderkat

Reputation: 481

I would create a new temp table with the fixed/matching values, then update it to set the values that are sometimes null.

Upvotes: 0

Related Questions