Gareth_Pointon
asked on
Concatenate SQL rows in to one column
Hi,
I have two tables.
First holds the singe row information to be displayed.
JobID - Company - Site - Contact
11 - ASB - site1- gazz
22 - HSA - site2 - Joe
Second can either have one or more rows based on the filter from the first table.
JobID - first name - surname
11 - Greg - Potter
11 - Frank - Harrys
22 - Dom - Davies
What I would like to happen is this:
JobID - Company - Site - Contact - Engineer
11 - ASB - site1 - gazz - Greg Potter - Frank Harrys
22 - HSA - site2 - Joe - Dom Davies
Can this be done?
I'm pulling this from SQP 2008 and using it in ASP so really need it as a view or something like that as this to be pulled via a Recordset.
Thanks
I have two tables.
First holds the singe row information to be displayed.
JobID - Company - Site - Contact
11 - ASB - site1- gazz
22 - HSA - site2 - Joe
Second can either have one or more rows based on the filter from the first table.
JobID - first name - surname
11 - Greg - Potter
11 - Frank - Harrys
22 - Dom - Davies
What I would like to happen is this:
JobID - Company - Site - Contact - Engineer
11 - ASB - site1 - gazz - Greg Potter - Frank Harrys
22 - HSA - site2 - Joe - Dom Davies
Can this be done?
I'm pulling this from SQP 2008 and using it in ASP so really need it as a view or something like that as this to be pulled via a Recordset.
Thanks
ASKER
Hi,
How do I call this then from ASP?
How do I call this then from ASP?
1. Create the function is SQL
2. Sample ASP
http://www.w3schools.com/asp/
CREATE FUNCTION [dbo].[GetEngineersByJobId]
(
@jobID int
)
RETURNS varchar(max)
AS
BEGIN
declare @output varchar(max);
select @output = COALESCE(@output + ', ', '') + t1.[first name] + ' ' + t1.[surname]
from FirstTable t1
where t1.JobID = @jobID;
return @output;
END
2. Sample ASP
<%
'declare the variables
Dim Connection
Dim ConnString
Dim Recordset
Dim SQL
'define the connection string, specify database driver
ConnString="DRIVER={SQL Server};SERVER=yourServername;UID=yourUsername;" & _
"PWD=yourPassword;DATABASE=yourDatabasename"
'declare the SQL statement that will query the database
SQL = "SELECT JobID, Company, Site, Contact, dbo.GetEngineersByID(JobID) as Engineer FROM SecondTable GROUP BY JobID, Company, Site, Contact"
'create an instance of the ADO connection and recordset objects
Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")
'Open the connection to the database
Connection.Open ConnString
'Open the recordset object executing the SQL statement and return records
Recordset.Open SQL,Connection
'first of all determine whether there are any records
If Recordset.EOF Then
Response.Write("No records returned.")
Else
'if there are records then loop through the fields
Do While NOT Recordset.Eof
Response.write Recordset("JobID")
Response.write Recordset("Company")
Response.write Recordset("Site")
Response.write Recordset("Contact")
Response.write Recordset("Engineer")
Response.write "<br>"
Recordset.MoveNext
Loop
End If
'close the connection and recordset objects to free up resources
Recordset.Close
Set Recordset=nothing
Connection.Close
Set Connection=nothing
%>
http://www.w3schools.com/asp/
ASKER
OK.
I'm not sure then if this will work as I have a DMXZone plugin for a calendar. This populates the code based on the Table or Query you select.
I was hoping to just add the engineers names on to the EXP like I'm doing here:
{ fn IFNULL(dbo.tblSite.Site_Na me, 'No Site') } + ' - ' + dbo.[tblJob-Cal].Job_no + ' - ' + dbo.[tblJob-Cal].Company_N ame AS Title_Con,
I'm not sure then if this will work as I have a DMXZone plugin for a calendar. This populates the code based on the Table or Query you select.
I was hoping to just add the engineers names on to the EXP like I'm doing here:
{ fn IFNULL(dbo.tblSite.Site_Na
ASKER
I have seen something like this but dont know if it will add more then one row as one column though:
SELECT RegionID, RegionDescription
, STUFF(
(SELECT ', ' + TerritoryDescription
FROM Territories
WHERE RegionID = R.RegionID
ORDER BY TerritoryDescription
FOR XML PATH('')
)
, 1
, 2
, '') AS Territories FROM Region AS R
SELECT RegionID, RegionDescription
, STUFF(
(SELECT ', ' + TerritoryDescription
FROM Territories
WHERE RegionID = R.RegionID
ORDER BY TerritoryDescription
FOR XML PATH('')
)
, 1
, 2
, '') AS Territories FROM Region AS R
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is what I have done to fic it:
SELECT { fn IFNULL(dbo.tblSite.Site_Name, 'No Site')
} + ' - ' + dbo.[tblJob-Cal].Job_no + ' - ' + dbo.[tblJob-Cal].Company_Name + ' - ' + { fn IFNULL(dbo.qryJob_Cal_Eng_Name_Group.Names, '') } AS Title_Con,
dbo.[tblJob-Cal].Job_no, dbo.[tblJob-Cal].description, dbo.[tblJob-Cal].link, dbo.[tblJob-Cal].type, dbo.[tblJob-Cal].ID, dbo.[tblJob-Cal].start, dbo.[tblJob-Cal].stop,
dbo.[tblJob-Cal].Del_job, dbo.tblSite.Site_Name, dbo.[tblJob-Cal].Company_Name
FROM dbo.qryJob_Cal_Eng_Name_Group RIGHT OUTER JOIN
dbo.[tblJob-Cal] ON dbo.qryJob_Cal_Eng_Name_Group.ID = dbo.[tblJob-Cal].ID LEFT OUTER JOIN
dbo.tblSite ON dbo.[tblJob-Cal].Site_ID = dbo.tblSite.ID
WHERE (dbo.[tblJob-Cal].Del_job = 0)
Have you chosen RIGHT OUTER and then LEFT OUTER joins for any particular reason? I'd do it this way using aliases and you can use ISNULL() in SQL Server.
SELECT
ISNULL(S.Site_Name, 'No Site')
+ ' - ' + Cal.Job_no
+ ' - ' + Cal.Company_Name
+ ' - ' + ISNULL(Grp.Names, '')
AS Title_Con
, Cal.Job_no
, Cal.description
, Cal.link
, Cal.type
, Cal.ID
, Cal.start
, Cal.stop
, Cal.Del_job
, Cal.Site_Name
, Cal.Company_Name
FROM dbo.[tblJob-Cal] AS Cal
LEFT OUTER JOIN dbo.qryJob_Cal_Eng_Name_Group AS Grp
ON Cal.ID = Grp.ID
LEFT OUTER JOIN dbo.tblSite AS S
ON Cal.Site_ID = S.ID
WHERE Cal.Del_job = 0
Which of these tables has the Engineers names?
ASKER
Sorry it didnt past this in also:
SELECT t1.ID, STUFF
((SELECT ', ' + LEFT(t2.firstname, 1) + LEFT(t2.surname, 1)
FROM qryBooked_Engineers t2
WHERE t2.Job_ID = t1.ID FOR XML PATH('')), 1, 2, '') AS Names
FROM [tblJob-Cal] t1
GROUP BY t1.ID;
SELECT t1.ID, STUFF
((SELECT ', ' + LEFT(t2.firstname, 1) + LEFT(t2.surname, 1)
FROM qryBooked_Engineers t2
WHERE t2.Job_ID = t1.ID FOR XML PATH('')), 1, 2, '') AS Names
FROM [tblJob-Cal] t1
GROUP BY t1.ID;
Open in new window