Geek Answers Handbook

Combining multiple rows into one in SQL

In sql server 2008, I have a table that has columns [a], [b], [c], [sort] and 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 so that I get one row as a result, and for each column I get the first (sorted by sorting column) value other than zero. Therefore, my result should be:

 1, 2, 3

Can anyone suggest how to do this? Thanks

+4
source share
1 answer

One of the methods

 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 other

 ;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 @TT 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
+7
source

More articles:


All Articles