locked
Join master data with multiple columns RRS feed

  • Question

  • User1501362304 posted

    Hi all,

    I want to know what is better way to do the work in below case.

    I have 2 tables as below
    MasterData
    ------
    Id     Name
    1      A
    2      B
    3      C

    UserData
    -------
    Id    C1      C2    C3
    1     1,2     1,3   2,3
    2     2,3     1,3   1,2

    I want the result data as below

    UserMaster
    ------
    Id   C1     C2    C3
    1    A,B    A,C   B,C
    2    B,C    A,C   A,B

    I can do this using 3 joins (one for each C's column in UserData) but can anyone please tell what would be appropriate query with least joins and less execution time, if any.

    Thanks

    Sunday, July 19, 2020 5:18 AM

All replies

  • User475983607 posted

    I can do this using 3 joins (one for each C's column in UserData) but can anyone please tell what would be appropriate query with least joins and less execution time, if any.

    You need to fix the design because it is not normalized.  Unfortunately, I can't understand what you are trying to do given the sample data.

    Sunday, July 19, 2020 11:09 AM
  • User77042963 posted

    Normalized table to make your query easy and straight.

    Here is the long version to get what you want with your current design. You need to use recent versions of SQL Server.

    CREATE TABLE MasterData(
       Id INT  NOT NULL PRIMARY KEY 
      ,Name varchar(10)   
    )
    Insert into MasterData values(1,'A'),(2,'B'),(3,'C')
    
    
    CREATE TABLE UserData(
       Id INT  NOT NULL PRIMARY KEY 
      ,C1 varchar(10)   
      ,C2 varchar(10)  
      ,C3 varchar(10)   
    );
    INSERT INTO UserData(Id,C1,C2,C3) VALUES
     (1,'1,2','1,3','2,3')
    ,(2,'2,3','1,3','1,2');
    ;with mycte1 as (
    select id, value from  UserData u
    cross apply string_split(c1,',')  
    )
    ,mycte2 as (
    select id, value from  UserData u
    cross apply string_split(c2,',')  
    )
    ,mycte3 as (
    select id, value from  UserData u
    cross apply string_split(c3,',')  
    )
    
    ,mycte4 as (
    select m.id,Name , 'c1' col from mycte1 m left join MasterData  md on m.value=md.Id
    union all
    select m.id,Name, 'c2' col from mycte2 m left join MasterData  md on m.value=md.Id
    union all
    select m.id,Name, 'c3' col from mycte3 m left join MasterData  md on m.value=md.Id)
    
    ,mycte5 as (
    select id, col, string_agg(Name,',') mergedCell 
     from mycte4
    group by id ,col
    )
    
    select id 
    ,max(Case when col='c1' then mergedCell else null end) 'c1'
    ,max(Case when col='c2' then mergedCell else null end) 'c2'
    ,max(Case when col='c3' then mergedCell else null end) 'c3'
    from mycte5
    group by id
    
    
    drop TABLE UserData,MasterData

    Monday, July 20, 2020 4:05 AM
  • User-18289217 posted

    They are right that you should normalize your database. In the meantime here you go:

    DECLARE @MasterData TABLE(Id int IDENTITY (1,1), Name varchar(50));
    INSERT INTO @MasterData(Name) VALUES('A');
    INSERT INTO @MasterData(Name) VALUES('B');
    INSERT INTO @MasterData(Name) VALUES('C');
    
    DECLARE @UserData TABLE(Id int IDENTITY(1, 1), C1 varchar(50), C2 varchar(50), C3 varchar(50));
    INSERT INTO @UserData(C1, C2, C3) VALUES('1,2', '1,3', '2,3');
    INSERT INTO @UserData(C1, C2, C3) VALUES('2,3', '1,3', '1,2');
    
    SELECT 
         Ud.Id, 
         -- C1
     (SELECT Name FROM @MasterData WHERE Id = SUBSTRING(Ud.C1, 0, CHARINDEX(',', Ud.C1, 0))) + ',' + 
     (SELECT Name FROM @MasterData WHERE Id = SUBSTRING(Ud.C1, CHARINDEX(',', Ud.C1, 0) + 1, LEN(Ud.C1) - CHARINDEX(',', Ud.C1, 0))) AS C1,
         -- C2
     (SELECT Name FROM @MasterData WHERE Id = SUBSTRING(Ud.C2, 0, CHARINDEX(',', Ud.C2, 0))) + ',' + 
     (SELECT Name FROM @MasterData WHERE Id = SUBSTRING(Ud.C2, CHARINDEX(',', Ud.C2, 0) + 1, LEN(Ud.C2) - CHARINDEX(',', Ud.C2, 0))) AS C2,
         -- C3
     (SELECT Name FROM @MasterData WHERE Id = SUBSTRING(Ud.C3, 0, CHARINDEX(',', Ud.C3, 0))) + ',' + 
     (SELECT Name FROM @MasterData WHERE Id = SUBSTRING(Ud.C3, CHARINDEX(',', Ud.C3, 0) + 1, LEN(Ud.C3) - CHARINDEX(',', Ud.C3, 0))) AS C3
    FROM 
        @UserData AS Ud

    P.S. I would create a scalar function to avoid long and not readable code

    Monday, July 20, 2020 4:46 AM
  • User1501362304 posted

    Thanks all but I can not use normalized table. As columns c1, c2 & c3 may contain hundreds of comma separated values and would be updated frequently. And solutions proposed are way lengthy for just 3 columns, I have 10's of such columns. 

    Tuesday, July 21, 2020 3:50 PM
  • User-18289217 posted

    so what do you want us to do now? Did you try to extend the example that I posted in the previous reply? Give it a try and then come back if you get stuck

    Tuesday, July 21, 2020 3:53 PM
  • User77042963 posted

    If you use relational database in not normalized manner, you can see how hard to get a solution with just a few  mark up samples. With hundreds of comma separated values in one column, it is very difficult to work on it efficiently.

    Hope you can figure out a magic way to solve your problem without using normalized principle. Or try not use relational solution altogether.

    Tuesday, July 21, 2020 4:04 PM