SQL Combining row values ​​in a primary key

Question

I have a requirement to combine values ​​in different lines of my query based on Item ID.

For example, I have the following rows of data.

GroupID LABEL VALUE UNIT 1 Name Henry 1 Guest Manny Guest 1 Room 12 1 Milk 10 Quart 1 Eggs 3 dozen 2 Name Mark Supervisor 2 Water 13 Litre 2 Milk 3 Gallons 2 Soap 12 bars 

And as a conclusion, I want to get the following

 ItemID VALUE 1 Name: Henry; Guest:Manny Guest; Room:12; Milk:10 Quart; Eggs: 3 dozen; 2 Name: Mark supervisor; Water: 13 litre; Milk: 3 Gallons; Soap: 12 bars; 

How to do this, please?

Answer 1

Try:

 SELECT DISTINCT a.GroupID AS ItemID,( SELECT ISNULL(b.LABEL,'')+': '+ISNULL(b.VALUE,'')+' '+ISNULL(b.UNIT,'')+';' FROM TABLE b WHERE b.GroupID=a.GroupID FOR XML PATH('')) AS [VALUE] FROM TABLE a 

Answer 2

You can write a recursive query based on this example ( SQL Fiddle ) - This now includes code for handling NULL values ​​in a UNIT column.

  WITH PRE AS (SELECT *, Row_number() OVER ( PARTITION BY GROUPID ORDER BY GROUPID) RN FROM TABLE1), RECURSIVE AS (SELECT *, Cast(LABEL + ':' + VALUE + ' ' + Isnull(UNIT, '') + ';' AS VARCHAR( MAX)) AS FINAL FROM PRE WHERE RN = 1 UNION ALL SELECT t1.*, FINAL + Cast(t1.LABEL +':' + t1.VALUE +' '+ Isnull(t1.UNIT, '') + ';' AS VARCHAR(MAX)) FROM PRE T1 INNER JOIN RECURSIVE T2 ON T1.GROUPID = T2.GROUPID AND T1.RN = T2.RN + 1) SELECT T1.GROUPID, T1.FINAL FROM RECURSIVE T1 INNER JOIN (SELECT Max(RN) RN, GROUPID FROM RECURSIVE GROUP BY GROUPID) T2 ON T1.GROUPID = T2.GROUPID AND t1.RN = T2.RN 

Answer 3

Note Combining String Values ​​in Transact-SQL There are many ways to solve this problem.

 Declare @t TABLE ( GroupID INT, Label NVARCHAR(20), Value NVARCHAR(20), Unit NVARCHAR (50) ) INSERT INTO @t VALUES (1,'Name','Henry','') ,(1,'Guest','Manny','Guest') ,(1,'Room','12','') ,(1,'Milk','10','Quart') ,(1,'Eggs','3','dozen') ,(2,'Name','Mark','Supervisor') ,(2,'Water','13','Litre') ,(2,'Milk','3','Gallons') ,(2,'Soap','12','bars') ;With Cte As ( Select GroupId ,Label = Label + ': ' + Value + ' ' + Unit From @t ) Select GroupId ,Value = Stuff((Select ';' + Cast(Label As Varchar(Max)) From Cte c2 Where c1.GroupId = c2.GroupId For Xml Path('')),1,1,'')+';' From Cte c1 Group By c1.GroupId 

Also pay attention to Combine many lines into one text line from

Answer 4

Full working example:

 SET NOCOUNT ON GO DECLARE @SourceTable TABLE ( GroupID INT, LABEL NVARCHAR(20), VALUE NVARCHAR(20), UNIT NVARCHAR (50) ) INSERT INTO @SourceTable (GroupID,LABEL,VALUE,UNIT) VALUES (1,'Name','Henry','') ,(1,'Guest','Manny','Guest') ,(1,'Room','12','') ,(1,'Milk','10','Quart') ,(1,'Eggs','3','dozen') ,(2,'Name','Mark','Supervisor') ,(2,'Water','13','Litre') ,(2,'Milk','3','Gallons') ,(2,'Soap','12','bars') SELECT DISTINCT GroupID ,(SELECT SUBSTRING((SELECT ';' + LABEL +':'+VALUE+' '+UNIT FROM @SourceTable AS B WHERE A.GroupID=B.GroupID FOR XML PATH('')),2,200) AS CSV) FROM @SourceTable AS A SET NOCOUNT OFF GO