SQL Concatenating row values on primary key

Question

I have a requirement to concatenate values in different rows of my query based on Item ID.

For instance, 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 output, 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 do I accomplish this please?

[And what have **you** tried so far, please?](http://www.whathaveyoutried.com) — marc_s, Oct 12 '12 at 06:25

Gidil · Answer 1 · 2012-10-12T07:34:50.493

4

You can write a recursive query based on this example (SQL Fiddle) -It now includes code to deal with NULL values in the 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

edited Oct 12 '12 at 07:34

answered Oct 12 '12 at 06:22

Gidil

4,137
2
34
50

I am getting some weird result. I tried this and instead got no results when in fact I have at least 12 rows in my table with values for the concatenation. – Kobojunkie Oct 12 '12 at 07:01
Did you look at the SQL Fiddle example? It uses the data you posted in the Q and gives the results as you requested. Is the data you're using considerably different? – Gidil Oct 12 '12 at 07:06
Yes, I did look at the fiddle example. Following the error, which I just noticed, it seems I am not allowed to do left joins within the recursive method. That seems to be why I was not getting anything. – Kobojunkie Oct 12 '12 at 07:08
1

If the empty value in the `UNIT` column of the `Room` row (or in that of the `Name Henry` row) is actually `NULL`, you might need to add ISNULLs or COALESCEs to work around that. – Andriy M Oct 12 '12 at 07:19
Thank you so much. I just got my SQL Hacks books again. Need to get back to seriously coding in SQL after years of working on basic CRUD projects. – Kobojunkie Oct 12 '12 at 21:41

TechDo · Accepted Answer · 2012-10-12T07:09:22.070

3

Please 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

edited Oct 12 '12 at 07:09

answered Oct 12 '12 at 06:24

TechDo

18,398
3
51
64

1

+1 for doing what I have decided to do. too. :- ) - using XML is the fastest and easy way to solve this issue. – gotqn Oct 12 '12 at 06:45
I also tried this example and I get no values at all when in fact I have so many values to compile, in my table. This happens when I try to join the table to another table using a left join. Is there a rule against this or is it likely some error on my end? – Kobojunkie Oct 12 '12 at 07:07
I think its becoz of NULL values in column Label or Value or Unit, please try by adding `ISNULL(b.Label, '')` instead of `b.Label` etc. – TechDo Oct 12 '12 at 07:10
thanks. I was able to use COALESCE to get past that issue on mine. thnaks – Kobojunkie Oct 12 '12 at 21:41

score 3 · Answer 3 · edited May 23 '17 at 11:48

Check this out Concatenating Row Values in Transact-SQL There are many ways to solve it.

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

enter image description here

Also pay attention to Concatenate many rows into a single text string of Stack Overflow

score 2 · Answer 4 · answered Oct 12 '12 at 06:43

Full working exmaple:

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

SQL Concatenating row values on primary key

4 Answers4