How to Turn this SQL Pivot Procedure into a SQL Pivot View

Question

I have an existing PIVOT table that is generated from a stored procedure that I'm wondering if I can get some help with turning this into a PIVOT table in a standard query view in SQL Server (Express), if possible.

I have no problem with the stored procedure working properly for my situation. This procedure was created a while back from a source I found at that time, and I'm not knowledgeable enough to build something like this all on my own in SQL Server just yet. I'm mostly trying to understand better how to do PIVOT tables in a view since I may have to do this more than a few times with this data because of how it is stored in the database. I'm not sure what other information would be needed, so please ask.

USE [Part Class]
GO
/****** Object:  StoredProcedure [dbo].[sp_CategoryPartAttributeValue]    Script Date: 4/15/2022 4:24:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Don Petry
-- Create date: 7/11/21
-- Description: Returns pivoted category and attribute data
-- =============================================
ALTER PROCEDURE [dbo].[sp_CategoryPartAttributeValue] 
 -- Add the parameters for the stored procedure here
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
 SET FMTONLY OFF;

    -- Insert statements for procedure here
 DECLARE 
    @columns NVARCHAR(MAX) = '',
    @sql     NVARCHAR(MAX) = '';

 -- select the category names
 SELECT 
 @columns += QUOTENAME(AttributeDesc) + ','
 FROM 
 dbo.Attribute
 ORDER BY 
 AttributeDesc;

 -- remove the last comma
 SET @columns = LEFT(@columns, LEN(@columns) - 1);

 -- construct dynamic SQL
 SET @sql ='
 SELECT * FROM            
 (
 SELECT dbo.Category.CategoryID, 
    dbo.Category.CategoryDesc, 
    dbo.PartNumbers.PartID, 
    dbo.PartNumbers.PartNumber, 
    dbo.PartNumbers.Description, 
    dbo.PartNumbers.PartStatus, 
    dbo.PartNumbers.OrderType, 
    dbo.PartNumbers.PrevPN, 
    dbo.PartNumbers.BOSS_OH_QTY, 
    dbo.Attribute.AttributeDesc, 
    dbo.PartAttribute.PartAttributeValue
 FROM dbo.PartNumbers 
   INNER JOIN dbo.PartAttribute ON dbo.PartNumbers.PartID = dbo.PartAttribute.PartID 
   INNER JOIN dbo.Attribute ON dbo.PartAttribute.AttributeID = dbo.Attribute.AttributeID
   INNER JOIN dbo.PartCategory ON dbo.PartNumbers.PartID = dbo.PartCategory.PartID
   INNER JOIN dbo.Category ON dbo.PartCategory.CategoryID = dbo.Category.CategoryID
 ) PN 
 PIVOT (
 MAX(PartAttributeValue) 
 FOR AttributeDesc IN ('+ @columns +')
 ) AS PVT;';

 -- execute the dynamic SQL
 EXECUTE sp_executesql @sql;
END

Answer 1

You can't make a view out of that. A view is just like a table: it has a constant set of columns with well-defined names. You procedure seems to be running a dynamic pivot - which is a non-relational operation - which can produce a different number of columns with different names each time.

It is possible to write static pivot queries, but then you will need to accept static names for your columns and a static number of columns.