Inner join based on comma separated field

Question

I have two tables InvoiceLocations and PCInvoiceLocation with respective data.

i just want a output like following.

Please help.

Code from comments:

declare @@LocationId nvarchar(50)='1,2' 
declare @@sql nvarchar(Max) 
Declare @@Output table(Code nvarchar(10) not null) 
Set @@sql='SELECT Code FROM invoiceLocations WHERE Id IN ('+@LocationId+')' 
insert @@Output(Code) 
exec sp_executesql @sql

yes trying to execute the following query but not working for me — Pramod Thakur, Nov 23 '17 at 19:43
declare @@LocationId nvarchar(50)='1,2' declare @@sql nvarchar(Max) Declare @@Output table(Code nvarchar(10) not null) Set @@sql='SELECT Code FROM invoiceLocations WHERE Id IN ('+@LocationId+')' insert @@Output(Code) exec sp_executesql @sql — Pramod Thakur, Nov 23 '17 at 19:44
SO is not a free coding service. Post your code. If it doesn't work, we can help. — Eric, Nov 23 '17 at 19:48
Don't put your code in comment section. Modify your post. No one is going to read that. — Eric, Nov 23 '17 at 19:49
@PramodThakur - Please post your tried query in your body of the post. — Md. Suman Kabir, Nov 23 '17 at 19:49
I am trying to store locationname in a table variable but i am not able to store that. — Pramod Thakur, Nov 23 '17 at 19:50
@Md.SumanKabir i am not able to edit my question. i have shared what i am trying to do in comment. — Pramod Thakur, Nov 23 '17 at 19:52
Please take that Locations column and normalise it into its own table that has a 1:many relationship with `pcinvoicelocation`. You're making a massive rod for your own back putting csv data into a column, with an expectation that it's going to be simple and fast to work with. This is not how relational databases are supposed to work — Caius Jard, Nov 23 '17 at 20:41
PramodThakur this can be achieved in 3 steps: First split the Locations field into rows, then Join these rows with the InvoiceLocations table to get related values, finally Aggregate the results into on comma seperated filed. I posted a detailed answer, you can take a look — Hadi, Nov 24 '17 at 20:57

Hadi · Answer 1 · 2017-11-27T18:12:56.320

Create Testing Environment

First i used the following command to create the testing environment

CREATE TABLE InvoiceLocations (ID int,CODE varchar(3), VALUE varchar(3));
CREATE TABLE PCInvoiceLOcation (ID int,CategoryID INT, Locations varchar(50),DefaultLocationID int);


INSERT INTO InvoiceLocations(ID,CODE,VALUE)
VALUES(1,'BFC','BFC'),
(2,'BRH','BRH'),
(3,'BRP','BRP'),
(4,'BCC','BCC')

INSERT INTO PCInvoiceLOcation(ID,CategoryID,Locations,DefaultLocationID)
VALUES(1,1,'1,2',1),
(2,2,'2,3',2),
(3,3,'2,1',1),
(4,4,'4',4)

Solution

You can achieve this in 3 steps:

First split the Locations field into rows
Join these rows with the InvoiceLocations table to get related values
Aggregate the results into on comma seperated filed

First split the Locations field into rows

;with tmp(ID,CategoryID,Locations,DefaultLocationID,  DataItem , Data) as (
        select ID,CategoryID,Locations,DefaultLocationID, 
                CAST( LEFT(Locations, CHARINDEX(',',Locations+',')-1) as varchar(5)),
                STUFF(Locations, 1, CHARINDEX(',',Locations+','), '')
        from PCInvoiceLOcation
    union all
        select ID,CategoryID,Locations,DefaultLocationID,  
                CAST(LEFT(Data, CHARINDEX(',',Data+',')-1) AS Varchar(5)),
                STUFF(Data, 1, CHARINDEX(',',Data+','), '')
        from tmp
        where Data > '')

Join these rows with the InvoiceLocations table to get related values

select tmp.ID,CategoryID,Locations,DefaultLocationID,  DataItem , InvoiceLocations.VALUE
from tmp
INNER JOIN InvoiceLocations ON tmp.DataItem = InvoiceLocations.ID

Aggregate the results into on comma seperated filed

Select ID,CategoryID,Locations,DefaultLocationID,  
            STUFF((SELECT ', ' + VALUE
                       FROM Tmp2 AS T3 
                       WHERE T3.ID = tmp2.ID 
                      FOR XML PATH('')), 1, 2, '')
    FROM tmp2 
    GROUP BY ID,CategoryID,Locations,DefaultLocationID

The Whole Query will looks like

;with tmp(ID,CategoryID,Locations,DefaultLocationID,  DataItem , Data) as (
        select ID,CategoryID,Locations,DefaultLocationID, 
                CAST( LEFT(Locations, CHARINDEX(',',Locations+',')-1) as varchar(5)),
                STUFF(Locations, 1, CHARINDEX(',',Locations+','), '')
        from PCInvoiceLOcation
    union all
        select ID,CategoryID,Locations,DefaultLocationID,  
                CAST(LEFT(Data, CHARINDEX(',',Data+',')-1) AS Varchar(5)),
                STUFF(Data, 1, CHARINDEX(',',Data+','), '')
        from tmp
        where Data > '')
    ,tmp2(ID,CategoryID,Locations,DefaultLocationID,  DataItem , VALUE) AS (
        select tmp.ID,CategoryID,Locations,DefaultLocationID,  DataItem , InvoiceLocations.VALUE
        from tmp
        INNER JOIN InvoiceLocations ON tmp.DataItem = InvoiceLocations.ID) 

        Select ID,CategoryID,Locations,DefaultLocationID,  
                STUFF((SELECT ', ' + VALUE
                           FROM Tmp2 AS T3 
                           WHERE T3.ID = tmp2.ID 
                          FOR XML PATH('')), 1, 2, '')
        FROM tmp2 
        GROUP BY ID,CategoryID,Locations,DefaultLocationID

Result:

SQLFiddle Demo

References

Inner join based on comma separated field

1 Answers1

Solution