CODEWITHSUNDEEP
sqlsql-serversql-server-2008pivotpivot-table
dynamicuser
dynamicuser

Reputation: 1552

Concatenate a pivot table output

I have the following code that returns a pivoted table of current jobs and their estiamted times:

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' +
                    QUOTENAME(Name)
                  FROM JobPhases
                  FOR XML PATH(''), TYPE
                 ).value('.', 'NVARCHAR(MAX)') 
                    , 1, 1, '');

SELECT @query = 'SELECT *
FROM    
(   
    SELECT c.Registration as ''Reg.'', p.Name, [x] = j.EstimatedTime
    FROM    JobDetails  AS j
    INNER JOIN JobPhases p ON p.ID = j.PhaseId
    INNER JOIN Jobs job on job.ID = j.JobID
    INNER JOIN Cars c on job.CarID = c.ID
    WHERE job.Status = 1 or job.Status = 0
) JobDetails
PIVOT
(   SUM(x)
    FOR Name IN (' + @cols + ')
) pvt'

execute(@query);

Output:

JobID | Repair & Reshape | Refit Stripped Parts | Polishing 
1000  | 2.00             | 1.00                 | 1.30
1001  | 2.30             | 0.30                 | 2.00

What I need is to concatenate j.ActualTime in the displayed value. Any ideas how I can do this? So, the final output would be - 4.00 / 5.30 (where 4.00 is j.EstimatedTime and 5.30 is j.ActualTime).

Cheers

Upvotes: 0

Views: 334

Answers (1)

John Tseng
John Tseng

Reputation: 6352

I would do all of your processing first, outside of the PIVOT. This way you can do aggregations, concatenations, and whatever you want. After all the processing, then do a simple, non-aggregating pivot:

SELECT *
FROM    
(   
    SELECT c.Registration as ''Reg.'', p.Name, 
        CAST(SUM(j.EstimatedTime) as VARCHAR(MAX)) + '/' +
        CAST(SUM(j.ActualTime) as VARCHAR(MAX)) as [x]
    FROM    JobDetails  AS j
    INNER JOIN JobPhases p ON p.ID = j.PhaseId
    INNER JOIN Jobs job on job.ID = j.JobID
    INNER JOIN Cars c on job.CarID = c.ID
    WHERE job.Status = 1 or job.Status = 0
    GROUP BY c.Registration, p.Name
) JobDetails
PIVOT
(   MAX(x)
    FOR Name IN (' + @cols + ')
) pvt

Upvotes: 1

Related Questions