Sql query concatenation of strings in a column of multiple rows

Question

I have table e as

eId FName LName Type  SubType
  1  a     aa    1    S11a
  1  a     aa    1    S12a
  1  a     aa    1    S13a
  1  a     aa    3    S31a
  1  a     aa    3    S32a
  2  b     bb    1    S11b
  2  b     bb    1    S12b
  2  b     bb    3    S31b
  2  b     bb    3    S32b

I want to get a table like this

eId    FName   LName   SubType1         SubType2
1       a       aa     S11a;S12a;S13a   S31a;S32a
2       b       bb     S11b;S12b         S31b;S32b

In other words, I want to run a sql query that gives me subtypes group by employees, as two columns for type 1 and type 2. I am trying to work with STUFF command in sql query but wasn’t successful. This is the query I wrote but it doesn’t work. Basically it gives me Null values for SubType columns.

    SELECT e1.eId, e1.FName, e1.LName
, STUFF((
        SELECT N'; ' + [SubType]
        FROM e e2
        WHERE e1.eId = e2.eId 
            AND e1.FName = e2.FName 
            AND e1.LName = e2.LName         
            AND e1.[Type] = e2.[Type]           
            AND e1.[Type] = 1
        FOR XML PATH ('')), 1, 2, '') AS SubType1

, STUFF((
        SELECT N'; ' + [SubType]
        FROM e e2
        WHERE e1.eId = e2.eId 
            AND e1.FName = e2.FName 
            AND e1.LName = e2.LName         
            AND e1.[Type] = e2.[Type]           
            AND e1.[Type] = 3
        FOR XML PATH ('')), 1, 2, '') AS SubType2 
FROM e e1
GROUP BY e1.eId, e1.FirstName, e1.LastName, e1.[Type]

How does it not work?

Gordon Linoff
– Gordon Linoff

2018-05-14 11:41:14 +00:00
Commented May 14, 2018 at 11:41 — Gordon Linoff
– Gordon Linoff, Commented May 14, 2018 at 11:41
It gives Null values for SubType1 and SubType2

Andi Keikha
– Andi Keikha

2018-05-14 11:42:01 +00:00
Commented May 14, 2018 at 11:42 — Andi Keikha
– Andi Keikha, Commented May 14, 2018 at 11:42

Gordon Linoff · Accepted Answer · 2018-05-14 11:43:37Z

3

Hmmm. The group by . . . type is suspect. Perhaps this will work:

SELECT e1.eId, e1.FName, e1.LName,
       STUFF((SELECT N'; ' + [SubType]
              FROM e e2
              WHERE e1.EmployeeId = e2.EmployeeId AND
                    e2.[Type] = 1
              FOR XML PATH ('')
             ), 1, 2, '') AS SubType1,
       STUFF((SELECT N'; ' + [SubType]
              FROM e e2
              WHERE e1.EmployeeId = e2.EmployeeId AND
                    e2.[Type] = 3
              FOR XML PATH ('')
             ), 1, 2, '') AS SubType1
FROM e e1
GROUP BY e1.eId, e1.FirstName, e1.LastName;

I also think the comparisons on the names is redundant, so I removed them.

answered May 14, 2018 at 11:43

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andi Keikha Over a year ago

If I don't add Type in Group By, I get this eror: Column 'e.Type' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Gordon Linoff Over a year ago

@Andi . . . . You will not get that error with this version of the query. The correlation clause does not use e1.type.

PSK · Accepted Answer · 2018-05-14 12:41:55Z

3

There are multiple issues in your query.

1- Column name you are using are not correct, in your select you are using FName and LName and Group by you are using FirstName and LastName.

2- EmployeeId column is eId, in once place you are sing eId and another place you are using EmployeeId

3- Type is not in select, it can't be part of group by.

Following query should work.

SELECT e1.eId, e1.FName, e1.LName,
       STUFF((SELECT N'; ' + [SubType]
              FROM e e2
              WHERE e1.[eId] = e2.[eId] AND
                    e2.[Type] = 1
              FOR XML PATH ('')
             ), 1, 2, '') AS SubType1,
       STUFF((SELECT N'; ' + [SubType]
              FROM e e2
              WHERE e1.[eId] = e2.[eId] AND
                    e2.[Type] = 3
              FOR XML PATH ('')
             ), 1, 2, '') AS SubType1
FROM e e1
GROUP BY e1.eId, e1.FName, e1.LName;

DEMO

Edit:

You can also try like following, this clearly segregate the group by with your sub queries.

SELECT *,
 STUFF((SELECT N'; ' + [SubType]
              FROM e e2
              WHERE e3.[eId] = e2.[eId] AND
                    e2.[Type] = 1
              FOR XML PATH ('')
             ), 1, 2, '') AS SubType1,
       STUFF((SELECT N'; ' + [SubType]
              FROM e e2
              WHERE e3.[eId] = e2.[eId] AND
                    e2.[Type] = 3
              FOR XML PATH ('')
             ), 1, 2, '') AS SubType1
FROM
(
  SELECT e1.eId, e1.FName, e1.LName   
  FROM e e1
  GROUP BY e1.eId, e1.FName, e1.LName
)e3

edited May 14, 2018 at 12:41

answered May 14, 2018 at 12:09

PSK

18k5 gold badges35 silver badges44 bronze badges

3 Comments

Andi Keikha Over a year ago

If I don't add Type in Group By, I get this eror: Column 'e.Type' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

PSK Over a year ago

I have created a demo, did you checked it?

PSK Over a year ago

@Andi, I have added one more query, you can have a look to it.

DxTx · Accepted Answer · 2018-05-14 12:33:24Z

Try this one...

Table Script and Sample data

CREATE TABLE [TableName](
    [eId] [int] NULL,
    [FName] [nvarchar](50) NULL,
    [LName] [nvarchar](50) NULL,
    [Type] [int] NULL,
    [SubType] [nvarchar](50) NULL
)

INSERT [TableName] ([eId], [FName], [LName], [Type], [SubType]) VALUES (1, N'a', N'aa', 1, N'S11a')
INSERT [TableName] ([eId], [FName], [LName], [Type], [SubType]) VALUES (1, N'a', N'aa', 1, N'S12a')
INSERT [TableName] ([eId], [FName], [LName], [Type], [SubType]) VALUES (1, N'a', N'aa', 1, N'S13a')
INSERT [TableName] ([eId], [FName], [LName], [Type], [SubType]) VALUES (1, N'a', N'aa', 3, N'S31a')
INSERT [TableName] ([eId], [FName], [LName], [Type], [SubType]) VALUES (1, N'a', N'aa', 3, N'S32a')
INSERT [TableName] ([eId], [FName], [LName], [Type], [SubType]) VALUES (2, N'b', N'bb', 1, N'S11b')
INSERT [TableName] ([eId], [FName], [LName], [Type], [SubType]) VALUES (2, N'b', N'bb', 1, N'S12b')
INSERT [TableName] ([eId], [FName], [LName], [Type], [SubType]) VALUES (2, N'b', N'bb', 3, N'S31b')
INSERT [TableName] ([eId], [FName], [LName], [Type], [SubType]) VALUES (2, N'b', N'bb', 3, N'S32b')

Query (Using PIVOT function)

SELECT eid, 
       fname, 
       lname, 
       [1] AS SubType1, 
       [3] AS SubType2 

FROM   (SELECT eid, [type], Max(fname) AS FName, Max(lname) AS LName, 
               Stuff((SELECT '; ' + subtype 
                      FROM   tablename t2 
                      WHERE  t2.eid = t1.eid 
                             AND t2.[type] = t1.[type] 
                      FOR xml path('')), 1, 2, '') AS SubType 
        FROM   tablename t1 
        GROUP  BY eid, [type]) sq 
       PIVOT ( Max(subtype) 
             FOR [type] IN ([1], [3]) ) piv

Output

+-----+-------+-------+------------------+------------+
| eid | fname | lname |     SubType1     |  SubType2  |
+-----+-------+-------+------------------+------------+
|   1 | a     | aa    | S11a; S12a; S13a | S31a; S32a |
|   2 | b     | bb    | S11b; S12b       | S31b; S32b |
+-----+-------+-------+------------------+------------+

Demo: http://www.sqlfiddle.com/#!18/1542b/3/0

Collectives™ on Stack Overflow

Sql query concatenation of strings in a column of multiple rows

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related