CODEWITHSUNDEEP
sqlsql-serverpivotconcatenation
Triumph Spitfire
Triumph Spitfire

Reputation: 645

Pivot with Column Concatenation in SQL Server

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.

Upvotes: 1

Views: 1174

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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

Upvotes: 2

Related Questions