Replace duplicate records set null ot empty in the column mysql

Question

this is mysql query i want to set duplicate value as null or empty

SELECT 
som.sale_invoice_id
,CONCAT(cm.first_name,cm.last_name) AS customername
,product_master.product_name 
FROM 
sale_invoice_master as som 
LEFT JOIN customer_master as cm 
     ON som.customer_id = cm.customer_id 
LEFT JOIN product_sale_item_master as soi 
     ON som.sale_invoice_id = soi.sale_invoice_id 
LEFT JOIN product_master   
     ON soi.product_id =product_master.product_id 
LEFT JOIN vehicle_master 
     ON soi.vehicle_id = vehicle_master.id

This is mycurrent result

sale_invoice_id	customername	product_name
1	JummakhanDilawarkhan	Apollo TYRE 16.9-28 12PR KRISHAK GOLD -D
1	JummakhanDilawarkhan	APOLLO TUBE 7.50x16
2	PareshKhanchandani	Apollo TL 155R13 AMAZER XL 8PR

i want this:

sale_invoice_id	customername	product_name
1	JummakhanDilawarkhan	Apollo TYRE 16.9-28 12PR KRISHAK GOLD -D
		APOLLO TUBE 7.50x16
2	PareshKhanchandani	Apollo TL 155R13

second duplicate row should be null or empty

Answer 1

I don't have your data to work out with. So what I have done is taken your current result as my primary data and used a query to produce your output:

I have mentioned it all in db-fiddle

Sure enough, you can modify this to use Window Functions as you are using MariaDB 10.4. I have just mentioned a possible solution.

/*To create table*/
CREATE TABLE `sales_invoice_data` (
  `sales_invoice_id` int(11) DEFAULT NULL,
  `customername` varchar(50) DEFAULT NULL,
  `product_name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*to insert data*/
insert into `sales_invoice_data` (`sales_invoice_id`, `customername`, `product_name`) values('1','JummakhanDilawarkhan','Apollo TYRE 16.9-28 12PR KRISHAK GOLD -D');
insert into `sales_invoice_data` (`sales_invoice_id`, `customername`, `product_name`) values('1','JummakhanDilawarkhan','APOLLO TUBE 7.50x16');
insert into `sales_invoice_data` (`sales_invoice_id`, `customername`, `product_name`) values('2','PareshKhanchandani','Apollo TL 155R13');

/*to retrieve your OP*/
SELECT 
sales_invoice_id,
customerName,
product_name
FROM 
(SELECT 
(CASE WHEN sales_invoice_id=@running_sales_id THEN '' ELSE sales_invoice_id END)  sales_invoice_id,
(CASE WHEN customername=@running_customer THEN '' ELSE customername END) customerName,
product_name,
(CASE WHEN @running_sales_id=0 THEN @running_sales_id:=sales_invoice_id ELSE @running_sales_id:=@running_sales_id END) ,
(CASE WHEN @running_customer='' THEN @running_customer:=customername ELSE @running_customer:=@running_customer END) ,
@running_sales_id:=a.sales_invoice_id,
@running_customer:=customername
FROM 
(SELECT 
  s.sales_invoice_id ,
  s.customername,
  s.product_name,
  @running_sales_id:=0,
  @running_customer:=''
FROM
  `sales_invoice_data`  s) a ) final
  ;

Answer 2

One possible solution would involve using MySQL's LAG() function. Here are the docs for it:

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_lag