need sql query for some action: rows into single column depending on other column

Question

5.00/5 (1 vote)

See more:

Hi ,

please tell me a sql query to perform this action

suppose following is the table i currently have :

cl1 | cl2 | keywords
c1 | cc1|
c1 | cc2|
d1 | dd1|
d1 | dd2|
suppose following is the table i want

cl1 | cl2 | keywords
c1 | cc1| cc1,cc2
c1 | cc2| cc1,cc2
d1 | dd1| dd1,dd2
d1 | dd2| dd1,dd2

Posted 3-May-12 9:37am

shanalikhan

Updated 3-May-12 12:30pm

Maciej Los

v2

Add a Solution

Comments

Maciej Los 4-May-12 4:39am

Shankhan0, it would be great to see your qestion as "solved".

[no name] 4-May-12 15:53pm

Hi thanks for answering
its giving many space btw items in keywords
i mean keyword is somehting like this :
"cc1 ,cc2"
how i remove this unnecessary spaces
thanks

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)

Maciej Los · Accepted Answer · 2012-05-03T09:43:00

Solution 1

Try this:
Option 1:

SQL

SELECT  [cl1], STUFF( (SELECT',' + [cl2] AS 'text()'
                  FROM [A_TEST].[dbo].[Table_3] t2
                  WHERE t2.cl1 = t1.cl1
                  FOR XML PATH('')), 1, 1, '') AS [keywords]
   FROM [A_TEST].[dbo].[Table_3] t1
   GROUP BY [cl1]
   GO

Results:

cl1	keywords
c1	cc1,cc2
d1	dd1,dd2

Option 2:

SQL

SELECT  [cl1], [cl2], STUFF( (SELECT',' + [cl2] AS 'text()'
                  FROM [A_TEST].[dbo].[Table_3] t2
                  WHERE t2.cl1 = t1.cl1
                  FOR XML PATH('')), 1, 1, '') AS [keywords]
   FROM [A_TEST].[dbo].[Table_3] t1
   GROUP BY [cl1], [cl2]
   GO

Results:

cl1	cl2	keywords
c1	cc1	cc1,cc2
c1	cc2	cc1,cc2
d1	dd1	dd1,dd2
d1	dd2	dd1,dd2

More at:
T-SQL Convert rows into single column[^]

Related topics:
STUFF()[^]
FOR clause[^]
COLEASCE[^]

Posted 3-May-12 9:43am

Maciej Los

Updated 3-May-12 12:24pm

v3

Comments

Kschuler 3-May-12 16:53pm

I don't think that's right...it looks the contents of the "keywords" column isn't cl1 + cl2...but rather a combo of cl2 values where cl1 matches.

Maciej Los 3-May-12 17:13pm

Now i see the difference. cl1 = c1, cl2 = cc1, but keywords = {cc1, cc2}. I'll correct my solution. Thank you ;)
[EDIT]
Done! Sorry for my previous, bad answer.
[/EDIT]

VJ Reddy 3-May-12 19:54pm

Good answer. 5!

Maciej Los 4-May-12 3:05am

Thank you, VJ ;)

Sandeep Mewara 4-May-12 2:33am

Good answer. 5!

Maciej Los 4-May-12 3:05am

Thank you, Sandeep ;)

Espen Harlinn 4-May-12 7:13am

Nice job, 5'ed!

Maciej Los 4-May-12 7:34am

Thank you ;)

[no name] 4-May-12 15:40pm

Hi thanks for answering
its giving many space btw items in keywords
i mean keyword is somehting like this :
"cc1 ,cc2"
how i remove this unnecessary spaces
thanks

Maciej Los 8-May-12 16:01pm

If you have spaces on the right side of string, you need to use RTRIM() function, like this:
SELECT RTRIM([cl1]), RTRIM([cl2]), STUFF( (SELECT',' + RTRIM([cl2]) AS 'text()'
Use LTRIM() to delete unused spaces on the left side.
To delete spaces on both sides use LTRIM(RTRIM([FieldName]))

Sorry for late answer. Emails from this forum are not reaching to me.