Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Concatenate child record fields by parent records in T-SQL?

Posted on 2011-05-02
4
Medium Priority
?
804 Views
Last Modified: 2012-05-11
Hello - I have to concatenate contacts names associated with given clients for use in a mail merge routine. Is there a way to do this in T-SQL (SS 2000)?

Actually, I have to create 2 formats; the first is the typical "cc" format that you would expect in the letter closing, where the contact name is followed by the contact company, indented on a 2nd line:  

cc:   Alex Adams
         The Adams Company
      Bob Barker
         The Barker Group
      Clyde Carter
         Carter & Associates


The second format is just the contact names, separated by commas:

      Alex Adams, Bob Barker, Clyde Carter

There could be hundreds of clients, most with 1 - 3 contacts, some with a maximum of 10

I have done some simple Views, and a couple of simple scalar functions, but this makes my head hurt.

Thanks for any help.
0
Comment
Question by:mlagrange
4 Comments
 
LVL 9

Expert Comment

by:kaminda
ID: 35504089
For comma seperated list you can use something like this

DECLARE @MyStatusList  VARCHAR(MAX)
SET @MyStatusList = ''
SELECT @MyStatusList = ISNULL(@MyStatusList,'') + COULMNNAME + ',' FROM TABLENAME

For the first scenario will this work? Have to try and see

SELECT name + CHAR(10) + companyName FROM TABLENAME

--- CHAR(10) is line feed
0
 
LVL 15

Accepted Solution

by:
Aaron Shilo earned 2000 total points
ID: 35504740
hi

i thing i understand that you need some kind of an hirarchy select.

checkout two demos for sql 2000 / sql 2005

CREATE TABLE #Employees_2000_2005
(
        EmpID INT PRIMARY KEY NOT NULL IDENTITY,
        EmpName VARCHAR(255) NOT NULL,
        Title VARCHAR(255) NOT NULL,
        ReportsTo INT FOREIGN KEY REFERENCES #Employees_2000_2005(EmpID) NULL 
)
GO

declare
@CTO int,
@DevManager int,
@TESTManager int,
@DevLead1 int,
@DevLead2 int,
@TESTLead int

INSERT INTO #Employees_2000_2005(EmpName, Title, ReportsTo)
VALUES ('Akram', 'Chief Technology Officer', NULL)
SELECT @CTO = @@IDENTITY

INSERT INTO #Employees_2000_2005(EmpName, Title, ReportsTo)
VALUES ('Ranjit', 'DEV Manager', @CTO)
SELECT @DevManager = @@IDENTITY

INSERT INTO #Employees_2000_2005(EmpName, Title, ReportsTo)
VALUES ('Adil', 'TEST Manager', @CTO)
SELECT @TESTManager = @@IDENTITY

INSERT INTO #Employees_2000_2005(EmpName, Title, ReportsTo)
VALUES ('Chandan', 'DEV Leader', @DevManager)
SELECT @DevLead1 = @@IDENTITY

INSERT INTO #Employees_2000_2005(EmpName, Title, ReportsTo)
VALUES ('Sudeep', 'DEV Leader', @DevManager)
SELECT @DevLead2 = @@IDENTITY

INSERT INTO #Employees_2000_2005(EmpName, Title, ReportsTo)
VALUES ('Ashraf', 'TEST Leader', @TESTManager)
SELECT @TESTLead = @@IDENTITY


INSERT INTO #Employees_2000_2005(EmpName, Title, ReportsTo)
VALUES ('Dheeraj', 'DEV Engineer', @DevLead1)
INSERT INTO #Employees_2000_2005(EmpName, Title, ReportsTo)
VALUES ('Hem', 'DEV Engineer', @DevLead1)
INSERT INTO #Employees_2000_2005(EmpName, Title, ReportsTo)
VALUES ('Gaurav', 'DEV Engineer', @DevLead1)

INSERT INTO #Employees_2000_2005(EmpName, Title, ReportsTo)
VALUES ('Uday', 'DEV Engineer', @DevLead2)
INSERT INTO #Employees_2000_2005(EmpName, Title, ReportsTo)
VALUES ('Shayam', 'DEV Engineer', @DevLead2)

INSERT INTO #Employees_2000_2005(EmpName, Title, ReportsTo)
VALUES ('Mukesh', 'TEST Engineer', @TESTLead)
INSERT INTO #Employees_2000_2005(EmpName, Title, ReportsTo)
VALUES ('Sarfaraz', 'TEST Engineer', @TESTLead)

--After inserting the records as similar to the above tree, we will have 
--a table which will have records something like this



--In SQL Server 2000
--There is no simple way to do this; you will end up writing a recursive stored procedures something as given below or UDF to accomplish this task.

  --SQL Server 2000
--Create a temporary table to hold the intermediate resultset
CREATE TABLE #Emp
(
        EmpID INT,
        EmpName VARCHAR(255),
        Manager VARCHAR(255)
)
GO
--Create a recursive stored procedure(SP) which will take EmpID as 
--an input, recursively traverse the hierarchy to find out all
--child nodes and sub-child nodes and store it in a temporary table
CREATE PROC #EmployeeHierarchy(@EmpID INT)
AS
BEGIN
        DECLARE @LocalEmpID INT, @EmpName VARCHAR(30)
        INSERT #Emp 
        SELECT EmpID, EmpName, (SELECT EmpName FROM #Employees_2000_2005 WHERE EmpID = emp.ReportsTo) AS Manager FROM #Employees_2000_2005 emp WHERE EmpID = @EmpID

        SET @LocalEmpID = (SELECT MIN(EmpID) FROM #Employees_2000_2005 WHERE ReportsTo = @EmpID)

        WHILE @LocalEmpID IS NOT NULL
        BEGIN
                EXEC #EmployeeHierarchy @LocalEmpID
                SET @LocalEmpID = (SELECT MIN(EmpID) FROM dbo.#Employees_2000_2005 WHERE ReportsTo = @EmpID  AND EmpID > @LocalEmpID)
        END
END
GO
--Truncate table before pushing data into using stored procedure
TRUNCATE TABLE #Emp
--Call the above created SP and pass EmpID = 2
EXECUTE #EmployeeHierarchy 2
SELECT * FROM #Emp ORDER BY 1
  
--Truncate table before pushing data into using stored procedure 
TRUNCATE TABLE #Emp 
--Call the above create SP and pass EmpID = 4 this time 
EXECUTE #EmployeeHierarchy 4 

SELECT * FROM #Emp ORDER BY 1 


--SQL Server 2005
--You can replace the recursive stored procedure approach taken above with Common Table Expression (CTE) introduced in SQL Server 2005, it does not only simplify the above approach but also you won't be having nesting level constrained as you had before.

--SQL Server 2005
--Get all employees who directly/indirectly report to EmpID = 2

WITH EmployeeHierarchy(EmpID, EmpName, Manager, [Level])
AS
(
        SELECT emp.EmpID, emp.EmpName, (SELECT EmpName FROM #Employees_2000_2005 WHERE EmpID = emp.ReportsTo) , 1 AS [Level]
        FROM #Employees_2000_2005 AS emp
        --Get all employees who directly/indirectly report to EmpID = 2
        WHERE emp.EmpID = 2
        UNION ALL
        SELECT emp.EmpID, emp.EmpName, Parent.EmpName, Level+1
        FROM #Employees_2000_2005 AS emp
        INNER JOIN EmployeeHierarchy AS Parent ON emp.ReportsTo = parent.EmpID
)
SELECT *
FROM EmployeeHierarchy;


--Get all employees who directly/indirectly report to EmpID = 4
WITH EmployeeHierarchy(EmpID, EmpName, Manager, [Level])
AS
(
        SELECT emp.EmpID, emp.EmpName, (SELECT EmpName FROM #Employees_2000_2005 WHERE EmpID = emp.ReportsTo) , 1 AS [Level]
        FROM #Employees_2000_2005 AS emp
        --Get all employees who directly/indirectly report to EmpID = 4
        WHERE emp.EmpID = 4
        UNION ALL
        SELECT emp.EmpID, emp.EmpName, Parent.EmpName, Level+1
        FROM #Employees_2000_2005 AS emp
        INNER JOIN EmployeeHierarchy AS Parent ON emp.ReportsTo = parent.EmpID
)
SELECT *
FROM EmployeeHierarchy;

GO

Open in new window

0
 

Author Comment

by:mlagrange
ID: 35697222
Thanks, Ashilo - your examples provided a 2000 version. The other examples that were provided used 2005+ things like Row_Number() and OVER (Partition.

Ultimately, I was pointed to an Access SQL query capability I didn't know about (doing a DCount() from within the query itself), and was able to do this from an Access query (below).

Thanks to all for responding.

Mark
(Query name: "_AcctTrsts1")

SELECT tblAcctsRoles.AcctID, tblContsHdrs.NameLFM, tblContsHdrs.NameSFMLPD, DCount("*","_AcctTrsts1","AcctID = " & [AcctID] & " And NameLFM < '" & [NameLFM] & "'")+1 AS Ordr
FROM tblAcctsRoles INNER JOIN tblContsHdrs ON tblAcctsRoles.ContHdrID = tblContsHdrs.ContHdrID
ORDER BY tblAcctsRoles.AcctID, tblContsHdrs.NameLFM;

Open in new window

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question