Mariam
Reputation: 563
How to concatenate data from a many to many table?
I have 2 tables having many to many relationship as follows
Departments table
╔═══════════╦════════════════╗ ║ deptID ║ DeptName ║ ╠═══════════╬════════════════╣ ║ 1 ║ abc ║ ║ 2 ║ def ║ ║ 3 ║ ghi ║ ╚═══════════╩════════════════╝
the other is Criteria table
╔════════╦════════════════════════╗ ║ id ║ criteria ║ ╠════════╬════════════════════════╣ ║ 1 ║ number of Employees ║ ║ 2 ║ status ║ ║ 3 ║ nature of products ║ ║ . ║ --- ║ ║ . ║ --- ║ ║ . ║ --- ║ ║ . ║ --- ║ ║ . ║ --- ║ ║ . ║ --- ║ ║ . ║ --- ║ ╚════════╩════════════════════════╝
dynamically increasing with many to many relationship in a separate table to give each criteria a value for each department as follows
╔════════╦══════╦═════════════════════╗ ║ DeptID ║ CrID ║ value ║ ╠════════╬══════╬═════════════════════╣ ║ 1 ║ 1 ║ 20 ║ ║ 1 ║ 2 ║ currently active ║ ║ 1 ║ 3 ║ mechanical ║ ║ 1 ║ . ║ ..... ║ ║ 1 ║ . ║ ..... ║ ║ 1 ║ . ║ ..... ║ ║ 2 ║ 1 ║ 40 ║ ║ 2 ║ 2 ║ paused for transfer ║ ║ 2 ║ 3 ║ software ║ ║ 2 ║ . ║ ..... ║ ║ 2 ║ . ║ ..... ║ ║ 2 ║ . ║ ..... ║ ║ 3 ║ 1 ║ 50 ║ ║ 3 ║ 2 ║ heavy duty ║ ║ 3 ║ 3 ║ support ║ ║ 3 ║ . ║ ..... ║ ║ 3 ║ . ║ ..... ║ ║ 3 ║ . ║ ..... ║ ║ ║ ║ ║ ║ . ║ . ║ ..... ║ ║ . ║ . ║ ..... ║ ║ . ║ . ║ ..... ║ ║ ║ ║ ║ ╚════════╩══════╩═════════════════════╝
1) how to get this result
╔═══════╦═════════════╦═════════════════════╦════════════╦═════╦═════╗ ║ name ║ number of ║ status ║ nature of ║ ... ║ ... ║ ║ ║ Employees ║ ║ products ║ ║ ║ ╠═══════╬═════════════╬═════════════════════╬════════════╬═════╬═════╣ ║ abc ║ 20 ║ currently active ║ mechanical ║ ... ║ ... ║ ║ def ║ 40 ║ paused for transfer ║ software ║ ... ║ ... ║ ║ ghi ║ 50 ║ heavy duty ║ support ║ ... ║ ... ║ ║ . ║ .. ║ ... ║ ... ... ║ ║ ║ ║ . ║ .. ║ ... ║ ... ... ║ ║ ║ ║ . ║ .. ║ ... ║ ... ... ║ ║ ║ ╚═══════╩═════════════╩═════════════════════╩════════════╩═════╩═════╝
2) how to get one table as follows
╔══════╦═══════════════════════════════════════════════════════════════════════════════════╗ ║ name ║ notes ║ ╠══════╬═══════════════════════════════════════════════════════════════════════════════════╣ ║ abc ║ number of Employees 20 , status currently active , nature of products mechanical ║ ║ def ║ number of Employees 40 , status paused for transfer , nature of products software ║ ║ ghi ║ number of Employees 50 , status heavy duty, nature of products support ║ ║ ║ ║ ║ ... ║ .................................................. ║ ║ ... ║ .................................................. ║ ║ ... ║ .................................................. ║ ║ ║ ║ ╚══════╩═══════════════════════════════════════════════════════════════════════════════════╝
Upvotes: 0
Views: 52
Answers (1)
Squirrel
Reputation: 24763
(1)
Do you need this result ? this will required the use of Dynamic SQL as the number of Criteria is unknown or not fixed so the number of columns in the result is not fixed
(2)
SELECT d.DeptName,
notes = stuff(n.notes, 1, 1, '')
FROM Departments d
OUTER APPLY
(
SELECT ',' + c.Criteria + ' ' + dc.value
FROM DeptCriteria dc
INNER JOIN Criteria c ON dc.CrID = c.id
WHERE dc.DeptID = d.deptID
ORDER BY c.id
FOR XML PATH('')
) n (notes)
Upvotes: 2
Related Questions
- Concatenate multiple rows from multiple tables
- Join two tables and concatenate multiple rows of a single field
- TSQL Concatenating data from 2 tables
- SQL Concatenation Many to Many
- SQL Server 2008 concatenate rows to column
- Query to concatenate column values from multiple rows SQL Server 2008 R2
- Concatenate Rows into String with Many to Many Joins
- how to concatenate one to many records in sql server
- Concatenation of rows in T-SQL
- SQL Server Concatenate Rows Using Many-To-Many Related Tables