Click here to Skip to main content
15,943,008 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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................

What have you tried?

Concatenate the columns like below
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.
Share this answer
prabhatsp 3-Feb-14 2:42am    
thk u sir, i got it
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
Share this answer
prabhatsp 3-Feb-14 2:43am    
thk u so much,i got the answer
berrymaria 3-Feb-14 2:47am    
+5 !

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900