Saeed Hamed Saeed Hamed - 5 months ago 8
SQL Question

How to convert several rows in one row with different selected fields in SQL Server

I have some records that they have a Priority field and I want to select some fields of these records with the highest rank in one record. If the value of the field is null it'd choose the lower priority one.

You can see sample data in the image below: the orange table is the source and the blue one is the result.

enter image description here

UPDATE

CREATE TABLE MyView(Id INT ,[Priority] INT ,[F1] INT ,[F2] INT ,[F3] INT);
INSERT INTO [dbo].[MyView]
VALUES ( 15, 4, NULL, 3, 2 ),
( 20, 3, 2, 1, NULL ),
( 28, 2, NULL, 0, 9 ),
( 36, 1, 5, 1, NULL );
DROP TABLE [dbo].[MyView];


Thank you in advanced.

Answer

Well, I can't think of anything too efficient for this, but try this:

SELECT TOP 1 t.f1,
       (SELECT TOP 1 s.f2 FROM YourTable s
        ORDER BY CASE WHEN s.f2 IS NULL THEN 1 ELSE 0 END,
                 s.priority DESC) as f2,
       (SELECT TOP 1 q.f3 FROM YourTable q
        ORDER BY CASE WHEN q.f3 IS NULL THEN 1 ELSE 0 END,
                 q.priority DESC) as f3
FROM YourTable t
ORDER BY CASE WHEN t.f1 IS NULL THEN 1 ELSE 0 END,
         t.priority DESC