CODEWITHSUNDEEP
sqlsql-serverstringconcatenation
Andi Keikha
Andi Keikha

Reputation: 1316

Sql query concatenation of strings in a column of multiple rows

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]

Upvotes: 2

Views: 83

Answers (3)

PSK
PSK

Reputation: 17943

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

Upvotes: 3

DxTx
DxTx

Reputation: 3347

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269445

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.

Upvotes: 3

Related Questions