SQL Server : collapse text rows into one or more rows

Question

I am trying to collapse a table maintaining the hierarchy.

I am using SQL Server 2008 R2.

Data looks like this. Id is unique:

ID  Country City        District            Street          amount
------------------------------------------------------------------
1   USA     New York    NULL                NULL            1
1   USA     New York    Manhattan           NULL            1
2   EU      Paris       NULL                NULL            1
2   EU      Paris       arrondissement II   NULL            1
2   EU      Paris       arrondissement II   Rue Vivienne    1
2   EU      Paris       arrondissement IV   NULL            1
2   EU      Paris       arrondissement IV   Rue de Rivoli   1

to this:

ID  Country City      District          Street        amount
------------------------------------------------------------
1   USA     New York  Manhattan         NULL            1
2   EU      Paris     arrondissement II Rue Vivienne    1
2   EU      Paris     arrondissement IV Rue de Rivoli   1

Sample code:

create table DataTab
(
    ID  [varchar](10),
    Country [varchar](10),
    City [varchar](10),
    District [varchar](50),
    Street [varchar](50),
    amount [int]
);

Insert into DataTab
values (1, 'USA', 'New York', NULL, NULL, 1);

Insert into DataTab
values (1, 'USA', 'New York', 'Manhattan', NULL, 1);

Insert into DataTab
values (2, 'EU', 'Paris', NULL, NULL, 1);

Insert into DataTab
values (2, 'EU', 'Paris', 'arrondissement II', NULL, 1);

Insert into DataTab
values (2, 'EU', 'Paris', 'arrondissement II', 'Rue Vivienne', 1);

Insert into DataTab
values (2, 'EU', 'Paris', 'arrondissement IV', NULL, 1);

Insert into DataTab
values (2, 'EU', 'Paris', 'arrondissement IV', 'Rue de Rivoli', 1);

select * from DataTab;

I have tried to solve the problem using CTE but somehow I cannot find a viable solution.

Hope you have time to help.

Answer 1

It might not be the standard way of solving this kind of problem, but try this query:

SELECT ID
    ,Country
    ,City
    ,District
    ,Street
    ,amount
FROM (
    SELECT *
        ,max(score) OVER (PARTITION BY id) max_score
    FROM (
        SELECT *
            ,CASE WHEN Country IS NOT NULL THEN 1 ELSE 0 END + 
             CASE WHEN City IS NOT NULL THEN 1 ELSE 0 END + 
             CASE WHEN District IS NOT NULL THEN 1 ELSE 0 END + 
             CASE WHEN Street IS NOT NULL THEN 1 ELSE 0 END + 
             CASE WHEN amount IS NOT NULL THEN 1 ELSE 0 END score
        FROM DataTab
        ) result_set
    GROUP BY ID
        ,Country
        ,City
        ,District
        ,Street
        ,amount
        ,score
    ) result_set
WHERE score = max_score

You can also find a sample SQLFiddle here.