Sql Combine two cells

Question

This should be an easy one, I hope. Here is my statement:

 Select UP1.PropertyValue as QuestionOption, email +'; ' as QuestionOptionValue   
   from Users 
 INNER JOIN UserProfile UP1 
 on UP1.UserId = Users.UserId AND PropertyDefinitionID = (SELECT PropertyDefinitionID
     FROM ProfilePropertyDefinition Where PropertyName='Committee' and
     PropertyValue='Beach and Recreation' AND PortalID=0 )

And it returns:

QuestionOption            QuestionOptionValue
Beach and Recreation      eamil@gmail.com;
Beach and Recreation      email@AOL.COM;

I want it to return

QuestionOption            QuestionOptionValue
Beach and Recreation      eamil@gmail.com; email@AOL.COM

What do I need to add and where?

In case of SQL-Server look here: http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server — Ocaso Protal, Jan 30 '14 at 10:47
I have this Statement but I need to cut it in half: SELECT 'Beach and Recreation' as QuestionOption, Stuff((SELECT N'; '+U.email FROM dbo.Users AS U LEFT OUTER JOIN(SELECT up.UserID, MAX(CASE WHEN ppd.PropertyName='Committee'THEN up.PropertyValue ELSE''END) AS Committee FROM dbo.UserProfile AS up INNER JOIN dbo.ProfilePropertyDefinition AS ppd ON up.PropertyDefinitionID=ppd.PropertyDefinitionID and ppd.PortalID=0 Group By up.UserID) as upd on U.UserID=upd.UserID Where upd.Committee='Beach and Recreation' FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N'') as QuestionOptionValue — user3223048, Jan 30 '14 at 10:52
possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — Taryn, Jan 30 '14 at 11:07

score 1 · Answer 1 · answered Jan 30 '14 at 10:59

1

In case of MYSQL you can use the following query

Select 
    QuestionOption, GROUP_CONCAT(QuestionOptionValue SEPARATOR ' ') as QuestionOptionValue
FROM  
    Table1
GROUP BY 
    QuestionOption

demo at http://sqlfiddle.com/#!2/db6b5a/2

answered Jan 30 '14 at 10:59

Deepika Janiyani

1,487
9
17

score 0 · Accepted Answer · answered Feb 27 '14 at 03:18

I eneded up figuring it out Here it is:

SELECT 'Beach and Recreation' as QuestionOption, Stuff((SELECT N'; '+U.email FROM 
Users AS U JOIN UserProfile ON U.UserID = UserProfile.UserID  
where UserProfile.PropertyValue='Beach and Recreation'  FOR XML   
PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N'') as QuestionOptionValue 
union SELECT 'CC/Web Page' as QuestionOption, Stuff((SELECT N'; '+U.email FROM Users 
AS U JOIN UserProfile ON U.UserID = UserProfile.UserID  
where UserProfile.PropertyValue='CC/Web Page'  FOR XML PATH(''),TYPE).value('text()    
[1]','nvarchar(max)'),1,2,N'') as QuestionOptionValue union
SELECT 'Civic Association Board' as QuestionOption, Stuff((SELECT N'; '+U.email FROM  
Users AS U JOIN UserProfile ON U.UserID = UserProfile.UserID  
where UserProfile.PropertyValue='Civic Association Board'  FOR XML   
PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N'') as QuestionOptionValue union
SELECT 'Clubhouse Rentals' as QuestionOption, Stuff((SELECT N'; '+U.email FROM Users 
AS U JOIN UserProfile ON U.UserID = UserProfile.UserID  
where UserProfile.PropertyValue='Clubhouse Rentals'  FOR XML 
PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N'') as QuestionOptionValue union
 SELECT 'Fundraising Committee' as QuestionOption, Stuff((SELECT N'; '+U.email FROM 
 Users AS U JOIN UserProfile ON U.UserID = UserProfile.UserID  
 where UserProfile.PropertyValue='Fundraising Committee'  FOR XML 
 PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N'') as QuestionOptionValue   
 Union SELECT 'Lake Preservation' as QuestionOption, Stuff((SELECT N'; 
 '+U.emailFROM     
 Users AS U JOIN UserProfile ON U.UserID = UserProfile.UserID  
 where UserProfile.PropertyValue='Lake Preservation'  FOR XML 
 PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N'') as QuestionOptionValue 
 Union SELECT 'Lifeguard Committee' as QuestionOption, Stuff((SELECT N'; '+U.email 
 FROM Users AS U JOIN UserProfile ON U.UserID = UserProfile.UserID  
 where UserProfile.PropertyValue='Lifeguard Committee'  FOR XML     
 PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N'') as QuestionOptionValue 
 Union SELECT 'Night Watch' as QuestionOption, Stuff((SELECT N'; '+U.email FROM Users 
 AS U JOIN UserProfile ON U.UserID = UserProfile.UserID  
 where UserProfile.PropertyValue='Night Watch'  FOR XML PATH(''),TYPE).value('text()
 [1]','nvarchar(max)'),1,2,N'') as QuestionOptionValue Union
 SELECT 'Volunteer Committee' as QuestionOption, Stuff((SELECT N'; '+U.email FROM 
 Users AS U JOIN UserProfile ON U.UserID = UserProfile.UserID  
 where UserProfile.PropertyValue='Volunteer Committee'  FOR XML 
 PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N'') as QuestionOptionValue

Sql Combine two cells

2 Answers2