Avatar of Gareth_Pointon
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
Microsoft SQL Server 2008ASPMicrosoft SQL Server

Avatar of undefined
Last Comment
Gareth_Pointon

8/22/2022 - Mon
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

ASKER
Gareth_Pointon

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/
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Éric Moreau

ASKER
Gareth_Pointon

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,
ASKER
Gareth_Pointon

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Gareth_Pointon

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?
ASKER
Gareth_Pointon

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;
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23