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.

Answer 1

With help from following posts
http://stackoverflow.com/questions/1949372/pivot-on-common-table-expression
http://stackoverflow.com/questions/1345065/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