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

Don Petry 21 Reputation points
2022-04-15T21:57:05.827+00:00

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
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,052 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 103.2K Reputation points MVP
    2022-04-15T22:05:19.113+00:00

    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.


0 additional answers

Sort by: Most helpful