数据透视表出现问题

Question

首次过帐。我是透视表的新手，已经找到了几个关于如何创建它们的答案，但我似乎无法让我的答案发挥作用。数据透视表是正确的方法吗？

我想做的是把所有的列都做成行，然后这些行就变成了列。基本上将桌子转90度。请参见图像。

感谢

基本查询结果和结果的图像我正在尝试获取

基本查询

SELECT        dbo.EstimateDetails.EstimateDetailID,
dbo.EstimateDetails.QtyOrdered,
dbo.EstimateDetails.NetPrice, 
dbo.EstimateDetails.ExtendedPrice, 
dbo.EstimateDetails.EstimateID, 
dbo.UserDefined_Custom.UserDefined1, 
dbo.UserDefined_Custom.UserDefined2,
dbo.EstimateDetails.OrdUOMID, 
dbo.Estimate.EstimateNo, 
dbo.EstimateDetails.[LineNo] AS Line
FROM            dbo.EstimateDetails 
INNER JOIN dbo.Estimate 
ON dbo.EstimateDetails.EstimateID = dbo.Estimate.EstimateID 
LEFT OUTER JOIN dbo.UserDefined_Custom 
ON dbo.EstimateDetails.ItemSpecID = dbo.UserDefined_Custom.ItemSpecID
where dbo.EstimateDetails.EstimateID = 141865


                                                                                                           

我现在的处境很糟糕。

Pivot尝试的结果

Select 
Pvt.[1], Pvt.[2], Pvt.[3], Pvt.[4],Pvt.[5],Pvt.[6] 
from
(SELECT        dbo.EstimateDetails.EstimateDetailID,
dbo.EstimateDetails.QtyOrdered,
dbo.EstimateDetails.NetPrice, 
dbo.EstimateDetails.ExtendedPrice, 
dbo.EstimateDetails.EstimateID, 
dbo.UserDefined_Custom.UserDefined1, 
dbo.UserDefined_Custom.UserDefined2,
dbo.EstimateDetails.OrdUOMID, 
dbo.Estimate.EstimateNo, 
dbo.EstimateDetails.[LineNo] AS Line
FROM            dbo.EstimateDetails 
INNER JOIN dbo.Estimate 
ON dbo.EstimateDetails.EstimateID = dbo.Estimate.EstimateID 
LEFT OUTER JOIN dbo.UserDefined_Custom 
ON dbo.EstimateDetails.ItemSpecID = dbo.UserDefined_Custom.ItemSpecID
where dbo.EstimateDetails.EstimateID = 141865
) AS Src
PIVOT
(
Max(EstimateDetailID)
for Line in ([1], [2], [3], [4],[5],[6])
) AS Pvt

Answer 1

您需要一个完整的转置，因此首先取消透视，然后枢轴。请注意，您需要将所有列转换为通用类型。

我个人倾向于使用CROSS APPLY而不是UNPIVOT，我发现它更清晰。

您还应该为表使用别名，这会使查询更易于编写和读取。

Select 
Pvt.Col AS Line, Pvt.[1], Pvt.[2], Pvt.[3], Pvt.[4],Pvt.[5],Pvt.[6] 
from (
SELECT
ed.[LineNo] AS Line,
Upvt.Col,
Upvt.value
FROM dbo.EstimateDetails AS ed
INNER JOIN dbo.Estimate AS e
ON ed.EstimateID = e.EstimateID 
LEFT OUTER JOIN dbo.UserDefined_Custom AS udc
ON ed.ItemSpecID = udc.ItemSpecID
CROSS APPLY (
('EstimateDetailID', CAST(ed.EstimateDetailID AS varchar(50)),
('QtyOrdered',       CAST(ed.QtyOrdered AS varchar(50)),
('NetPrice',         CAST(ed.NetPrice AS varchar(50)), 
('ExtendedPrice',    CAST(ed.ExtendedPrice AS varchar(50)), 
('EstimateID',       CAST(ed.EstimateID AS varchar(50)), 
('UserDefined1',     CAST(udc.UserDefined1 AS varchar(50)), 
('UserDefined2',     CAST(udc.UserDefined2 AS varchar(50)),
('OrdUOMID',         CAST(ed.OrdUOMID AS varchar(50)), 
('EstimateNo',       CAST(e.EstimateNo AS varchar(50)) 
) AS Upvt(Col, Value)
where ed.EstimateID = 141865
) AS Src
PIVOT
(
Max(Value)
for Line in ([1], [2], [3], [4], [5], [6])
) AS Pvt

---

老实说，现在看它，我意识到你真的只是为了显示而转换它，所以列名实际上没有意义(例如Line中有列名(。我建议您在客户端应用程序中这样做，T-SQL确实不适合这样做。