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
Gareth_PointonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Louis01Commented:
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

0
Gareth_PointonAuthor Commented:
Hi,

How do I call this then from ASP?
0
Louis01Commented:
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/
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Gareth_PointonAuthor Commented:
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,
0
Gareth_PointonAuthor Commented:
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
0
PortletPaulfreelancerCommented:
stuff( select .... for xml path) will span more than row, but there is a concatenation of names too.
select
     JobID, Company, Site, Contact, Engineer
from Table1
cross apply (select stuff(
               (select '- ' + ([first name] + ' ' + [surname])
                from table2
                where table1.jobID = table2.JobID
                order by [first name], [surname]
                for xml path('')
               ), 1, 2, '')
            ) as ca1 (Engineer)

Open in new window

You can remove line breaks to suit of course.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gareth_PointonAuthor Commented:
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

0
PortletPaulfreelancerCommented:
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?
0
Gareth_PointonAuthor Commented:
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;
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.