0

I am having a problem write a query that fill in the blank value. I know it sounds confusing so let me demonstrate and explain.

Says, I have a query "Select Name, Serial, TrackNum From Table1" and the result is below:

Table1

Name    Serial    TrackNum
AAA               
AAA     222       T1
BBB     333       T1
BBB     444       T2
BBB     555
BBB     666
CCC     777       T3
CCC     888             
CCC     999  
DDD     998       
EEE     997       T4
EEE     996       
FFF               T5

I am having problem to form a query to produce this result below. You see that Serial# 555 and 666 were inherited "T2" from the row above, and same thing for Serial# 888 and 999. However, part# DDD has serial but not track#. And FFF has not serial but track#.

Name    Serial    TrackNum
AAA               
AAA     222       T1
BBB     333       T1
BBB     444       T2
BBB     555       T2
BBB     666       T2
CCC     777       T3
CCC     888       T3            
CCC     999       T3 
DDD     998       
EEE     997       T4
EEE     996       T4
FFF               T5

Please help and appreciate if you provide sample code.

Milacay
  • 1,407
  • 7
  • 32
  • 56
  • 1
    Are they initially `NULL` values or empty strings? – BJones Aug 03 '16 at 21:04
  • See http://stackoverflow.com/a/710222/2055998 for ideas. – PM 77-1 Aug 03 '16 at 21:11
  • think of extending your initial table with an additional column, representing your calculated related tracknum. You want the trackNum corresponding to the max name and serial number where the trackNum is not null. – Beth Aug 03 '16 at 21:22
  • @bjones, it is not always NULL value initially. – Milacay Aug 03 '16 at 21:31

2 Answers2

1

Would this work for your needs? The caveat to this technique is that the first row for AAA will also display T1 for the TrackNum rather than an empty string/NULL.

DECLARE @Table1 TABLE (Name VARCHAR(3),Serial VARCHAR(3),TrackNum VARCHAR(3))
INSERT @Table1
VALUES   ('AAA','','')
        ,('AAA','222','T1')
        ,('BBB','333','T1')
        ,('BBB','444','T2')
        ,('BBB','555','')
        ,('BBB','666','')
        ,('CCC','777','T3')
        ,('CCC','888','')           
        ,('CCC','999','')  

;WITH c AS (SELECT  Name, 
                    MAX(TrackNum) AS TrackNum
            FROM @Table1
            GROUP BY Name)
SELECT t.Name, t.Serial, CASE 
                            WHEN t.TrackNum IS NULL OR t.TrackNum = ''
                                THEN  CASE
                                        WHEN t.Serial IS NULL OR t.Serial = ''
                                            THEN ''
                                        ELSE c.TrackNum 
                                      END
                            ELSE t.TrackNum 
                            END AS TrackNum
FROM @Table1 t
JOIN c ON t.Name = c.Name

If you want to UPDATE anything this can give you an idea.

;WITH c AS (SELECT  Name, 
                    MAX(TrackNum) AS TrackNum
            FROM @Table1
            GROUP BY Name)

UPDATE t
SET t.TrackNum = c.TrackNum
FROM @Table1 t
JOIN c ON t.Name = c.Name
WHERE t.TrackNum IS NULL OR t.TrackNum = ''

SELECT *
FROM @Table1
BJones
  • 2,450
  • 2
  • 17
  • 25
  • This is very close, but the first row for AAA display "T1" would not work for me. Thank you for putting it together. It seems like the right track to accomplish this. – Milacay Aug 03 '16 at 22:08
  • @Milacay see updated answer. I added to the `CASE` statement. Let me know if that suits your needs. – BJones Aug 03 '16 at 22:23
  • it works great, but one exception that is if first row AAA has a value rather than NULL, then it display "T1" for TrackNum. Is there a way to not display T1 when first row Serial has value? Thanks again. – Milacay Aug 03 '16 at 22:41
  • Nevermind, I got that works now. Just added "WHEN t.TrackNum IS NULL OR t.TrackNum = '' THEN '' " – Milacay Aug 03 '16 at 22:54
0

Assuming those are NULL values:

SELECT Name, Serial,
  CAST(
    SUBSTRING(
      MAX( CAST(Name AS BINARY(4)) + CAST(Serial AS BINARY(4)) ) 
        OVER( ORDER BY Name
              ROWS UNBOUNDED PRECEDING ),
      5, 4)
    AS varchar(5)) AS TrackNum
FROM table

Courtesy of Mr. Ben-Gan, http://sqlmag.com/t-sql/last-non-null-puzzle

TTeeple
  • 2,913
  • 1
  • 13
  • 22