converting ID to column name and also replacing NULL with last known value

Question

TABLE_A

Rev     ChangedBy
-----------------------------
1       A
2       B
3       C

TABLE_B

Rev Words      ID
----------------------------
1 description_1   52
1 history_1         54
2 description_2   52
3 history_2         54

Words column datatype is ntext.

TABLE_C

ID Name
-----------------------------
52 Description
54 History

OUTPUT

Rev ChangedBy Description        History  
------------------------------------------------
1     A   description_1      history_1
2     B   description_2      history_1
3     C   description_2      history_2

Description and History column will have the previous known values if they dont have value for that Rev no. i.e. Since for Rev no. 3 Description does not have an entry in TABLE_B hence the last known value description_2 appears in that column for Rev no. 3 in the output.

This looks like fairly straight forward joins to me. What query have you tried and what is not working about it? — BoltBait, Mar 04 '10 at 19:33
Why does Rev 3 list "description_2" rather than "history_2" in the output? Otherwise, it looks like a join between tables A and B on the rev value... — OMG Ponies, Mar 04 '10 at 19:35
because description column will not have history values. Since Rev 3 does not have any entry related to description in Table_B hence it is showing the last known value which is description_2. I hope i was able to explain. — stackoverflowuser, Mar 04 '10 at 19:41
@BoltBait: I have just started with T-SQL and not sure how to convert the ID field to column name (probably UNPIVOT) and populating NULL with last known value from Table_B. — stackoverflowuser, Mar 04 '10 at 19:43
@BoltBait: I tried the following: select a.Rev, a.ChangedBy, b.Words,c.Name from Table_A as a inner join Table_B as b on a.Rev = b.Rev inner join Table_C as c on b.FId = c.Id But this is not the desired result. — stackoverflowuser, Mar 04 '10 at 19:52

score 0 · Accepted Answer · edited May 23 '17 at 10:32

With help from following posts
PIVOT on Common Table Expression
SQL QUERY replace NULL value in a row with a value from the previous known value

I was able to come up with following query to get the desired output.

    ;WITH CTE_A AS
    (
        SELECT a.Rev, a.ChangedBy, CAST(b.Words as nvarchar(max)) as [Words],c.Name FROM Table_A AS a
        INNER JOIN Table_B AS b ON a.Rev = b.Rev
        INNER JOIN Table_C AS c ON b.FId = c.Id
    ),
    CTE_B AS
    (

    select Rev, ChangedBy, [Description], [History] from
    CTE_A
    PIVOT
    (
        MAX([Words])
        FOR [Name] in ([Description],[History])
    ) as p
    )

select Rev,ChangedBy, 
ISNULL([Description],(select top 1 [Description] from CTE_B where Rev < t.Rev and [Description] is not null order by Rev desc)),
ISNULL([History],(select top 1 [History] from CTE_B where Rev < t.Rev and [History] is not null order by Rev desc))  
from CTE_B t

converting ID to column name and also replacing NULL with last known value

1 Answers1