Solved

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

Posted on 2010-08-31
3
743 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 143

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

730 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