Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Replace the values from the lookup tables Expand / Collapse
Author
Message
Posted Monday, June 17, 2013 2:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 11:23 AM
Points: 17, Visits: 29
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
Post #1464367
Posted Monday, June 17, 2013 2:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1464379
Posted Tuesday, June 18, 2013 8:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 11:23 AM
Points: 17, Visits: 29
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
Post #1464706
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse