Query to combine two rows/columns of data into one row/column
Posted on 2010-08-31
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:
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,
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 "