Sql-server – Two rows having null values merge into one row without null values

sql server

I have fetched the record from table like this:

Name                 Opp                     Bid                  Pro
----------------------------------------------------------------------
Admin                 2                      NULL                 NULL
Pragnya Sonal         7                      NULL                 NULL
Priyanka Debnath      17                     NULL                 NULL
Sanjeev Sasmal        2                      NULL                 NULL
Subrajeet Sahoo       1                      NULL                 NULL
Pragnya Sonal         NULL                   2                    NULL
Pragnya Sonal         NULL                   NULL                 1

But I want to reorder this table in this format:

Admin                 2                      NULL                 NULL
Pragnya Sonal         7                      2                    1
Priyanka Debnath      17                     NULL                 NULL
Sanjeev Sasmal        2                      NULL                 NULL
Subrajeet Sahoo       1                      NULL                 NULL

Can any one help me to solve this, thank you very much in advance

Best Answer

You can use grouping to get only one row per Name. If more than one row for the same Name and column have values you have to decide what to do with those values: do you want to see minimum, maximum, sum, average etc? So for example:

SELECT Name, min(Opp), min(Bid), min(Pro) FROM your_table GROUP BY Name

Tables

-- Original table
CREATE TABLE dbo.History
(
    Effective   datetime NOT NULL,      -- When the value became current
    Product     integer NOT NULL,       -- The product
    Kind        tinyint NOT NULL,       -- The price kind (RRP, special, etc)
    Price       smallmoney NOT NULL,    -- The new price

    CONSTRAINT [PK dbo.History Effective, Product, Kind]
        PRIMARY KEY CLUSTERED
            (Effective, Product, Kind)
);

-- Holding area for duplicates that may be needed in future
CREATE TABLE dbo.HistoryDuplicates
(
    Effective   datetime NOT NULL,
    Product     integer NOT NULL,
    Kind        tinyint NOT NULL,
    Price       smallmoney NOT NULL,

    CONSTRAINT [PK dbo.HistoryDuplicates Product, Kind, Effective]
        PRIMARY KEY CLUSTERED
            (Product, Kind, Effective)
);

Initial Data

CREATE TABLE #NewRows
(
    Effective   datetime NOT NULL,
    Product     integer NOT NULL,
    Kind        tinyint NOT NULL,
    Price       smallmoney NOT NULL,
    [Action]    char(1) NOT NULL DEFAULT 'X',
);

CREATE UNIQUE CLUSTERED INDEX cuq
ON #NewRows (Product, Kind, Effective);

INSERT #NewRows
    (Effective, Product, Kind, Price)
VALUES
    ('2013-04-23T00:23:00', 1234, 1, 1.00),
    ('2013-04-24T00:24:00', 1234, 1, 1.00),
    ('2013-04-25T00:25:00', 1234, 1, 1.50),
    ('2013-04-25T00:25:00', 1234, 2, 2.00),
    ('2013-04-26T00:26:00', 1234, 1, 2.00),
    ('2013-04-27T00:27:00', 1234, 1, 2.00),
    ('2013-04-28T00:28:00', 1234, 1, 1.00);

The first step is to remove any redundancies in the input data, storing the removed data in the new holding table:

-- Remove redundancies in the input set
-- and save in the duplicates table
DELETE NR
OUTPUT
    DELETED.Effective,
    DELETED.Product,
    DELETED.Kind,
    DELETED.Price
INTO dbo.HistoryDuplicates
    (Effective, Product, Kind, Price)
FROM #NewRows AS NR
OUTER APPLY
(
    SELECT TOP (1)
        NR2.Price
    FROM #NewRows AS NR2
    WHERE
        NR2.Product = NR.Product
        AND NR2.Kind = NR.Kind
        AND NR2.Effective < NR.Effective
    ORDER BY
        NR2.Effective DESC
) AS X
WHERE
    EXISTS (SELECT X.Price INTERSECT SELECT NR.Price);

De-duplication execution plan

Classifying input rows

The next step is to decide whether each row in the input table is redundant (w.r.t the History table) or not. The following query sets the Action column of the input set data appropriately:

-- Decide what to do with each row
UPDATE NewRows
SET [Action] = 
    CASE
        WHEN NOT EXISTS (SELECT ExistingRow.Price INTERSECT SELECT NewRows.Price)
            THEN 'I' -- Insert
        WHEN ExistingRow.Price = NewRows.Price
            THEN 'D' -- Duplicate
        ELSE 'X'
    END
FROM #NewRows AS NewRows
OUTER APPLY
(
    SELECT TOP (1)
        H.Price,
        H.Effective
    FROM dbo.History AS H
    WHERE
        H.Product = NewRows.Product
        AND H.Kind = NewRows.Kind
        AND H.Effective <= NewRows.Effective
    ORDER BY
        H.Effective DESC
) AS ExistingRow;

CREATE UNIQUE CLUSTERED INDEX cuq
ON #NewRows 
    ([Action], Product, Kind, Effective) 
WITH (DROP_EXISTING = ON);

Action execution plan

Store Redundant Rows

Now we store the rows identified as redundant to the holding table:

-- Store duplicates
WITH Duplicates AS
(
    SELECT NR.*
    FROM #NewRows AS NR
    WHERE NR.[Action] = 'D'
)
MERGE dbo.HistoryDuplicates AS HD
USING Duplicates AS NR
    ON NR.Product = HD.Product
    AND NR.Kind = HD.Kind
    AND NR.Effective = HD.Effective
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (Product, Kind, Effective, Price) 
    VALUES (Product, Kind, Effective, Price);

Redundant records plan

New History rows

The non-redundant rows are added to the History table:

-- Inserts
WITH Inserts AS
(
    SELECT NR.*
    FROM #NewRows AS NR
    WHERE NR.[Action] = 'I'
)
MERGE dbo.History AS H
USING Inserts AS NR
    ON NR.Product = H.Product
    AND NR.Kind = H.Kind
    AND NR.Effective = H.Effective
    AND NR.Price = H.Price
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (Effective, Product, Kind, Price)
    VALUES (Effective, Product, Kind, Price);

New rows plan

Reinstating redundant records

Adding new records can result in redundant rows needing to be reinstated. The following query identifies qualifying redundant rows and moves them to the History table:

DELETE NextDuplicate
OUTPUT DELETED.Product,
       DELETED.Kind,
       DELETED.Price,
       DELETED.Effective
INTO dbo.History
    (Product, Kind, Price, Effective)
FROM #NewRows AS NR
CROSS APPLY
(
    SELECT TOP (1)
        H.Effective,
        H.Price
    FROM dbo.History AS H
    WHERE
        H.Product = NR.Product
        AND H.Kind = NR.Kind
        AND H.Effective > NR.Effective
    ORDER BY
        H.Effective ASC
) AS NextHistory
CROSS APPLY
(
    SELECT TOP (1)
        HD.Effective,
        HD.Product,
        HD.Kind,
        HD.Price
    FROM dbo.HistoryDuplicates AS HD
    WHERE
        HD.Product = NR.Product
        AND HD.Kind = NR.Kind
        AND HD.Effective > NR.Effective
        AND HD.Effective < NextHistory.Effective
    ORDER BY
        HD.Effective ASC
) AS NextDuplicate
WHERE
    NR.[Action] = 'I'
    AND NOT EXISTS (SELECT NextDuplicate.Price INTERSECT SELECT NR.Price);

Relocate rows plan

Results

History table

╔═════════════════════════╦═════════╦══════╦═══════╗
║        Effective        ║ Product ║ Kind ║ Price ║
╠═════════════════════════╬═════════╬══════╬═══════╣
║ 2013-04-23 00:23:00.000 ║    1234 ║    1 ║ 1.00  ║
║ 2013-04-25 00:25:00.000 ║    1234 ║    1 ║ 1.50  ║
║ 2013-04-25 00:25:00.000 ║    1234 ║    2 ║ 2.00  ║
║ 2013-04-26 00:26:00.000 ║    1234 ║    1 ║ 2.00  ║
║ 2013-04-28 00:28:00.000 ║    1234 ║    1 ║ 1.00  ║
╚═════════════════════════╩═════════╩══════╩═══════╝

History Duplicates table

╔═════════════════════════╦═════════╦══════╦═══════╗
║        Effective        ║ Product ║ Kind ║ Price ║
╠═════════════════════════╬═════════╬══════╬═══════╣
║ 2013-04-24 00:24:00.000 ║    1234 ║    1 ║ 1.00  ║
║ 2013-04-27 00:27:00.000 ║    1234 ║    1 ║ 2.00  ║
╚═════════════════════════╩═════════╩══════╩═══════╝

Processing new data

The preceding steps are quite general. We can process a new batch of rows using exactly the same code. The next script loads the input table with two sample rows, one of which is a duplicate, and the other an example of needing to reinstate a previously-redundant row:

-- If needed
DROP TABLE #NewRows;

CREATE TABLE #NewRows
(
    Effective   datetime NOT NULL,
    Product     integer NOT NULL,
    Kind        tinyint NOT NULL,
    Price       smallmoney NOT NULL,
    [Action]    char(1) NOT NULL DEFAULT 'X',
);

CREATE UNIQUE CLUSTERED INDEX cuq
ON #NewRows (Product, Kind, Effective);

INSERT #NewRows
    (Effective, Product, Kind, Price)
VALUES
    ('2013-04-24 00:24:01.000', 1234, 1, 1.00), -- New duplicate
    ('2013-04-26 00:26:30.000', 1234, 1, 5.00); -- Complex new row

Running the rest of the general script produces this final state:

History:

╔═════════════════════════╦═════════╦══════╦═══════╗
║        Effective        ║ Product ║ Kind ║ Price ║
╠═════════════════════════╬═════════╬══════╬═══════╣
║ 2013-04-23 00:23:00.000 ║    1234 ║    1 ║ 1.00  ║
║ 2013-04-25 00:25:00.000 ║    1234 ║    1 ║ 1.50  ║
║ 2013-04-25 00:25:00.000 ║    1234 ║    2 ║ 2.00  ║
║ 2013-04-26 00:26:00.000 ║    1234 ║    1 ║ 2.00  ║
║ 2013-04-26 00:26:30.000 ║    1234 ║    1 ║ 5.00  ║ <- New
║ 2013-04-27 00:27:00.000 ║    1234 ║    1 ║ 2.00  ║ <- Recovered
║ 2013-04-28 00:28:00.000 ║    1234 ║    1 ║ 1.00  ║
╚═════════════════════════╩═════════╩══════╩═══════╝

History Duplicate:

╔═════════════════════════╦═════════╦══════╦═══════╗
║        Effective        ║ Product ║ Kind ║ Price ║
╠═════════════════════════╬═════════╬══════╬═══════╣
║ 2013-04-24 00:24:00.000 ║    1234 ║    1 ║ 1.00  ║
║ 2013-04-24 00:24:01.000 ║    1234 ║    1 ║ 1.00  ║ <- New duplicate
╚═════════════════════════╩═════════╩══════╩═══════╝

Sql-server – Merge two rows into one row

I just removed the locationcode in select and group by clause so they will join eventually and that solved my problem.

select b.Barcode,a.ItemCode,b.Color,b.Size,b.Class,c.price,
SUM(case when e.description = 'Selling Quantity' and d.LocationCode = e.LocationCode         then 1 else 0 end) as 'Selling Qty',
SUM(case when e.description = 'Stock Quantity' then 1 else 0 end) as 'Stock Quantity'
from masterfile.item a 
left join masterfile.itemdetail b on a.ItemCode = b.ItemCode
left join masterfile.itemprice c on a.ItemCode = c.Itemcode and 
b.Color = c.ItemColor and b.Size = c.ItemSize and b.Class = c.ItemClass
left join masterfile.locationinv d on a.itemcode = d.itemcode and
c.ItemColor = d.Color and c.ItemSize = d.Size and c.ItemClass = d.Class
left join masterfile.location e on d.locationcode = e.locationcode
group by b.Barcode,a.itemcode,b.color,b.size,b.class,c.price