Muhamad Ridwansyah Muhamad Ridwansyah - 4 months ago 6x
SQL Question

Merge Query With Group

Hello im using SQL Server i want merge data by categori
i have a query like this

SELECT a.NamaMerk, b.NamaTipe from MsMerk as a inner join MsTipe as b
on a.ID_Merk = b.ID_Merk where a.FlagActive='Y' and b.FlagActive='Y' and a.ID_Merk = b.ID_Merk and b.ID_Tipe = b.ID_Tipe

This Show data

enter image description here

i want data Show like this

Samsung : Galaxy S7

Galaxy S8

Asus : Zenfone 4

Zenfone 5

Like a merge data by category, help me thank's


Try this out. I use row_number to rank by NamaMerk and then when it's greater than 1 I set it to empty string

         WHEN T.rank > 1 THEN '' 
         ELSE T.NamaMerk 
       END        AS 'NamaMerk', 
       T.NamaTipe AS 'NamaTipe' 
FROM   (SELECT a.NamaMerk, 
                   partition BY a.NamaMerk 
                   ORDER BY b.NamaTipe) AS 'RANK' 
        FROM   msmerk AS a 
               INNER JOIN mstipe AS b 
                       ON a.id_merk = b.id_merk 
        WHERE  a.flagactive = 'Y' 
               AND b.flagactive = 'Y' 
               AND a.id_merk = b.id_merk ) T