合并查询与组(Merge Query With Group)

合并查询与组(Merge Query With Group)

您好我使用SQL Server我想按分类合并数据我有这样的查询

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

这显示数据

我想要数据显示这样的

三星：Galaxy S7

Galaxy S8

华硕：Zenfone 4

Zenfone 5

就像按类别合并数据一样，帮助我谢谢

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

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

最满意答案

试试吧。 我使用row_number按NamaMerk排名，然后当它大于1时我将其设置为空字符串

SELECT CASE WHEN T.rank > 1 THEN '' ELSE T.NamaMerk END AS 'NamaMerk', T.NamaTipe AS 'NamaTipe' FROM (SELECT a.NamaMerk, b.NamaTipe, Row_number() OVER( 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

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

SELECT CASE WHEN T.rank > 1 THEN '' ELSE T.NamaMerk END AS 'NamaMerk', T.NamaTipe AS 'NamaTipe' FROM (SELECT a.NamaMerk, b.NamaTipe, Row_number() OVER( 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