Merge row columns from a query in sql server

Question

I have a simple query which returns the following rows :

Current rows:

Empl    ECode   DCode       LCode       Earn    Dedn    Liab
====    ====    =====       =====       ====    ====    ====
123     PerHr   Null        Null        13      0       0
123     Null    Union       Null        0       10      0
123     Null    Per         Null        0       20      0
123     Null    Null        MyHealth    0       0       5
123     Null    Null        401         0       0       10
123     Null    Null        Train       0       0       15
123     Null    Null        CAFTA       0       0       20

However, I needed to see the above rows as follows :

Empl    ECode   DCode   LCode       Earn    Dedn    Liab
====    ====    =====   =====       ====    ====    ====
123     PerHr   Union   MyHealth    13      10      5
123     Null    Per     401         0       20      10
123     Null    Null    Train       0       0       15
123     Null    Null    CAFTA       0       0       20

It's more like merging the succeeding rows into the preceding rows wherever there are Nulls encountered for EarnCode, DednCode & LiabCode. Actually what I wanted to see was to roll up everything to the preceding rows.

In Oracle we had this LAST_VALUE function which we could use, but in this case, I simply cannot figure out what to do with this.

In the example above, ECode's sum value column is Earn, DCode is Dedn, and LCode is Liab; notice that whenever either of ECode, DCode, or LCode is not null, there is a corresponding value in Earn, Dedn, or the Liab columns.

By the way, we are using SQL Server 2008 R2 at work.

Hoping for your advice, thanks.

Answer 1

This is basically the same technique as Tango_Guy does but without the temporary tables and with the sort made explicit. Because the number of rows per Empl is <= the number of rows already in place, I didn't need to make a dummy table for the leftmost table, just filtered the base data to where there was a match amongst the 3 codes. Also, I reviewed your discussion and the Earn and ECode move together. In fact a non-zero Earn in a column without an ECode is effectively lost (this is a good case for a constraint - non-zero Earn is not allowed when ECode is NULL):

http://sqlfiddle.com/#!3/7bd04/3

CREATE TABLE data(ID INT IDENTITY NOT NULL,
                  Empl VARCHAR(3), 
                  ECode VARCHAR(8), 
                  DCode VARCHAR(8), 
                  LCode VARCHAR(8),
                  Earn INT NOT NULL,
                  Dedn INT NOT NULL,
                  Liab INT NOT NULL ) ;

INSERT INTO data (Empl, ECode, DCode, LCode, Earn, Dedn, Liab)
VALUES ('123', 'PerHr', NULL, NULL, 13, 0, 0),
        ('123', NULL, 'Union', NULL, 0, 10, 0),
        ('123', NULL, 'Per', NULL, 0, 20, 0),
        ('123', NULL, NULL, 'MyHealth', 0, 0, 5),
        ('123', NULL, NULL, '401', 0, 0, 10),
        ('123', NULL, NULL, 'Train', 0, 0, 15),
        ('123', NULL, NULL, 'CAFTA', 0, 0, 20);

WITH basedata AS (
    SELECT *, ROW_NUMBER () OVER(ORDER BY ID) AS OrigSort, ROW_NUMBER () OVER(PARTITION BY Empl ORDER BY ID) AS EmplSort
    FROM data
),
E AS (
  SELECT Empl, ECode, Earn, ROW_NUMBER () OVER(PARTITION BY Empl ORDER BY OrigSort) AS EmplSort
  FROM basedata
  WHERE ECode IS NOT NULL
),
D AS (
  SELECT Empl, DCode, Dedn, ROW_NUMBER () OVER(PARTITION BY Empl ORDER BY OrigSort) AS EmplSort
  FROM basedata
  WHERE DCode IS NOT NULL
),
L AS (
  SELECT Empl, LCode, Liab, ROW_NUMBER () OVER(PARTITION BY Empl ORDER BY OrigSort) AS EmplSort
  FROM basedata
  WHERE LCode IS NOT NULL
)
SELECT basedata.Empl, E.ECode, D.Dcode, L.LCode, E.Earn, D.Dedn, L.Liab
FROM basedata
LEFT JOIN E
    ON E.Empl = basedata.Empl AND E.EmplSort = basedata.EmplSort
LEFT JOIN D
    ON D.Empl = basedata.Empl AND D.EmplSort = basedata.EmplSort
LEFT JOIN L
    ON L.Empl = basedata.Empl AND L.EmplSort = basedata.EmplSort
WHERE E.ECode IS NOT NULL OR D.DCode IS NOT NULL OR L.LCode IS NOT NULL
ORDER BY basedata.Empl, basedata.EmplSort

Answer 2

I have a solution, but it is very kludgy. If anyone has something better, that would be great.

However, an algorithm:

1) Get rownumbers for each distinct list of values in the columns
2) Join all columns based on rownumber

Example:

select Distinct ECode   into #Ecode     from source_table   order by rowid;
select Distinct DCode   into #Dcode     from source_table   order by rowid;
select Distinct LCode   into #Lcode     from source_table   order by rowid;
select Distinct Earn    into #Earn  from source_table   order by rowid;
select Distinct Dedn    into #Dedn  from source_table   order by rowid;
select Distinct Liab    into #Liab  from source_table   order by rowid;

select b.ECode, c.DCode, d.LCode, e.Earn, f.Dedn, g.Liab
from source_table   a -- Note:  a source for row numbers that will be >= the below
left outer join #Ecode b on a.rowid = b.rowid
left outer join #DCode c on a.rowid = c.rowid
left outer join #LCode d on a.rowid = d.rowid
left outer join #Earn  e on a.rowid = e.rowid
left outer join #Dedn  f on a.rowid = f.rowid
left outer join #Liab  g on a.rowid = g.rowid
where 
    b.ecode is not null or
    c.dcode is not null or
    d.lcode is not null or
    e.earn is not null or
    f.dedn is not null or
    g.liab is not null;

I didn't include Empl, since I don't know what role you want it to play. If this is all true for a given Empl, then you could just add it, join on it, and carry it through.

I don't like this solution at all, so hopefully someone else will come up with something more elegant.

Best, David

Answer 3

Not sure if it is what you need but have you tried coalesc

SELECT Name, Class, Color, ProductNumber,
COALESCE(Class, Color, ProductNumber) AS FirstNotNull
FROM Production.Product ;