How to get feature key and feature value separated $ based on part id

ahmed elbarbary

4y1k

I work on sql server 2012 i need to get featurekey and feature value separated $
Based on partid but i don't know how o 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')

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kirtesh Shah4y ago
check this article to get better undestanding ,

https://www.c-sharpcorner.com/UploadFile/860b29/concatenate-multiple-rows-within-single-row-in-sql-server-20/
Sachin Singh4y ago
Modified version for $
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 DISTINCT '$' + Featurekey
          FROM #PartsFeature
          WHERE PartId = a.PartId
          FOR XML PATH (''))
          , 1, 1, '')  AS Featurekey,
          STUFF(
         (SELECT DISTINCT '$' + FeatureValue
          FROM #PartsFeature
          WHERE PartId = a.PartId
          FOR XML PATH (''))
          , 1, 1, '')  AS FeatureValue
FROM #PartsFeature AS a
GROUP BY Featurekey,PartId,FeatureValue
drop table #PartsFeature
Sachin Singh4y ago
i used comma, you can use $
Sachin Singh4y ago
Test this, will work
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 DISTINCT ',' + Featurekey
          FROM #PartsFeature
          WHERE PartId = a.PartId
          FOR XML PATH (''))
          , 1, 1, '')  AS Featurekey,
          STUFF(
         (SELECT DISTINCT ',' + FeatureValue
          FROM #PartsFeature
          WHERE PartId = a.PartId
          FOR XML PATH (''))
          , 1, 1, '')  AS FeatureValue
FROM #PartsFeature AS a
GROUP BY Featurekey,PartId,FeatureValue
drop table #PartsFeature
Srinivasan Ramamoorthi4y ago
Refer this link for creating cursor in stored procedure. https://www.aspsnippets.com/Articles/Using-Cursor-in-SQL-Server-Stored-Procedure-with-example.aspx
Srinivasan Ramamoorthi4y ago
One option that comes to my mind immediately is to create a stored procedure, have a cursor and fetch these columns. Then Iterate the cursor and split feature key and value using substring and store the values in a temp table. Once you have inserted records in temp table, insert it to parts feature table.