How to pivot and concatenate data in a view?

Question

I'm creating a view to serve as a report that displays row data from various tables as columns, in some cases involving concatenation. My query works excellent when I use a temporary table, but it needs to be a view, and I'm wrestling with converting the temporary table approach to subqueries.

I've created a couple sample tables and data to simplify this question as much as possible. Here's the schema:

CREATE TABLE [Car] (
    [CarId] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Car] PRIMARY KEY CLUSTERED ([CarId] ASC))
 INSERT INTO [Car] VALUES
    (1, 'Honda')

CREATE TABLE [Part] (
    [PartId] [int] IDENTITY(1,1) NOT NULL,
    [CarId] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [PercentComplete] [decimal](3, 2) NOT NULL,
 CONSTRAINT [PK_Part] PRIMARY KEY CLUSTERED  ([PartId] ASC))
INSERT INTO [Part] VALUES
    (1, 'Engine', 0.5)
    ,(1, 'Transmission', 0.75)
    ,(1, 'Suspension', 0.3)

Here's the initial step:

SELECT
    c.CarId
    ,c.Name AS [CarName]
    ,p.Name AS [PartName]
    ,p.PercentComplete AS [PartPercentComplete]
INTO #Temp
FROM Car c
JOIN Part p
ON p.CarId = c.CarId

Here's the grouped select which converts it to my report view:

SELECT
    MAX(CarName) AS [Car Name]

    ,STUFF((
        SELECT ', ' + [PartName] 
        FROM #Temp
        WHERE (CarId = t1.CarId) 
        FOR XML PATH (''))
    ,1,2,'') AS [Parts]

    ,MAX(CASE [PartCompleteAvg] WHEN 1.00 THEN 'Complete' ELSE 'Incomplete' END) AS [Part Status]
    ,MAX(CASE [PartId] WHEN [LatestPartId] THEN [PartName] ELSE NULL END) AS [Latest Part]
FROM (
    SELECT
        CarId
        ,AVG(PartPercentComplete) AS [PartCompleteAvg]
        ,MAX(PartId) AS [LatestPartId]
    FROM #Temp
    GROUP BY CarId
) t1
LEFT JOIN #Temp t2
ON t2.CarId = t1.CarId
GROUP BY t1.CarId

It works fantastic except I have trouble converting it to a view because of the intermediate aggregate function AVG(PartPercentComplete), and the concatenation trick with STUFF. My report requires several of each of these.

I know I can't nest AVG() within MAX() as that gives Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

(FYI I'm aware of PIVOT but it'd need to be over multiple columns, though I realize that's possible too, CASE seems easier and I'm told faster.)

My totally incorrect and poor attempt:

SELECT
    MAX(CarName) AS [Car Name]
    ,STUFF((
        SELECT ', ' + [PartName] 
        FROM #Temp
        WHERE (CarId = t1.CarId) 
        FOR XML PATH (''))
    ,1,2,'') AS [Parts]

    ,MAX(CASE [PartCompleteAvg] WHEN 1.00 THEN 'Complete' ELSE 'Incomplete' END) AS [Part Status]
FROM (
    SELECT
        CarId
        ,AVG(PartPercentComplete) AS [PartCompleteAvg]
    FROM (
        SELECT
            c.CarId
            ,c.Name AS [CarName]
            ,p.Name AS [PartName]
            ,p.PercentComplete AS [PartPercentComplete]
        FROM Car c
        JOIN Part p
        ON p.CarId = c.CarId
    ) t1
    GROUP BY CarId
) t1
LEFT JOIN #Temp t2
ON t2.CarId = t1.CarId
GROUP BY t1.CarId

Obviously #Temp is invalid in both locations. I'm not a SQL expert and I'm hoping there is a clean and relatively simple way to do this, maybe even avoiding the self-join.

Edit

I added in

MAX(PartId) AS [LatestPartId]

and

MAX(CASE [PartId] WHEN [LatestPartId] THEN [PartName] ELSE NULL END) AS [Latest Part]

as it's a better example of why I need an intermediate GROUP BY.

Answer 1

Maybe I am missing something in your requirements but does this not work for you (tested in SQL Server 2008 and created a view with it):

select max(c.name) CarName,
  STUFF((
        SELECT ', ' + p.Name 
        from part p
        WHERE (p.CarId = c.CarId) 
        FOR XML PATH (''))
    ,1,2,'') AS [Parts],
   CASE AVG(p.PercentComplete) WHEN 1.00 THEN 'Complete' ELSE 'Incomplete' END AS [Part Status]
from car c
inner join part p
  on c.carid = p.carid
group by c.carid

see SQL Fiddle with Demo

Answer 2

Here's an updated answer for the updated question.

;WITH A(CarID,CarName,PartName,IsComplete,RN)AS(
SELECT C.CarID, C.Name, P.Name, case when P.PercentComplete=1 then 1 else 0 end,
    rn=row_number() over (partition by C.CarID order by P.PartId)
From Car C
LEFT JOIN Part P on P.CarId = C.CarId
)
,B(CarID,CarName,PartNames,LastPartName,IsComplete,RN)AS(
select CarID,CarName,CAST(PartName as VARCHAR(max)),PartName,IsComplete,RN
from A
Where rn=1
union all
select A.CarID,A.CarName,B.PartNames+', '+A.PartName,A.PartName,Case when A.IsComplete=1 and B.IsComplete=1 then 1 else 0 end,A.RN
from B
join A on A.CarID=B.CarID and A.RN-1=B.RN
),C AS(
select *, RNc=Row_number() over (partition by CarID order by RN desc)
from B
)
select CarID,CarName,PartNames,LastPartName,IsComplete
from C
where RNc=1

Below this, the original answer.

---

CREATE TABLE [Car] (
    [CarId] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL,
    CONSTRAINT [PK_Car] PRIMARY KEY CLUSTERED ([CarId] ASC));
INSERT INTO [Car] VALUES (1, 'Honda'),
                         (2, 'Ford');

CREATE TABLE [Part] (
    [PartId] [int] IDENTITY(1,1) NOT NULL,
    [CarId] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [PercentComplete] [decimal](3, 2) NOT NULL,
    CONSTRAINT [PK_Part] PRIMARY KEY CLUSTERED  ([PartId] ASC));
INSERT INTO [Part] VALUES (1, 'Engine', 0.5),
                          (1, 'Transmission', 0.75),
                          (1, 'Suspension', 0.3),
                          (2, 'Engine', 1),
                          (2, 'Brake', 1.0);

   SELECT C.CarID,
          C.Name CarName,
          STUFF((
              SELECT ', ' + Name
               FROM Part
              WHERE CarId = C.CarId 
                FOR XML PATH ('')),1,2,'') Parts,
          CASE WHEN Progress = 1 then 'Complete' else 'Incomplete' END [Part Status]
     From Car C
LEFT JOIN (
   select CarId, SUM(PercentComplete)/Count(1) Progress
     From Part
 Group by CarId) P on P.CarID = C.CarId

CarID       CarName  Parts                               Part Status
----------- -------- ----------------------------------- -----------
1           Honda    Engine, Transmission, Suspension    Incomplete
2           Ford     Engine, Brake                       Complete

Answer 3

Richard put me on the right track. Here's what I was looking for:

SELECT c.CarId
    ,c.Name AS [CarName]
    ,STUFF((
        SELECT ', ' + Name
        FROM Part p
        WHERE p.CarId = c.CarId
        FOR XML PATH ('')),1,2,'') AS [Parts]
    ,CASE WHEN Progress = 1 THEN 'Complete' ELSE 'Incomplete' END AS [Part Status]
    ,[Latest Part]
FROM Car c
LEFT JOIN
(
    SELECT p.CarId
    ,SUM(PercentComplete)/COUNT(1) AS [Progress]
    ,MAX(CASE WHEN PartId = LatestPartId THEN p.Name ELSE NULL END) AS [Latest Part]
    FROM Part p
    LEFT JOIN (
        SELECT CarId
        ,MAX(PartId) AS [LatestPartId]
        FROM Part
        GROUP BY CarId
    ) latestPart
    ON latestPart.CarId = p.CarId
    GROUP BY p.CarId
) partInfo
ON partInfo.CarId = c.CarId

The key was to leave GROUP BY out of the main select (much preferred) and put my aggregation code in a series of JOINs using GROUP BY, nesting a join when necessary to compute some intermediate information.

I feel foolish as I hadn't thought of using SELECT within JOIN, my brain only wanted to think of using it within FROM. Now I can use pivoting and concatenation effectively!

Here's the SQL Fiddle.