Can Pivot query do
-
Hi,
I am trying to create a report as shown in Fig3. for the tables data shown in Fig1. & Fig2. I was trying to get the output using Pivot. To certain extent I was able to show the result in report which is colored yellow using dynamic pivot but not all columns as shown in report.
Below is the Pivot Query
--------------------------------
SELECT * FROM (select o.orderid, o.OrdDate, o.Customer, o.OrdValue, d.Prod, d.Category
from [Order] o
inner join OrderDetail d on o.OrderId=d.OrderId) AS A
pivot( max (prod) for category in ([CATE-1],[CATE-2],[CATE-3],[CATE-4]) )as pvt
--------------------------------
Can anyone provide some idea to get the output of this report
Fig1
Fig2.
Fig3.
Thanks
a_k93
-
The short answer is NO.
The longer answer is YES. (by one character :-D)
In order to produce the data in table 3, you need to UNPIVOT the data in table 2 first so that you have each comment type as a field. you can then join the order headers to the productline-comment table and PIVOT the results back up again.
-
You can probably do this by not using PIVOT but some other pivoting technique,
Please post the tables and sample data as CREATE TABLE and INSERT statements, so that I can write and test some code for you.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Hi,
Below is the script generated for 2 tables
CREATE TABLE [dbo].[Order](
[OrderId] [int] IDENTITY(1,1) NOT NULL,
[OrdDate] [date] NOT NULL,
[Customer] [varchar](50) NOT NULL,
[OrdValue] [numeric](18, 2) NOT NULL,
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
[OrderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Order] ([OrdDate], [Customer], [OrdValue]) VALUES (CAST(0x94360B00 AS Date), N'XYZ', CAST(11000.00 AS Numeric(18, 2)))
INSERT [dbo].[Order] ([OrdDate], [Customer], [OrdValue]) VALUES (CAST(0xDD3A0B00 AS Date), N'ABC', CAST(5000.00 AS Numeric(18, 2)))
go
CREATE TABLE [dbo].[OrderDetail](
[OrdDetId] [int] IDENTITY(1,1) NOT NULL,
[OrderId] [int] NOT NULL,
[Prod] [varchar](50) NULL,
[Category] [varchar](50) NULL,
[Rate] [numeric](18, 2) NULL,
[Qty] [int] NULL,
[BuyerComment] [varchar](50) NULL,
[SellerComment] [varchar](50) NULL,
[MfgComment] [varchar](50) NULL,
CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED
(
[OrdDetId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[OrderDetail] ([OrderId], [Prod], [Category], [Rate], [Qty], [BuyerComment], [SellerComment], [MfgComment]) VALUES (1, N'P1', N'CATE-1', CAST(500.00 AS Numeric(18, 2)), 2, N'good Quality', N'good quality', N'Best Quality')
INSERT [dbo].[OrderDetail] ([OrderId], [Prod], [Category], [Rate], [Qty], [BuyerComment], [SellerComment], [MfgComment]) VALUES (1, N'P2', N'CATE-2', CAST(750.00 AS Numeric(18, 2)), 2, N'Poor Quality', N'good quality', N'Delux Quality')
INSERT [dbo].[OrderDetail] ([OrderId], [Prod], [Category], [Rate], [Qty], [BuyerComment], [SellerComment], [MfgComment]) VALUES (1, N'P3', N'CATE-3', CAST(1000.00 AS Numeric(18, 2)), 4, N'good quality', N'good quality', N'Best Quality')
INSERT [dbo].[OrderDetail] ([OrderId], [Prod], [Category], [Rate], [Qty], [BuyerComment], [SellerComment], [MfgComment]) VALUES (1, N'P4', N'CATE-4', CAST(1500.00 AS Numeric(18, 2)), 3, N'Average', N'good quality', N'Supreme Quality')
INSERT [dbo].[OrderDetail] ([OrderId], [Prod], [Category], [Rate], [Qty], [BuyerComment], [SellerComment], [MfgComment]) VALUES (2, N'P1', N'CATE-1', CAST(500.00 AS Numeric(18, 2)), 10, N'Average', N'good quality', N'Best Quality')
-
Have you tried a crosstab query? Immensely useful technique to learn, easier to figure out than PIVOT (and usually faster too).
This excellent article by Jeff Moden [/url]shows you how it's done.
Post back if you get stuck and need help.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Would this be what you are looking for?
SELECT o.OrderId,MAX(CASE WHEN od.Category = 'CATE-1' THEN od.Prod END) AS [Cate-1],
MAX(CASE WHEN od.Category = 'CATE-2' THEN od.Prod END) AS [Cate-2],
MAX(CASE WHEN od.Category = 'CATE-3' THEN od.Prod END) AS [Cate-3],
MAX(CASE WHEN od.Category = 'CATE-4' THEN od.Prod END) AS [Cate-4],
MAX(CASE WHEN od.Category = 'CATE-1' THEN od.BuyerComment END) AS [P1 Buyer Comment],
MAX(CASE WHEN od.Category = 'CATE-1' THEN od.SellerComment END) AS [P1 Seller Comment],
MAX(CASE WHEN od.Category = 'CATE-1' THEN od.MfgComment END) AS [P1 Mfg Comment],
MAX(CASE WHEN od.Category = 'CATE-2' THEN od.BuyerComment END) AS [P2 Buyer Comment],
MAX(CASE WHEN od.Category = 'CATE-2' THEN od.SellerComment END) AS [P2 Seller Comment],
MAX(CASE WHEN od.Category = 'CATE-2' THEN od.MfgComment END) AS [P2 Mfg Comment],
MAX(CASE WHEN od.Category = 'CATE-3' THEN od.BuyerComment END) AS [P3 Buyer Comment],
MAX(CASE WHEN od.Category = 'CATE-3' THEN od.SellerComment END) AS [P3 Seller Comment],
MAX(CASE WHEN od.Category = 'CATE-3' THEN od.MfgComment END) AS [P3 Mfg Comment],
MAX(CASE WHEN od.Category = 'CATE-4' THEN od.BuyerComment END) AS [P4 Buyer Comment],
MAX(CASE WHEN od.Category = 'CATE-4' THEN od.SellerComment END) AS [P4 Seller Comment],
MAX(CASE WHEN od.Category = 'CATE-4' THEN od.MfgComment END) AS [P4 Mfg Comment]
FROM dbo.[Order] AS o
INNER JOIN dbo.OrderDetail AS od
ON od.OrderId = o.OrderId
GROUP BY o.OrderId;
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
That might work but it is data-centric rather than schema-centric. When the company introduces a new product line and creates 'CATE-5' you have to modify the query as there is no MAX() condition for the new CATE.
I like UNPIVOT because it is schema centric; you tell it which columns(fields) to unpivot: I don't much care for the syntax, but I like the concept ;-).
PIVOT is more data centric as you need to know the data values to generate the columns. Where this information is not known you have to use dynamic SQL to generate the list of pivot columns and then run sp_execute on the dynamic script.
-
If the columns required change when the data changes, then you need what is called a "dynamic crosstab". Google for it. Personally, I hate them - they require dynamic SQL which always introduces some security risk, and they are clumsy and hard to maintain. Most important, they are a function that should not be done in the relational engine, so I always try to push this kind of functionality to the front end.
For the record, PIVOT and UNPIVOT also are static. If you use them, you still have to change the code when new values pop up. And they are not quite as versatile as the type of solution I posted.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
I agree that they are static, but I was referring more to the source of the script rules, If you use UNPIVOT, you do that on named fields. In most cases you would hope the schema will change less often than the data!
as I noted earlier, PIVOT is more complex because the columns depend on the individual data values. Where those values are fixed then you can write static PIVOT commands
In Jeff's [excellent] examples, he is manipulating temporal data and it is pretty unlikely that the United Nations, NATO, the EU or ISO will be introducing a fifth quarter to the year any time soon so the cross-tab methodology makes sense there. Where you do not know what the data will be you have to go dynamic whether using cross-tab or PIVOT.
-
Thanks everybody, for their valuable contribution on the above subject.
I have created the below sp to accomplish the above task. This adds category dynamically to the main query.
Create proc [dbo].[usp_orders_catewise_crstab]
as
begin
declare @sql_cate nvarchar(max)
declare @sql_qry nvarchar(max)
set @sql_cate=''
set @sql_qry=''
select distinct Category into #tmp from OrderDetail
select @sql_cate = @sql_cate + ' Max(case when od.Category = ''' + Category +
''' THEN od.Prod END) as [' + Category + '],'
from #tmp
set @sql_cate=LEFT(@sql_cate,len(@sql_cate)-1)
print @sql_cate
select @sql_qry =
N' SELECT o.OrderId,' + @sql_cate + ' FROM dbo.[Order] AS o
INNER JOIN dbo.OrderDetail AS od
ON od.OrderId = o.OrderId
GROUP BY o.OrderId'
PRINT CAST(@sql_qry AS NTEXT)
EXEC sp_executesql @sql_qry
end
-
goodjob a_k. Glad that the suggestions were useful to you and for posting your resulting code for others to learn from. as you can see dynamic pivot / unpivot / cross-tabs are a PITA as you have to write code that writes code and as stated previously dynamic code is vulnerable to injection attacks (google 'little bobby tables') if someone was to set up a category description of "CATE-5; Delete FROM Products; '''' " you might be in big trouble if you ran that as part of the concatenated code as SQL would see the semi-colon as a statement terminator and then run the next statement independently - which would be bad!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply