joshaxton
asked on
TSQL Combine Multiple Rows Into One Column
I have the below TSQL statement running in SQL Server 2005 which creates a grid of six records. I only want to show 1 record per Contact (name) with the Company condensed into one column with many values instead of separate rows per record. The Company is representative of the user assigned to the contact. In this case, multiple users could be assigned to one contact with some of those user working for the same company. In that case I would only want to show that company once. Additionally, the days elapsed might be different per user assigned. In this case I would want to show the greatest quantity of days elapsed for that one row. I have attached the TSQL code as well as the current records displayed and the desired end result.
I am awarding the maximum points as I am looking for the specific SQL logic for my case rather than a generic example.
TSQL:
Undesired Format:
Desired Format:
I am awarding the maximum points as I am looking for the specific SQL logic for my case rather than a generic example.
TSQL:
SELECT
c.id AS ContactID,
CASE c.middlename
WHEN NULL THEN
c.firstname + ' ' +
c.lastname + ''
ELSE
c.firstname + ' ' +
c.middlename + ' ' +
c.lastname + ''
END AS FullName,
CASE c.phone2
WHEN NULL THEN
c.phone1 + ' | ' +
c.phone2
ELSE
c.phone1
END AS Phone,
c.email,
co.name AS Company,
CASE WHEN cu.date_assigned IS NULL THEN
lstatus.description + '-Not Assigned'
ELSE
lstatus.description + '-Assigned'
END AS Status,
CASE WHEN cu.date_assigned IS NULL THEN
DATEDIFF(dd, cu.date_created, getdate())
ELSE
DATEDIFF(dd, cu.date_assigned, getdate())
END AS DaysElapsed
FROM
contacts c
INNER JOIN
lookups lstatus ON lstatus.id = c.status
INNER JOIN
contacts_users cu ON cu.contact_id = c.id
AND cu.active = 1
INNER JOIN
users_locations ul on ul.user_id = cu.user_id
INNER JOIN
locations l on l.id = ul.location_id
AND l.active = 1
INNER JOIN
companies co on co.id = l.company_id
AND co.active = 1
ORDER BY
c.id
Undesired Format:
Desired Format:
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I ran that SQL and it reduced the records to 3 rows which is great but unfortunately printed "ar Solutions, Inc.,Delta Community CU,Car Solutions, Inc.,Car Solutions, Inc.," in the column for Company. It printed that same line for each record.
change this part:
stuff(
(Select Company + ',' from
(Select c1.ContactID, c1.Company from myCTE as c1
where c1.contactID = myCTE.contactID --<<< add this
group by c1.ContactID, c1.Company ) as C2
for xml path('')
),1,1,''
) as Company
stuff(
(Select Company + ',' from
(Select c1.ContactID, c1.Company from myCTE as c1
where c1.contactID = myCTE.contactID --<<< add this
group by c1.ContactID, c1.Company ) as C2
for xml path('')
),1,1,''
) as Company
found another glitch, this is better:
stuff(
(Select ',' + Company from
(Select c1.ContactID, c1.Company from myCTE as c1
where c1.contactID = myCTE.contactID --<<< add this
group by c1.ContactID, c1.Company ) as C2
for xml path('')
),1,1,''
) as Company
stuff(
(Select ',' + Company from
(Select c1.ContactID, c1.Company from myCTE as c1
where c1.contactID = myCTE.contactID --<<< add this
group by c1.ContactID, c1.Company ) as C2
for xml path('')
),1,1,''
) as Company
p.s.
After that basic structure is working, we should refine it by simplifying the CTE. but first, things first...
After that basic structure is working, we should refine it by simplifying the CTE. but first, things first...
ASKER
Your last query worked wonderfully but what do you mean regarding the simplification of the CTE?
Can't resist a "cleaner" solution. The CTE is working unnecessary overtime. This pulls some of the logic out of the CTE and puts it in main SQL. Again, it's desk checked only, so I hope I haven't introduced any typos.
With myCTE As
(
SELECT
c.*
,co.name as company
,cu.date_assigned
,cu.date_created
,lstatus.description as status
FROM
contacts c
INNER JOIN
lookups lstatus ON lstatus.id = c.status
INNER JOIN
contacts_users cu ON cu.contact_id = c.id
AND cu.active = 1
INNER JOIN
users_locations ul on ul.user_id = cu.user_id
INNER JOIN
locations l on l.id = ul.location_id
AND l.active = 1
INNER JOIN
companies co on co.id = l.company_id
AND co.active = 1
)
Select id as ContactID
,CASE middlename
WHEN NULL THEN
firstname + ' ' +
lastname + ''
ELSE
firstname + ' ' +
middlename + ' ' +
lastname + ''
END AS FullName
,CASE phone2
WHEN NULL THEN
phone1 + ' | ' +
phone2
ELSE
phone1
END AS Phone
,Email
,stuff(
(Select ',' + company from
(Select c1.ID, c1.company from myCTE as c1
where c1.ID = myCTE.ID
group by c1.ID, c1.company ) as C2
for xml path('')
),1,1,''
) as Company
,CASE WHEN date_assigned IS NULL THEN
status + '-Not Assigned'
ELSE
status + '-Assigned'
END AS Status,
,Max(CASE WHEN date_assigned IS NULL THEN
DATEDIFF(dd, date_created, getdate())
ELSE
DATEDIFF(dd, date_assigned, getdate())
END) AS DaysElapsed
from myCTE
Group by ID, company, middlename, firstname, lastname, Phone1, phone2, Email, date_assigned, date_created, status
ORDER BY Id
http://blog.shlomoid.com/2008/11/emulating-mysqls-groupconcat-function.html