SQL Server: REPLACE comma and combine it with similar row

Question

I have a bad table structure. I try to modify it as little as possible because the real problem is more complicated. I'm working in SQL Server 2005.

Here is my table:

tbl_item

id | item
---+------
1  | car
2  | car
3  | car, => This is what I focus
4  | jet 
5  | jet, car => This is just an example of the comma purpose

Query :

SELECT item, count(*) AS sum 
FROM tbl_item 
GROUP BY item 
ORDER BY sum DESC

Result:

item    | sum
--------+-------
car     | 2
car,    | 1
jet     | 1 
jet,car | 1

What I want is like this:

item     | sum
---------+--------
car      | 3
jet      | 1 
jet, car | 1 => actually I don't care about this, but this just for example

I tried :

SELECT REPLACE(item,',',''), count(*) AS sum 
FROM tbl_item 
GROUP BY item 
ORDER BY sum DESC

But the result is:

item     | sum
---------+------
car      | 2
car      | 1 => still in the different row
jet      | 1 
car, jet | 1

I can manipulate this so easy with PHP, but I wonder how to do this with pure SQL Server.

@detellda work like charm man. can u post your answer. i will sign that for the correct answer — plonknimbuzz
– plonknimbuzz, Commented Jan 11, 2017 at 2:45

S3S · Accepted Answer · 2017-01-11 03:18:19Z

3

Try:

GROUP BY REPLACE(item,',','')

This will normalize the items, then group on them.

edited Jan 11, 2017 at 3:18

S3S

25.2k5 gold badges29 silver badges48 bronze badges

answered Jan 11, 2017 at 2:48

detellda

1261 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jonathan Porter · Accepted Answer · 2017-01-11 02:48:42Z

3

You need to add it to the group by clause:

SELECT REPLACE(item,',',''), count(*) AS sum 
FROM tbl_item 
GROUP BY replace(item,',','')
ORDER BY sum DESC

answered Jan 11, 2017 at 2:48

Jonathan Porter

1,5689 gold badges38 silver badges67 bronze badges

1 Comment

plonknimbuzz Over a year ago

this is the correct answer too, but detellda is the first one who answer. thanks for the help and edit my question

Collectives™ on Stack Overflow

SQL Server: REPLACE comma and combine it with similar row

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related