Is there a way to Merge SQL Server Row Query?

Question

Please look at my current summary query result :

id      name mch
127664  ML  2
127666  ML  2
127667  ML  2
127670  ML  2
127671  ML  2
127672  ML  2
127674  ML  2
127675  ML  2
127678  ML  1
127680  ML  1
127665  ML  2

I want to merge row which has same value.. Just merge the name column. And then here is my expected query :

id      name    mch
127664  ML      2
127666          2
127667          2
127670          2
127671          2
127672          2
127674          2
127675          2
127678          1
127680          1
127665          2

I already look for some problem but still not found. I hope you want to guide me to handle this..

Possible duplicate of [How do I use ROW\_NUMBER()?](https://stackoverflow.com/questions/961007/how-do-i-use-row-number) — Amira Bedhiafi, Aug 20 '19 at 10:55

score 4 · Answer 1 · answered Aug 20 '19 at 10:49

Well you could try a ROW_NUMBER trick here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) rn
    FROM yourTable
)

SELECT
    id,
    CASE WHEN rn = 1 THEN name ELSE '' END AS name,
    mch
FROM cte
ORDER BY
    name,
    id;

But typically this type of requirement would be better handled in your presentation layer (e.g. PHP or Java).

score 1 · Answer 2 · answered Aug 20 '19 at 10:54

1

Your query should be like this :

WITH t AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) counter
    FROM table //nameofyourtable
)

SELECT
    id,CASE WHEN counter = 1 THEN name ELSE '' END AS name, mch
FROM t ORDER BY
    name,
    id;

answered Aug 20 '19 at 10:54

Amira Bedhiafi

8,088
6
24
60

What's the point of adding the same code as in the already existing answer? – Zohar Peled Aug 20 '19 at 11:01
@ZoharPeled I already flagged the question because it's related to row numbers and maybe didn't refresh the page. We can have the same way of thinking :) – Amira Bedhiafi Aug 20 '19 at 11:05

Is there a way to Merge SQL Server Row Query?

2 Answers2