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

Louis01

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
GO

SELECT JobID, Company, Site, Contact, dbo.GetEngineersByID(JobID) as Engineer
FROM SecondTable
GROUP BY JobID, Company, Site, Contact
GO

Open in new window

Gareth_Pointon

ASKER

Hi,

How do I call this then from ASP?

Louis01

1. Create the function is SQL

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

Open in new window

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
%>

Open in new window

http://www.w3schools.com/asp/

Éric Moreau

check http://emoreau.com/Entries/Blogs/2009/10/SQL-Concatenate-values-multiple-from-multiple-lines.aspx

Gareth_Pointon

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_Name, 'No Site') } + ' - ' + dbo.[tblJob-Cal].Job_no + ' - ' + dbo.[tblJob-Cal].Company_Name AS Title_Con,

Gareth_Pointon

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

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gareth_Pointon

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)

Open in new window

PortletPaul

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

Open in new window

Which of these tables has the Engineers names?

Gareth_Pointon

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;