Merge common rows in SQL Server

ahmedsa
 
on Oct 27, 2021 10:45 PM
409 Views

I work on sql server 2012 i need to get featurekey and feature value separated $ Based on partid but i don't know how to do that by select sql query?

expected result as below

PartId Featurekey FeatureValue
1550 Botato$Mango$dates Yellow$Red$Black
1600 Rice$macrona$chicken white$Red$Yellow
1700 Guava$grapes$FIG Yellow$Green$Red
create table #PartsFeature
(
    PartId int,
    Featurekey nvarchar(200),
    FeatureValue nvarchar(200),
)
insert into #PartsFeature(PartId,Featurekey,FeatureValue)
values
    (1550,'Botato','Yellow'),
    (1550,'Mango','Red'),
    (1550,'dates','Black'),
    (1600,'Rice','white'),
    (1600,'macrona','Red'),
    (1600,'chicken','Yellow'),
    (1700,'Guava','Yellow'),
    (1700,'grapes','Green'),
    (1700,'FIG','Red')
Download FREE API for Word, Excel and PDF in ASP.Net: Download
arjunv
 
on Oct 27, 2021 10:48 PM

I am working on it, will get back to you soon.

arjunv
 
on Oct 27, 2021 11:34 PM

Dear Ahmedsa, 

Kindly refer below sample query.

SQL

CREATE TABLE #PartsFeature
(
    PartId INT,
    Featurekey NVARCHAR(200),
    FeatureValue NVARCHAR(200),
)
INSERT INTO #PartsFeature(PartId,Featurekey,FeatureValue)
VALUES
(1550,'Botato','Yellow'),
(1550,'Mango','Red'),
(1550,'dates','Black'),
(1600,'Rice','white'),
(1600,'macrona','Red'),
(1600,'chicken','Yellow'),
(1700,'Guava','Yellow'),
(1700,'grapes','Green'),
(1700,'FIG','Red')

SELECT DISTINCT PartId,
	 STUFF((SELECT '$' + CAST(Featurekey AS VARCHAR(100))
           FROM #PartsFeature t2
           WHERE t2.PartId = t1.PartId
           FOR XML PATH('')),1,1,'') 'Featurekey',
	STUFF((SELECT '$' + CAST(FeatureValue AS VARCHAR(100))
           FROM #PartsFeature t2
           WHERE t2.PartId = t1.PartId
           FOR XML PATH('')),1,1,'') 'FeatureValue'
FROM #PartsFeature t1

DROP TABLE #PartsFeature

Output

PartId Featurekey FeatureValue
1550 Botato$Mango$dates Yellow$Red$Black
1600 Rice$macrona$chicken white$Red$Yellow
1700 Guava$grapes$FIG Yellow$Green$Red