how to concatenate column values into single text in sql?

Question

0.00/5 (No votes)

See more:

i have one table

like ex:

empcode date
4779 15-08-2015
4779 20-08-2015
1294 15-08-2015
6429 15-08-2015
5268 10-08-2015
5268 11-08-2015
5268 12-08-2015

i want data like

empcode date

4779 15,20
1294 15
6429 15
5268 10,11,12

empcode 4779 having nof days in one cell...pls tell me this very urgent..

Posted 24-Aug-15 19:21pm

Member 11337367

Add a Solution

3 solutions

Solution 1

Use Pivoting. Here is an example.

Simple Way To Use Pivot In SQL Query[^]

Posted 24-Aug-15 19:44pm

debashishPaul

Comments

Member 11337367 25-Aug-15 1:50am

please give query to make that..

Solution 3

Hi,

Use Stuff or Coalesce to get the same output that you want.

Please try with below query using STUFF.

SQL

SELECT DISTINCT empcode,
  STUFF((SELECT distinct ',' + Cast(Day([Date]) AS varchar)
         FROM [Table] p1
         WHERE p.empcode= p1.empcode
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'') as DateConcate
FROM [Table] p;

Please let me know if you have any concern or query on this or if you need more information on this.

Thanks

Posted 24-Aug-15 19:55pm

Advay Pandya

Updated 24-Aug-15 19:58pm

v2

Comments

Member 11337367 25-Aug-15 3:18am

Can u pls explain your query since iam not able to understand that..what is the use of stuff and for xml u defined..pls know me what it does?

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)

Wendelius · Accepted Answer · 2015-08-24T19:49:00

Solution 2

If you want the days to be within a single column you could try something like the following

SQL

SELECT a.empcode,
       STUFF((SELECT ',' + RTRIM(datepart(day, datecol) ) 
              FROM mytable b 
              WHERE b.empcode = a.empcode 
              FOR XML PATH('')), 1, 1, '') AS dates
FROM (SELECT DISTINCT EmpCode FROM mytable) a

Posted 24-Aug-15 19:49pm

Wendelius

Comments

Member 11337367 28-Aug-15 6:06am

one more i want that month under that i nee those values like in below format

empcode april,may,june
111 1,2 3,4 5,6
112 7,8 10 21,24
like this if it is possible pls help me out