Replace the values from the lookup tables

  • Lookup Table 1:

    CREATE TABLE [dbo].[LT1](

    [LT1_ID] [varchar](25) NULL,

    [Desc] [varchar](25) NULL);

    insert into [LT1] ([LT1_ID] ,[Desc]) values ("Cat_Code","Category"),

    ("Prod_Code", "Product")

    Lookup Table 2:

    CREATE TABLE [dbo].[LT2](

    [LT2_ID] [varchar](25) NULL,

    [Val] [varchar](25) NULL);

    insert into [LT2] ([LT2_ID] ,[Val]) values ("Drama__Q__M", "Drama"),

    ("Comedy__Q__M", "Comedy"),

    ("Blue__Q_Ray", "BlueRay")

    I have another table with the below information

    CREATE TABLE [dbo].[History](

    [Content] [varchar](25) NULL);

    insert into [History] ([Content]) values ("Changed Cat_code to Drama__Q__M From Null"),

    ("Changed Product_code to Blue__Q__Ray From Null"),

    ("Assigned to AAA"),

    ("Changed Cat_code to Comedy__Q__M From Drama__Q__M");

    I need to get the lookup values from the lookup tables LT1 and LT2 and replace the respective LT1_ID and LT2_ID value with Desc and Val values from look up tables 1 and 2. the output should look like this

    Changed Category to Drama From Null

    Changed Product to BlueRay From Null

    Assigned to AAA

    Changed Category to Comedy From Drama

    Can any one please help me in resolving this?

    Thank you

  • sqlsdev (6/17/2013)


    I have 2 lookup tables. I need to get and replace the values from the lookup tables for the below data.

    Lookup Table 1

    LT_ID Desc

    Cat_Code Category

    Prod_Code Product

    Lookup Table 2

    LT_ID Desc

    Drama__Q__M Drama

    Comedy__Q__M Comedy

    Blue__Q_Ray BlueRay

    I have another table with the below information

    1 Changed Cat_code to Drama__Q__M From Null

    2 Changed Product_code to Blue__Q__Ray From Null

    3 Changed Cat_code to Comedy__Q__M From Drama__Q__M

    from the above I need an output like below

    1 Changed Category to Drama From Null

    2 Changed Product to BlueRay From Null

    3 Changed Category to Comedy From Drama

    Can anyone please provide the solution for this?

    Thank you in advance

    In order to help we will need a few things:

    1. Sample DDL in the form of CREATE TABLE statements

    2. Sample data in the form of INSERT INTO statements

    3. Expected results based on the sample data

    Please take a few minutes and read the first article in my signature for best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Lookup Table 1:

    CREATE TABLE [dbo].[LT1](

    [LT1_ID] [varchar](25) NULL,

    [Desc] [varchar](25) NULL);

    insert into [LT1] ([LT1_ID] ,[Desc]) values ("Cat_Code","Category"),

    ("Prod_Code", "Product")

    Lookup Table 2:

    CREATE TABLE [dbo].[LT2](

    [LT2_ID] [varchar](25) NULL,

    [Val] [varchar](25) NULL);

    insert into [LT2] ([LT2_ID] ,[Val]) values ("Drama__Q__M", "Drama"),

    ("Comedy__Q__M", "Comedy"),

    ("Blue__Q_Ray", "BlueRay")

    I have another table with the below information

    CREATE TABLE [dbo].[History](

    [Content] [varchar](25) NULL);

    insert into [History] ([Content]) values ("Changed Cat_code to Drama__Q__M From Null"),

    ("Changed Product_code to Blue__Q__Ray From Null"),

    ("Assigned to AAA"),

    ("Changed Cat_code to Comedy__Q__M From Drama__Q__M");

    I need to get the lookup values from the lookup tables LT1 and LT2 and replace the respective LT1_ID and LT2_ID value with Desc and Val values from look up tables 1 and 2. the output should look like this

    Changed Category to Drama From Null

    Changed Product to BlueRay From Null

    Assigned to AAA

    Changed Category to Comedy From Drama

    Can any one please help me in resolving this?

    Thank you

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply