Solved

TSQL Combine Multiple Rows Into One Column

Posted on 2010-08-30
8
1,293 Views
Last Modified: 2012-05-10
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:
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

Open in new window


Undesired Format:
Undesired Format
Desired Format:
Desired Format
0
Comment
Question by:joshaxton
  • 5
  • 2
8 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33558822
0
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 33559300
Try this (desk checked only)

With myCTE As
(
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
)
Select
ContactID, FullName, Phone, Email

,stuff(
(Select Company + ','  from
   (Select c1.ContactID, c1.Company from myCTE as c1 group by c1.ContactID, c1.Company ) as C2
 for xml path('')
),1,1,''
) as Company

,Status
,Max(DaysElapsed) as DaysElapsed
from myCTE
Group by ContactID, FullName, Phone, Email, Status
ORDER BY ContactId
0
 

Author Comment

by:joshaxton
ID: 33559927
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.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 33560230
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




0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 42

Expert Comment

by:dqmq
ID: 33560246
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
0
 
LVL 42

Expert Comment

by:dqmq
ID: 33560270
p.s.
After that basic structure is working, we should refine it by simplifying the CTE.  but first, things first...
0
 

Author Comment

by:joshaxton
ID: 33560397
Your last query worked wonderfully but what do you mean regarding the simplification of the CTE?
0
 
LVL 42

Expert Comment

by:dqmq
ID: 33562814
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 

Open in new window

0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

867 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now