Merge Rows in Sql

Question

I want to merge rows in SQL server as follows.

Table

PK  F1      F2      F3      Order
A   NULL    NULL    Grapes  2
B   NULL    Fig     NULL    1
C   Apple   Orange  Banana  0

The input and the expected result is as follows.

Input       Expected Result     
2       Apple   Fig    Grapes
1       Apple   Fig    Banana
0       Apple   Orange Banana

How can I do this in sql stored procedure? I prefer to do in Sql other than in code.

Thanks in Advance

Answer 1

If I understand you input right then maybe something like this:

Test data

DECLARE @T TABLE
(
    PK VARCHAR(5),
    F1 VARCHAR(10),
    F2 VARCHAR(10),
    F3 VARCHAR(10),
    [Order] INT
)

INSERT INTO @T
VALUES
    ('A',NULL,NULL,'Grapes',2),
    ('B',NULL,'Fig',NULL,1),
    ('C','Apple','Orange','Banana',0)

Query

;WITH CTE
AS
(   
    SELECT
        ROW_NUMBER() OVER(ORDER BY [Order]) AS RowNbr,
        T.*
    FROM
        @T AS T
)
, CTE2
AS
(
    SELECT
        CTE.RowNbr,
        CTE.PK,
        CTE.F1,
        CTE.F2,
        CTE.F3,
        CTE.[Order]
    FROM
        CTE
    WHERE
        CTE.RowNbr=1
    UNION ALL
    SELECT
        CTE.RowNbr,
        CTE.PK,
        ISNULL(CTE.F1,CTE2.F1),
        ISNULL(CTE.F2,CTE2.F2),
        ISNULL(CTE.F3,CTE2.F3),
        CTE.[Order]
    FROM
        CTE
        JOIN CTE2 ON CTE.RowNbr=CTE2.RowNbr+1

)
SELECT
    CTE2.[Order],CTE2.F1,CTE2.F2,CTE2.F3
FROM
    CTE2
ORDER BY
    CTE2.RowNbr DESC

Result

2   Apple   Fig     Grapes
1   Apple   Fig     Banana
0   Apple   Orange  Banana