score:2
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:
References
More questions
- Inner join based on comma separated field
- update a database table field with comma separated list from join
- Comma separated output based on varchar field
- SQL query inner join and group by to get comma separated value without using string_agg()
- Inner join with comma separated column
- SQL Server - join rows into comma separated list
- Join query result to a single line of values separated by comma
- Joining a table based on comma separated values
- Difference between cross apply and inner join based on below example
- How to join comma separated column values with another table as rows
- SQL: Inner Join return one row based on criteria
- SQL inner join on a column based on max value from another column
- Can't figure out how to join tables due to comma separated values
- Child data field aggregated into one comma separated field
- SQL Server - Query to join two tables based on columnName field
- comma separated names based on company ID
- Comma Separated String from Junction Table Inner Query (SQL Server)
- SQL Server join results as comma separated list
- Convert multiple returns from a join into a single comma separated list
- In MSSQL filter rows based on an ID exists in a column as comma separated string
- SQL inner join based on table name pattern
- SQL Inner join on one field when second is null and on second when first is null
- TSQL Splitting Comma Separated Field into Multiple Rows with a Row per user Column
- Join tables with comma separated list
- Is there a way to UPDATE TOP (N) with inner join where N is a field of such inner join?
- SQL Server: Joining in rows via. comma separated field
- Join with comma separated values in SQL Server Compact not working
- Entity Framework query a comma separated field
- Sql Query Join on Comma Separated Value
- SQL Server query inner join with date field doesn't work
More questions with similar tag
- Can't edit in sql database in Visual studio 2013
- T-SQL: Query to eliminate non-unique results
- Azure Database Columns not show up in SQL Management Studio 2014
- Create SQL Server Agent jobs programatically
- Combine two unioned tables?
- Best way to maintain data integrity in a survey database
- Getting count of related tags
- sum three columns in three different tables using union query?
- Entity Framework bulk insert with updating rows from another table
- WAS Liberty: sqljdbc driver not configured for integrated authentication
- Azure data factory V2 copy data issue - error code: 2200 An item with the same key has already been added
- How to insert binary data into MSSQL using PDO
- Best way of storing array on database
- Apache DbUtils : Handling multiple result sets returned from Stored Procedure
- Split pipe lined string into Rows - Split the empty values also in SQL Server
- How to give permission to different Active Directory groups to specific views and stored procedures?
- pandas.read_sql_query() throws TypeError: 'NoneType' object is not iterable
- Spring Isolation Support? MSQL Snapshot Isolation
- How to connect to multiple database servers, display combined results?
- How can I compare two name strings that are formatted differently in SQL Server?
- JDBC connection failed, error: TCP/IP connection to host failed
- Any suggestions for identifying what indexes need to be created?
- Why does delete statement to a table, lock a select statement to the same table -- SQL Server
- Can I change the datatype of the Spark dataframe columns that is being loaded to SQL Server as a table?
- SQL Return all upper case values in a column
- How to reuse a cursor in tsql from the begining
- Converting int primary key to bigint in Sql Server
- Cannot insert the value NULL into column (UPDATE with CASE, SQL Server)
- Very long query execution using C++, Sql Server and ODBC connection
- Should I keep this "GlobalConnection" or create connection for every query?
Source:
stackoverflow.com