CREATE TABLE #tp
(
Headerno VARCHAR (10),
Machineno VARCHAR (10),
Descrption NVARCHAR (20),
Artno VARCHAR (20),
Qty INT,
Repartno varchar (20),
Repqty INT
)
INSERT INTO #tp SELECT ' HD01 ', ' 0101520 ', N ' battery problem ', ' 102020 ', 2, ' 102020 ', 2
INSERT INTO #tp SELECT ' HD01 ', ' 0101520 ', N ' battery problem ', ' 101010 ', 2, ' 202020 ', 2
INSERT INTO #tp SELECT ' HD01 ', ' 0101520 ', N ' battery problem ', ' 126888 ', 2, ' 102020 ', 2
INSERT INTO #tp SELECT ' HD02 ', ' 01012221 ', N ' d motor fault ', ' 102020 ', 2, ' 102020 ', 2
INSERT INTO #tp SELECT ' HD03 ', ' 12312312 ', N ' sudden stop ', ' 102020 ', 2, ' 102020 ', 2
INSERT INTO #tp SELECT ' HD03 ', ' 12312312 ', N ' sudden stop ', ' 102020 ', 2, ' 102020 ', 2
INSERT INTO #tp SELECT ' HD04 ', ' 12312344 ', N ' belt loose ', ' 102020 ', 2, ' 102020 ', 2
SELECT case when Row=1 then Headerno ELSE ' END Headerno,
If Row=1 then Machineno ELSE ' END Machineno,
Case if Row=1 then descrption ELSE ' END descrption
, Artno,qty,repartno,repqty
From
(
SELECT *,row_number () over (PARTITION by Headerno,machineno,descrption ORDER by GETDATE ()) row
From #tp
) M
SQL Server Multi-column deduplication, same display of only one piece of data