Geek Answers Handbook

Attempt to combine rows into a single row with certain conditions

For two or more lines that are selected for combining, one of them is identified as a template line. Other rows should combine their data into any nullable columns that the template has.

Sample data:

Id  Name     Address          City          State   Active  Email             Date
1   Acme1    NULL             NULL          NULL    NULL    blah@yada.com     3/1/2011
2   Acme1    1234 Abc Rd      Springfield   OR      0       blah@gmail.com    1/12/2012
3   Acme2    NULL             NULL          NULL    1       blah@yahoo.com    4/19/2012

Say that the user has selected the line with Id 1 as the template line, and the lines with identifiers 2 and 3 should be combined into line 1 and then deleted. Any columns of zero value in row Id 1 must be filled (if any) with the most recent (see Date Column) nonzero value, and nonzero values โ€‹โ€‹already present in row Id 1 must be left as is. The result of this request for the above data should be exactly this:

Id  Name     Address          City          State   Active  Email             Date
1   Acme1    1234 Abc Road    Springfield   OR      1       blah@yada.com     3/1/2011

, Active 1, 0, Id 3 .

P.S. , / , ? , , , . , ?

+5
4

, , desc. . . max(), ( ). , .

; with rows as (
    select test.*,
  -- Template row must be last - how do you decide which one is template row?
  -- In this case template row is the one with id = 1
    row_number() over (order by case when id = 1 then 1 else 0 end,
                       date) rn
    from test
  -- Your list of rows to merge goes here
  -- where id in ( ... )
),
-- Finding first occupied row per column
positions as (
  select
    max (case when Name is not null then rn else 0 end) NamePosition,
    max (case when Address is not null then rn else 0 end) AddressPosition,
    max (case when City is not null then rn else 0 end) CityPosition,
    max (case when State is not null then rn else 0 end) StatePosition,
    max (case when Active is not null then rn else 0 end) ActivePosition,
    max (case when Email is not null then rn else 0 end) EmailPosition,
    max (case when Date is not null then rn else 0 end) DatePosition
  from rows
)
-- Finally join this columns in one row
select 
  (select Name from rows cross join Positions where rn = NamePosition) name,
  (select Address from rows cross join Positions where rn = AddressPosition) Address,
  (select City from rows cross join Positions where rn = CityPosition) City,
  (select State from rows cross join Positions where rn = StatePosition) State,
  (select Active from rows cross join Positions where rn = ActivePosition) Active,
  (select Email from rows cross join Positions where rn = EmailPosition) Email,
  (select Date from rows cross join Positions where rn = DatePosition) Date
from test
-- Any id will suffice, or even DISTINCT
where id = 1

Sql Fiddle.

EDIT:

- rows.rn = xxxPosition. , .

+2

.

.. DECLARE @templateID INT = 1 .. , .

NOT NULL ( ). - TOP 1 :

SELECT
(SELECT TOP 1 Name FROM DataTab WHERE Name IS NOT NULL AND NOT ID = @templateID ORDER BY Date DESC) AS LatestName,
(SELECT TOP 1 Address FROM DataTab WHERE Address IS NOT NULL AND NOT ID = @templateID ORDER BY Date DESC) AS AddressName
-- add more columns here

CTE (Common Table Expression), UDPATE..

WITH Latest_CTE (CTE_LatestName, CTE_AddressName) -- add more columns here; I like CTE prefix to distinguish source columns from target columns..
AS
-- Define the CTE query.
(
    SELECT
    (SELECT TOP 1 Name FROM DataTab WHERE Name IS NOT NULL AND NOT ID = @templateID ORDER BY Date DESC) AS LatestName,
    (SELECT TOP 1 Address FROM DataTab WHERE Address IS NOT NULL AND NOT ID = @templateID ORDER BY Date DESC) AS AddressName
    -- add more columns here
)
UPDATE
<update statement here (below)>

UPDATE ISNULL - โ€‹โ€‹ - , null

WITH
<common expression statement here (above)>
UPDATE DataTab
SET 
Name = ISNULL(Name, CTE_LatestName), -- if Name is null then set Name to CTE_LatestName else keep Name as Name
Address = ISNULL(Address, CTE_LatestAddress)
-- add more columns here..
WHERE ID = @templateID

- , .

DELETE FROM DataTab WHERE NOT ID = @templateID

?

+1

SQL.

sys.columns sys.tables, , , , , . 0 , , .

+1

. , SQL . , -.

- , , .

CREATE TABLE 
dbo.Dummy 
    (
    [ID] int ,
    [Name] varchar(30),
    [Address] varchar(40) null,
    [City]  varchar(30) NULL,
    [State] varchar(2) NULL,
    [Active] tinyint NULL,
    [Email] varchar(30) NULL,
    [Date] date NULL
    );
--
INSERT dbo.Dummy
VALUES
(
    1, 'Acme1', NULL, NULL, NULL, NULL, 'blah@yada.com', '3/1/2011'
)
,
(
    2, 'Acme1', '1234 Abc Rd', 'Springfield', 'OR', 0, 'blah@gmail.com', '1/12/2012'
)
,
(
    3, 'Acme2', NULL, NULL, NULL, 1, 'blah@yahoo.com', '4/19/2012'
);
DECLARE 
    @TableName nvarchar(128) = 'Dummy',
    @TemplateID int = 1,
    @SetStmtList nvarchar(max) = '',
    @LoopCounter int = 0,
    @ColumnCount int = 0,
    @SQL nvarchar(max) = ''
    ;
--
--Create a table to hold the column names
DECLARE     
    @ColumnList table 
        (
        ColumnID tinyint IDENTITY,
        ColumnName nvarchar(128)
        );
--
--Get the column names
INSERT @ColumnList
(
    ColumnName
)
    SELECT
        c.name
    FROM
        sys.columns AS c
        JOIN
        sys.tables AS t
            ON
                t.object_id = c.object_id
    WHERE
        t.name = @TableName;
--
--Create loop boundaries to build out the SQL statement
SELECT
    @ColumnCount = MAX( l.ColumnID ),
    @LoopCounter = MIN (l.ColumnID )
FROM
    @ColumnList AS l;
--
--Loop over the column names
WHILE @LoopCounter <= @ColumnCount
BEGIN
    --Dynamically construct SET statements for each column except ID (See the WHERE clause)
    SELECT 
        @SetStmtList = @SetStmtList + ',' + l.ColumnName + ' =COALESCE(' + l.ColumnName + ', (SELECT TOP 1 ' + l.ColumnName + ' FROM ' + @TableName + ' WHERE ' + l.ColumnName + ' IS NOT NULL AND ID <> ' + CAST(@TemplateID AS NVARCHAR(MAX )) + ' ORDER BY Date DESC)) '
    FROM 
        @ColumnList AS l
    WHERE 
        l.ColumnID = @LoopCounter
        AND
        l.ColumnName <> 'ID';
--
    SELECT
        @LoopCounter = @LoopCounter + 1;
--
END;

--TESTING - Validate the initial table values
SELECT * FROM dbo.Dummy ;
--
--Get rid of the leading common in the SetStmtList
SET @SetStmtList = SUBSTRING( @SetStmtList, 2, LEN( @SetStmtList ) - 1 );
--Build out the rest of the UPDATE statement
SET @SQL = 'UPDATE ' + @TableName  + ' SET ' + @SetStmtList + ' WHERE ID = ' + CAST(@TemplateID AS NVARCHAR(MAX ))
--Then execute the update
EXEC sys.sp_executesql
    @SQL;
--
--TESTING - Validate the updated table values
SELECT * FROM dbo.Dummy ;
--
--Build out the DELETE statement
SET @SQL = 'DELETE FROM ' + @TableName + ' WHERE ID <> ' + CAST(@TemplateID AS NVARCHAR(MAX ))
--Execute the DELETE
EXEC sys.sp_executesql
    @SQL;
--
--TESTING - Validate the final table values
SELECT * FROM dbo.Dummy; 
--
DROP TABLE dbo.Dummy;
+1

More articles:


All Articles