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

  • 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

     

    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')
  • Upgrade and use STRING_AGG?

    use tempdb;
    go

    create table #PartsFeature
    (
    PartId int,
    Featurekey nvarchar(200),
    FeatureValue nvarchar(200),
    );
    go

    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 PartID,
    FeatureKeys = STRING_AGG(FeatureKey,'$'),
    FeatureValues = STRING_AGG(FeatureValue,'$')
    FROM #PartsFeature
    GROUP BY PartID;

    In my grandpa's days they used stuff like this:

    SELECT  a.UserID, 
    SUBSTRING(d.Addresses,1, LEN(d.Addresses) - 1) AddressList
    FROM
    (
    SELECT DISTINCT UserID
    FROM tableName
    ) a
    CROSS APPLY
    (
    SELECT [AddressLine1] + ', '
    FROM tableName AS B
    WHERE A.UserID = B.UserID
    FOR XML PATH('')
    ) D (Addresses)

    Or, if you gotta use 2012, this should work:

    SELECT PartID,
    FeatureValues2 = RIGHT(x.FeatureValues2,LEN(x.FeatureValues2)-1)
    FROM
    ( SELECT PartID,
    FeatureValues2 = ( SELECT '$' + pf2.FeatureValue
    FROM #PartsFeature pf2
    WHERE pf2.partID = pf.PartID
    FOR XML PATH('')
    )
    FROM #PartsFeature pf
    GROUP BY PartID ) x;

    • This reply was modified 1 month ago by  pietlinden.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply