none
Split string and get related values from another table query help RRS feed

  • Question

  • Hi All,

    I have 2 table like below.

          Table1                                                  Table2

    Id  Countryname  UpdatedNames                    Countryname  Description                               
    1      IND                                                                  JAP        Japan
             US                                                                  IND         India
              JAP                                                                AFR         Africa
                                                                                  GER         Germany
    2        AFR                                                               Pak         Pakistan                              
             GER                                                                SRI         Srilanka
                PAK                                                            US         UnitedStatesofAmerica
             SRI  

     Table1 column have multiple entries in ID no-1 or Idno-2...etc.. So we have to split these first and join these data with table2 and get those data and append those values to ID no-1 or Idn0-2..etc with multiple entries in Updated names column. its not like 1 to 1 join.

    I am Expecting Like following mean Description needs to update in UpdatedNames column with respected values.                  

    Table1                                                                                                                                                           

    Id  Countryname  UpdatedNames                                         
    1     IND          India                      
          US           UnitedStatesofAmerica                          
          JAP          Japan                       
                                             
    2    AFR         Africa                           
         GER         Germany                        
         PAK         Pakistan                              
         SRI         Srilanka

    do we have any function or Query. Could you please help on this.

    Thanks,

    Srisai


    Tuesday, November 19, 2019 10:57 AM

All replies

  • Create Table #State (id int, Countryname varchar (30), UpdateNames varchar(30))
    Create Table #State2 (Countryname varchar (30), UpdateNames varchar(30))
    
    	 
    		 
    		 
    		 Insert into #State values 
    		 (1,'IND',null),
    		 (2,'US',null), 
    		 (3,'JAP',null),
    		 (4,'AFR',null),
    		 (5,'GER',null),
    		 (6,'PAK',null),
    		 (7,'SRI',null)
    
    		 Insert into #State2 values
    		 ('JAP', 'Japan'),
    		 ('IND', 'India'),
    		 ('AFR', 'Africa'),
    		 ('GER', 'Germany'),
    		 ('PAK', 'Pakistan'),
    		 ('SRI', 'Shrilanka'),
    		 ('US', 'UnitedStatesofAmerica')
    
    
    		 Update #State set #state.UpdateNames = #State2.UpdateNames from #State  inner join #State2  on #State.Countryname=#State2.Countryname
    Please mark as answer if this post helped you
    Tuesday, November 19, 2019 11:29 AM
  • UPDATE tb1 set UpdatedNames  =  tb2.Description     FROM tb1 JOIN tb2  ON tb1.Countryname  =tb2.Countryname  


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, November 19, 2019 11:30 AM
    Answerer
  • Hi Srisai4me,

    Please check UPDATE (Transact-SQL). And it will provide you an example . Please try .

    IF OBJECT_ID ('dbo.Table1', 'U') IS NOT NULL  
        DROP TABLE dbo.Table1;  
    GO  
    IF OBJECT_ID ('dbo.Table2', 'U') IS NOT NULL  
        DROP TABLE dbo.Table2;  
    GO  
    CREATE TABLE dbo.Table1  
        (c1 int PRIMARY KEY NOT NULL, c2 int NOT NULL);  
    GO  
    CREATE TABLE dbo.Table2  
        (d1 int PRIMARY KEY NOT NULL, d2 int NOT NULL);  
    GO  
    INSERT INTO dbo.Table1 VALUES (1, 10);  
    INSERT INTO dbo.Table2 VALUES (1, 20), (2, 30);  
    GO  
    DECLARE abc CURSOR LOCAL FOR  
        SELECT c1, c2   
        FROM dbo.Table1;  
    OPEN abc;  
    FETCH abc;  
    UPDATE dbo.Table1   
    SET c2 = c2 + d2   
    FROM dbo.Table2   
    WHERE CURRENT OF abc;  
    GO  
    SELECT c1, c2 FROM dbo.Table1;  
    GO  

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 20, 2019 7:05 AM
  • Thanks for the reply.

    But This will not work. because Table1 column have multiple entries in ID no-1 or Idno-2...etc.. So we have to split these first and join these data with table2 and get those data and append those values to ID no-1 or Idn0-2..etc with multiple entries in Updated names column. its not like 1 to 1 join.

    Wednesday, November 20, 2019 7:09 AM
  • Hi Diegoctn,

    Thanks for the reply. But This will not work. because Table1 column have multiple entries in ID no-1or Idno-2...etc.. So we have to split these first and join these data with table2 and get those data and appendthose values to ID no-1 or Idn0-2..etc with multiple entriesinUpdated names column. its not like 1 to 1 join.

    Wednesday, November 20, 2019 7:14 AM
  • Hi ,

    Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …) along with your expected result? So that we’ll get a right direction and make some test.

     

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 20, 2019 7:39 AM
  • Hi Rachel,

    Table 1 : Create Script

    Create Table Table1(Id INT NOT NULL PK, CountryName Varchar(100) NULL,UpdatedName Varchar(max) NULL )

    Insert into Table1 Values

    ( 1,'IND US Japan',NULL)

    ( 2,'AFR GER Pak SRi',NULL)

    Table2 :  CREATE Table Table2 ( CountryNames Nvarchar(100),Description Nvarcchar(Max)

             Insert into Table2 values
                ('JAP', 'Japan'),
                  ('IND', 'India'),
      
                 ('AFR', 'Africa'),
                   ('GER', 'Germany'),
                   ('PAK', 'Pakistan'),
                   ('SRI', 'Shrilanka'),
      
                  ('US', 'UnitedStatesofAmerica')

    I am expected My table1 shpuld be like following.

    Table1 :                                                                                                                                                          

    Id  Countryname                               UpdatedNames (description of Table1)                                       
    1     IND  US   JAP                            India  UnitedStatesofAmerica  Japan                                                                                   
                                          
    2      AFR  GER  PAK SRI                   Africa   Germany  Pakistan   Srilanka                                                                              

    Please let me know any other information .

    Thanks


    • Edited by Srisai4me Wednesday, November 20, 2019 9:53 AM
    Wednesday, November 20, 2019 9:48 AM
  • with cte
    as
    (
     select id,UpdatedName,value from Table1 cross apply string_split(CountryName,' ') as d
    ) select id, value,
    Description as UpdatedName  from cte join Table2 on cte.value=Table2.CountryNames

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, November 20, 2019 10:05 AM
    Answerer
  • Create a new table  as below

    select id,UpdatedName,value into NewTable1 from Table1 cross apply string_split(CountryName,' ') as d

    update NewTable1  set UpdatedName=Description  from NewTable1 join Table2 on NewTable1.value=Table2.CountryNames
    select * from NewTable1


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, November 20, 2019 10:06 AM
    Answerer
  • Your table1 design  is wrong. Please fix it before you continue your development. 

    You can find query to get what you want but why waste time on it as your solution for now.

    Wednesday, November 20, 2019 3:46 PM
    Moderator
  • DECLARE @T1 TABLE (
    	Id INT NOT NULL, 
    	CountryNames VARCHAR(100) NULL,
    	UpdatedName VARCHAR(max) NULL 
    );
    
    INSERT INTO @T1 VALUES 
    (1, 'IND US JAP', NULL), 
    (2, 'AFR GER PAK SRI', NULL);
    
    DECLARE @T2 TABLE (
    	CountryName NVARCHAR(100),
    	Description NVARCHAR(Max)
    );
    
    INSERT INTO @T2 VALUES
    ('JAP', 'Japan'),
    ('IND', 'India'),
    ('AFR', 'Africa'),
    ('GER', 'Germany'),
    ('PAK', 'Pakistan'),
    ('SRI', 'Shrilanka'),
    ('US', 'UnitedStatesofAmerica');
    
    WITH CTE_1 AS (
    	SELECT ID, value AS CountryName
    	FROM @T1
    	CROSS APPLY STRING_SPLIT(CountryNames, SPACE(1))
    ),
    CTE_2 AS (
    	SELECT c1.Id, c1.CountryName, t2.Description
    	FROM CTE_1 AS c1
    	INNER JOIN @T2 AS t2 ON t2.CountryName = c1.CountryName
    )
    
    SELECT 
    	c2.Id, 
    	STUFF(
    		(
    			SELECT ' ' + CountryName 
    			FROM CTE_2 
    			WHERE Id = c2.Id 
                FOR XML PATH(''), TYPE
    		).value('.', 'NVARCHAR(MAX)'),
    		1,
    		1,
    		''
    	) AS CountryNames,
    	STUFF(
    		(
    			SELECT ' ' + Description 
    			FROM CTE_2 
    			WHERE Id = c2.Id 
                FOR XML PATH(''), TYPE
    		).value('.', 'NVARCHAR(MAX)'),
    		1,
    		1,
    		''
    	) AS Descriptions
    FROM CTE_2 AS c2
    GROUP BY c2.Id;


    A Fan of SSIS, SSRS and SSAS

    Wednesday, November 20, 2019 4:25 PM