How to set whole column value in a cell with group by

Question

0.00/5 (No votes)

See more:

Hi
I have inner table like

CommentID FirstName
15 Davidson Lincoln
15 JohnFirstNameJoI
15 testf 1
17 Davidson Lincoln
17 fp1
17 JohnFirstNameJoI
17 testf p1
18 Davidson Lincoln
18 html
18 JohnFirstNameJoI
19 Davidson Lincoln
19 html
19 JohnFirstNameJoI

now i want this in following format

CommentID FirstName
15 Davidson Lincoln,JohnFirstNameJoI,testf

17 Davidson Lincoln,fp1,JohnFirstNameJoI p1,testf p1

18 Davidson Lincoln,html,JohnFirstNameJoI

19 Davidson Lincoln,html,JohnFirstNameJoI

I mean i wanna first name in single cell on the basis of commentid.Please provide me any solution.

Thank and Regards
Rahul Sharma

Posted 12-Nov-13 0:08am

Rahul_Pandit

Add a Solution

Comments

Mohan Gopi 12-Nov-13 6:36am

Hi Rahul, can u share me your code.

Rahul_Pandit 12-Nov-13 6:48am

SELECT PT_tblComments.CommentID, tblLogin.FirstName, PT_tblComments.CommentText CommentFRom FROM PT_tblSendToDetails tblSendTo
JOIN BT_tblLogin tblLogin ON UserID = LoginID
JOIN PT_tblComments ON PT_tblComments.CommentID = tblSendTo.CommentID
GROUP BY PT_tblComments.CommentID, tblLogin.FirstName, PT_tblComments.CommentText

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ashish_Agrawal · Accepted Answer · 2013-11-12T02:36:00

Solution 1

You can do that with the help of FOR XML PATH and Stuff.

SQL

SELECT C.CommentID as CommentID,
STUFF(( SELECT ',' + Sub.FirstName as [text()]
        FROM Consumer Sub Where Sub.CommentID = C.CommentID
        FOR XML PATH ('')),1,1,'')
        as FirstNames
From Consumer C

Posted 12-Nov-13 2:36am

Ashish_Agrawal

Comments

Rahul_Pandit 13-Nov-13 0:23am

I already found a solution but your solution is also fine..i accept it Ashish

Ashish_Agrawal 13-Nov-13 10:38am

Thanks Rahul. However, would like know the solution you found.