question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked EchoLiu-msft edited

How to get faeturekey and featurevalue separated $ based on part id ?

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

sample data

 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')

expected result
144239-image.png


so how to write sql select query do above as expected result i need

sql-server-generalsql-server-transact-sql
image.png (7.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Please check:

  SELECT PartId,
         STUFF((SELECT '$' + CAST(Featurekey AS VARCHAR(30)) AS [text()]
           FROM #PartsFeature  AS O
           WHERE O.PartId = C.PartId 
           FOR XML PATH('')), 1, 1, NULL) AS Featurekey,
         STUFF((SELECT '$' + CAST(FeatureValue AS VARCHAR(30)) AS [text()]
           FROM #PartsFeature  AS O
           WHERE O.PartId = C.PartId 
           FOR XML PATH('')), 1, 1, NULL) AS FeatureValue
 FROM #PartsFeature  AS c
 GROUP BY PartId

Best regards
Echo


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.