Solved

Query to combine two rows/columns of data into one row/column

Posted on 2010-08-31
3
741 Views
Last Modified: 2012-08-14
Hello, I have a view that yields these 3 rows of data.
FirstNameLastName HouseholdIDCustomerID  ProgramName
Joe           Schmoe              76           37      HUD SUP HOUSING
Joe              Schmoe           76              37      CHILD DAY CARE
Martin      VanBuren      73              32      HEAD START FEDERAL      

I want to combine the first two rows into one, so it looks like this:
                                                                          Programs
Joe           Schmoe              76            37      HUD SUP HOUSING, CHILD DAY CARE  

How would I do this?

I tried the stuff command but it yields the same result as the original dataset:

"select CUST.FirstName, CUST.LastName,
CUST.HouseholdID, CUST.CustomerID,
stuff((select ',' + [ProgramName] from CSE_Program PROG
where CUST.ProgramID = PROG.ProgramID AND
CUST.CustomerID = Cust.CustomerID
for XML PATH('')),1,1,'') as Programs, CUST.ProgramID
FROM VW_CustomersWithPrograms CUST "
0
Comment
Question by:dantcho
3 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33569303
this is done with a "group_concat" function, in sql server with FOR XML PATH :
http://blog.shlomoid.com/2008/11/emulating-mysqls-groupconcat-function.html
0
 
LVL 6

Accepted Solution

by:
anushahanna earned 500 total points
ID: 33569434
With same data, see attached code
create table a (FirstName varchar(100), Lastname  varchar(100), HouseholdID  int, CustomerID Int, ProgramName varchar(100))

insert into a
select 'Joe','Schmoe',76,37,'HUD SUP HOUSING' union
select 'Joe','Schmoe',76,37,'CHILD DAY CARE' union
select 'Martin','VanBuren',73,32,'HEAD START FEDERAL'
WITH CustomersWithPrograms AS
(
select FirstName, LastName,HouseholdID, CustomerID,ProgramName FROM a  
)
SELECT DISTINCT FirstName, LastName,HouseholdID, CustomerID,
  NULLIF(   
    STUFF(   
      (SELECT ', ' + ProgramName FROM CustomersWithPrograms WHERE FirstName = Cust.FirstName
AND LastName = Cust.LastName FOR XML PATH('')  
                        ), 1, 2, ''   
    ), ''   
  ) AS AllCust 
FROM CustomersWithPrograms Cust

Open in new window

0
 

Author Closing Comment

by:dantcho
ID: 33569511
Perfect, exactly what I needed.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

815 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

11 Experts available now in Live!

Get 1:1 Help Now