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 2 years, 11 months ago by pietlinden.