Pivot with Column Concatenation in SQL Server

Question

Please see the code snippet below:

 DROP TABLE IF EXISTS PROJECT_DETAILS;
CREATE TABLE PROJECT_DETAILS
(
    PROJECT_NAME NVARCHAR(20),
    PROJECT_TYPE NVARCHAR(20),
    TOTAL_HOURS INT
);

INSERT INTO PROJECT_DETAILS VALUES('ProjectA','AU',100)
INSERT INTO PROJECT_DETAILS VALUES('ProjectA','SGP',50)
INSERT INTO PROJECT_DETAILS VALUES('ProjectA','NZ',75)
INSERT INTO PROJECT_DETAILS VALUES('ProjectB','US',200)
INSERT INTO PROJECT_DETAILS VALUES('ProjectB','CAN',100)
INSERT INTO PROJECT_DETAILS VALUES('ProjectC','JP',120)
INSERT INTO PROJECT_DETAILS VALUES('ProjectD','IND',100)
INSERT INTO PROJECT_DETAILS VALUES('ProjectD','CH',80)
INSERT INTO PROJECT_DETAILS VALUES('ProjectE','RSA',90)
INSERT INTO PROJECT_DETAILS VALUES('ProjectE','KEN',30)

DESIRED OUTPUT

PROEJCT_NAME    PROJECT_TYPE    EXEC_TYPE   TOTAL_HRS
ProjectA        AU              AU-SGP-NZ   100
ProjectA        SGP             AU-SGP-NZ   50
ProjectA        NZ              AU-SGP-NZ   75
ProjectB        US              US-CAN      200
ProjectB        CAN             US-CAN      100
ProjectC        JP              JP          120
ProjectD        IND             IND-CH      100
ProjectD        CH              IND-CH      80
ProjectE        RSA             RSA-KEN     90
ProjectE        KEN             RSA-KEN     30

As you can see above, I'd like to have the column names concatenated for each distinct project name (as a new column). Please let me know if you need any other details.

Hi, It doesn't matter what order it appears. Maybe, for now, you can order by TOTAL_HOURS — Triumph Spitfire, Oct 18 '17 at 10:11
Possible duplicate of [Concatenate many rows into a single text string?](https://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — qxg, Oct 18 '17 at 10:21

Yogesh Sharma · Accepted Answer · 2017-10-18T10:21:15.300

Just use STUFF with XML to Display Data in Single Row (No Need to PIVOT):

SELECT P.PROJECT_NAME,
       P.PROJECT_TYPE,
       [EXEC_TYPE] = STUFF(
                          (
                              SELECT 
                                     '-'+PROJECT_TYPE
                              FROM PROJECT_DETAILS
                              WHERE PROJECT_NAME = P.PROJECT_NAME FOR XML PATH('')
                          ), 1, 1, ''),
       P.TOTAL_HOURS
FROM PROJECT_DETAILS P;

Output :

PROEJCT_NAME    PROJECT_TYPE    EXEC_TYPE   TOTAL_HRS
ProjectA        AU              AU-SGP-NZ   100
ProjectA        SGP             AU-SGP-NZ   50
ProjectA        NZ              AU-SGP-NZ   75
ProjectB        US              US-CAN      200
ProjectB        CAN             US-CAN      100
ProjectC        JP              JP          120
ProjectD        IND             IND-CH      100
ProjectD        CH              IND-CH      80
ProjectE        RSA             RSA-KEN     90
ProjectE        KEN             RSA-KEN     30

Thanks for your quick response @Yogesh Sharma. It works. Will let you know if I come across any issues — Triumph Spitfire, Oct 18 '17 at 10:23

Pivot with Column Concatenation in SQL Server

1 Answers1