In sql server 2008, I have a table that has the columns [a],[b],[c],[sort] and it has 4 records:
1, NULL, NULL 0
NULL, 2, NULL 1
NULL, NULL, 3 2
10, NULL, NULL 3
I need to combine all the rows in a way that i get one row as a result, and for each column I get the first (ordered by sort column) non-null value. So my result should be:
1, 2, 3
Can anyone suggest how to do this? Thanks
One way
SELECT (SELECT TOP 1 [a]
FROM @T
WHERE [a] IS NOT NULL
ORDER BY [sort]) AS [a],
(SELECT TOP 1 [b]
FROM @T
WHERE [b] IS NOT NULL
ORDER BY [sort]) AS [b],
(SELECT TOP 1 [c]
FROM @T
WHERE [c] IS NOT NULL
ORDER BY [sort]) AS [c]
Or another
;WITH R
AS (SELECT [a],
[b],
[c],
[sort]
FROM @T
WHERE [sort] = 0
UNION ALL
SELECT Isnull(R.[a], T.[a]),
Isnull(R.[b], T.[b]),
Isnull(R.[c], T.[c]),
T.[sort]
FROM @T T
JOIN R
ON T.sort = R.sort + 1
AND ( R.[a] IS NULL
OR R.[b] IS NULL
OR R.[c] IS NULL ))
SELECT TOP 1 [a],
[b],
[c]
FROM R
ORDER BY [sort] DESC
TOPqueries.