I have 3 tables called:
I want to show on a GUI a table of all resource names. In one cell in each row I would like to list out all of the applications (comma separated) of that resource.
So the question is, what is the best way to do this in SQL as I need to get all resources and I also need to get all applications for each resource?
Do I run a select * from resources first and then loop through each resource and do a separate query per resource to get the list of applications for that resource?
Is there a way I can do this in one query?
This question is related to
sql
sql-server
tsql
string-aggregation
I think we could write in the following way to retrieve(below code is just an example, please modify as needed):
Create FUNCTION dbo.ufnGetEmployeeMultiple(@DepartmentID int)
RETURNS VARCHAR(1000) AS
BEGIN
DECLARE @Employeelist varchar(1000)
SELECT @Employeelist = COALESCE(@Employeelist + ', ', '') + E.LoginID
FROM humanresources.Employee E
Left JOIN humanresources.EmployeeDepartmentHistory H ON
E.BusinessEntityID = H.BusinessEntityID
INNER JOIN HumanResources.Department D ON
H.DepartmentID = D.DepartmentID
Where H.DepartmentID = @DepartmentID
Return @Employeelist
END
SELECT D.name as Department, dbo.ufnGetEmployeeMultiple (D.DepartmentID)as Employees
FROM HumanResources.Department D
SELECT Distinct (D.name) as Department, dbo.ufnGetEmployeeMultiple (D.DepartmentID) as
Employees
FROM HumanResources.Department D
I don't know if there's any solution to do this in a database-agnostic way, since you most likely will need some form of string manipulation, and those are typically different between vendors.
For SQL Server 2005 and up, you could use:
SELECT
r.ID, r.Name,
Resources = STUFF(
(SELECT ','+a.Name
FROM dbo.Applications a
INNER JOIN dbo.ApplicationsResources ar ON ar.app_id = a.id
WHERE ar.resource_id = r.id
FOR XML PATH('')), 1, 1, '')
FROM
dbo.Resources r
It uses the SQL Server 2005 FOR XML PATH construct to list the subitems (the applications for a given resource) as a comma-separated list.
Marc
From next version of SQL Server you will be able to do
SELECT r.name,
STRING_AGG(a.name, ',')
FROM RESOURCES r
JOIN APPLICATIONSRESOURCES ar
ON ar.resource_id = r.id
JOIN APPLICATIONS a
ON a.id = ar.app_id
GROUP BY r.name
For previous versions of the product there are quite a wide variety of different approaches to this problem. An excellent review of them is in the article: Concatenating Row Values in Transact-SQL.
Concatenating values when the number of items are not known
Non-reliable approaches
To be agnostic, drop back and punt.
Select a.name as a_name, r.name as r_name
from ApplicationsResource ar, Applications a, Resources r
where a.id = ar.app_id
and r.id = ar.resource_id
order by r.name, a.name;
Now user your server programming language to concatenate a_names while r_name is the same as the last time.
Assuming SQL Server:
Table structure:
CREATE TABLE [dbo].[item_dept](
[ItemName] char(20) NULL,
[DepartmentID] int NULL
)
Query:
SELECT ItemName,
STUFF((SELECT ',' + rtrim(convert(char(10),DepartmentID))
FROM item_dept b
WHERE a.ItemName = b.ItemName
FOR XML PATH('')),1,1,'') DepartmentID
FROM item_dept a
GROUP BY ItemName
Results:
ItemName DepartmentID
item1 21,13,9,36
item2 4,9,44
MySQL
SELECT r.name,
GROUP_CONCAT(a.name SEPARATOR ',')
FROM RESOURCES r
JOIN APPLICATIONSRESOURCES ar ON ar.resource_id = r.id
JOIN APPLICATIONS a ON a.id = ar.app_id
GROUP BY r.name
**
MS SQL Server
SELECT r.name,
STUFF((SELECT ','+ a.name
FROM APPLICATIONS a
JOIN APPLICATIONRESOURCES ar ON ar.app_id = a.id
WHERE ar.resource_id = r.id
GROUP BY a.name
FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
FROM RESOURCES r
GROUP BY deptno;
Oracle
SELECT r.name,
LISTAGG(a.name SEPARATOR ',') WITHIN GROUP (ORDER BY a.name)
FROM RESOURCES r
JOIN APPLICATIONSRESOURCES ar ON ar.resource_id = r.id
JOIN APPLICATIONS a ON a.id = ar.app_id
GROUP BY r.name;
SELECT r.name,
GROUP_CONCAT(a.name SEPARATOR ',')
FROM RESOURCES r
JOIN APPLICATIONSRESOURCES ar ON ar.resource_id = r.id
JOIN APPLICATIONS a ON a.id = ar.app_id
GROUP BY r.name
SELECT r.name,
STUFF((SELECT ','+ a.name
FROM APPLICATIONS a
JOIN APPLICATIONRESOURCES ar ON ar.app_id = a.id
WHERE ar.resource_id = r.id
GROUP BY a.name
FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
FROM RESOURCES r
SELECT r.name,
STRING_AGG(a.name, ',')
FROM RESOURCES r
JOIN APPLICATIONSRESOURCES ar ON ar.resource_id = r.id
JOIN APPLICATIONS a ON a.id = ar.app_id
GROUP BY r.name
I recommend reading about string aggregation/concatentation in Oracle.
Using COALESCE to Build Comma-Delimited String in SQL Server
http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string
Example:
DECLARE @EmployeeList varchar(100)
SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') +
CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1
SELECT @EmployeeList
I believe what you want is:
SELECT ItemName, GROUP_CONCAT(DepartmentId) FROM table_name GROUP BY ItemName
If you're using MySQL
Reference
This will do it in SQL Server:
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Convert(nvarchar(8),DepartmentId)
FROM Table
SELECT @listStr
Source: Stackoverflow.com