Pivot/transpond source table to new view and concat rows

Question

I can't figure out the statement to get from SourceTable to the NewViewResult.

Currently the source is still on a Microsoft SQL Server 2012.

Description of Source and expected result

Below is the code to create and fill the SourceTable:

CREATE TABLE SourceTable
(
    [Contract] [nvarchar] (255) NULL,
    [Role] [nvarchar] (255) NULL,
    [Name] [nvarchar] (255) NULL
);

INSERT INTO [SourceTable] ([Contract], [Role], [Name]) 
VALUES ('First Contract', 'Author', 'Tom');
INSERT INTO [SourceTable] ([Contract], [Role], [Name]) 
VALUES ('First Contract', 'Manager', 'Ben');
INSERT INTO [SourceTable] ([Contract], [Role], [Name]) 
VALUES ('First Contract', 'Reviewer', 'Kate');
INSERT INTO [SourceTable] ([Contract], [Role], [Name]) 
VALUES ('First Contract', 'Signee', 'John');
INSERT INTO [SourceTable] ([Contract], [Role], [Name]) 
VALUES ('First Contract', 'Singee 2', 'Eli');
INSERT INTO [SourceTable] ([Contract], [Role], [Name]) 
VALUES ('Another Contract', 'Author', 'Chris');
INSERT INTO [SourceTable] ([Contract], [Role], [Name]) 
VALUES ('Another Contract', 'Manager', 'Susan');
INSERT INTO [SourceTable] ([Contract], [Role], [Name]) 
VALUES ('Another Contract', 'Reviewer', 'Davis');
INSERT INTO [SourceTable] ([Contract], [Role], [Name]) 
VALUES ('Another Contract', 'Reviewer', 'Tomi');
INSERT INTO [SourceTable] ([Contract], [Role], [Name]) 
VALUES ('Another Contract', 'Reviewer', 'Jane');
INSERT INTO [SourceTable] ([Contract], [Role], [Name]) 
VALUES ('Another Contract', 'Reviewer', 'Dolly');
INSERT INTO [SourceTable] ([Contract], [Role], [Name]) 
VALUES ('Another Contract', 'Reviewer', 'Ray');
INSERT INTO [SourceTable] ([Contract], [Role], [Name]) 
VALUES ('Another Contract', 'Reviewer', 'Pat');
INSERT INTO [SourceTable] ([Contract], [Role], [Name]) 
VALUES ('Another Contract', 'Reviewer', 'Amy');
INSERT INTO [SourceTable] ([Contract], [Role], [Name]) 
VALUES ('Another Contract', 'Signee', 'Eli');
INSERT INTO [SourceTable] ([Contract], [Role], [Name]) 
VALUES ('Another Contract', 'Signee 2', 'John');

Source Table to new View

Any chance you'd be willing to either upgrade your SQLS or install this: https://github.com/orlando-colamatteo/ms-sql-server-group-concat-sqlclr — Caius Jard, Mar 28 '20 at 05:48

score 0 · Answer 1 · answered Mar 28 '20 at 06:07

I'd do this by conditional aggregation:

SELECT 
  contract,
  MAX(CASE WHEN role = 'Author' Then Name End) as Author,
  MAX(CASE WHEN role = 'Manager' Then Name End) as Manager,
  ...
FROM t
GROUP BY contract

But the slight sticking point is the multiple names. On a more modern SQLS or one upgraded with a CLR group concat you could swap the MAX for GROUP_CONCAT

If you're sticking with your vanilla SQLS2012 you'll probably end up with a STUFF/FOR XML PATH hack on a coordinated subquery

MAX(STUFF((SELECT DISTINCT ',' + Name 
FROM t t1 WHERE t.contract = t1.contract
FOR XML PATH('')), 1, 1, '' )) as reviewers,

score 0 · Answer 2 · answered Mar 28 '20 at 07:07

0

Thank you Caius. I'll check this as well.

It seems I did not search good enough in the first place. I have found a possible solution here: Sql PIVOT and string concatenation aggregate

answered Mar 28 '20 at 07:07

chris

23
1
1
3

score 0 · Answer 3 · answered Mar 28 '20 at 10:21

Here you go

SELECT Contract, 
       STRING_AGG(CASE WHEN Role = 'Author' THEN Name END, ',') Author,
       STRING_AGG(CASE WHEN Role = 'Manager' THEN Name END, ',') Manager,
       STRING_AGG(CASE WHEN Role = 'Reviewer' THEN Name END, ',') Reviewer,
       STRING_AGG(CASE WHEN Role = 'Signee' THEN Name END, ',') Signee,
       STRING_AGG(CASE WHEN Role = 'Signee 2' THEN Name END, ',') [Singee 2]
FROM SourceTable
GROUP BY Contract
ORDER BY Contract DESC;

Returns:

+------------------+--------+---------+-----------------------------------+--------+----------+
|     Contract     | Author | Manager |             Reviewer              | Signee | Singee 2 |
+------------------+--------+---------+-----------------------------------+--------+----------+
| First Contract   | Tom    | Ben     | Kate                              | John   | Eli      |
| Another Contract | Chris  | Susan   | Davis,Tomi,Jane,Dolly,Ray,Pat,Amy | Eli    | John     |
+------------------+--------+---------+-----------------------------------+--------+----------+

Online demo

Pivot/transpond source table to new view and concat rows

3 Answers3