How do combine two column using sql

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi
I have to combine column in following format

ID    first    second    third
1    aaa    bbb    ccc
2    jina    -         bbb

The output should be(combine the number of items)
ID    total
1    aaa,bbb,ccc
2    jina,bbb

The want the output also in the format(count the number of items)

ID    total
1    3
2    2

Any suggestions pls................

Posted 2-Feb-14 19:47pm

prabhatsp

Add a Solution

2 solutions

Add a Solution

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

thatraja · Accepted Answer · 2014-02-02T20:12:00

What have you tried?

Concatenate the columns like below

SQL

SELECT ID, Column1 + "," + Column2 + "," + Column3 ....

In above query you have to check if the column value is NULL(in your case -) & omit that if returns NULL(-). For that you could use CASE[^] statement.

For second query you need to find the occurence of character(,), check this one
Character Occurrences[^]

Now your turn, go ahead. Customize where you want.

Hiren2 P · Accepted Answer · 2014-02-02T20:20:00

Solution 2

Select ID, ISNULL(first,'')+ISNULL(','+second,'')+ISNULL(','+third,'') AS Total_String,
(Case when first is null then 0 else 1 end)+(Case when second is null then 0 else 1 end)+(Case when third is null then 0 else 1 end) AS Total_Int

Posted 2-Feb-14 20:20pm

Hiren2 P

Updated 2-Feb-14 20:26pm

v2

Comments

prabhatsp 3-Feb-14 2:43am

thk u so much,i got the answer

berrymaria 3-Feb-14 2:47am

+5 !

How do combine two column using sql

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How do combine two column using sql

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us