DGV显示中的枢轴表错误 [英] Pivot table error in DGV display

问题描述

类。 Feetype 
 I. Jan 
 I. Feb 
 I. Mar 
 II。 Jan 

这是我的表数据。如何在数据透视表上使用Feetype

  Dim  VFeeType  As  字符串 =   SELECT COALESCE（[' + FeeType +']'）FROM FeeMaster1 
  Dim  strSQL6  As  < span class =code-keyword> String  =   select * from（select Regno，FeeType，SName来自FeeTrans的，AmountPaid，Class）FeeTrans PIVOT（['+ VFeeType +'）中[FeeType]的Sum（AmountPaid））为AP，其中Class ='& cboClass.Text&  ' 
  Dim  DaAp6 作为 新 SqlDataAdapter（strSQL6，con）
  Dim  Dset6  As  新 DataTable 
 DaAp6.Fill（Dset6）
 DataGridView2.DataSource = Dset6 

解决方案

首先，使用 SELECT * 在数据透视表中是错误的！数据透视表需要使用至少一列来生成行，并且至少有一列（或更多）生成列标题。

更多关于其中的支点：

http://msdn.microsoft.com/en-US/library/ms177410% 28v = sql.105％29.aspx [ ^ ]

http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=PIVOTData [ ^ ]



其次，绝对不要一个代码！在服务器端使用存储过程。

有关存储过程的更多信息：

http://msdn.microsoft.com/en-us/library/ms190669(v = sql.105）.aspx [ ^ ]

http://msdn.microsoft.com/en-us/library/ms187961.aspx [ ^ ]



可能，您的存储过程应该如下所示：

 创建  PROCEDURE  GetMyPivot（）
  @ class   NVARCHAR （ 20 ）
 
  AS  
  BEGIN  
 
  DECLARE   @cols  NVARCAHR（ 200 ）
 
  SET   @ col  = STUFF（（ SELECT   DISTINCT  ' ]，[' + [FeeType] 
  FROM  FeeMaster1 
  ORDER   BY  ' ]，[' + [FeeType] 
  FOR  XML PATH（' '））， 1 ， 2 ，' '）+ ' <小号pan class =code-string>]' 
 
  SELECT  [Regno]，[SName]，[Class]，  @cols  
  FROM （
  SELECT  [Regno]，[FeeType]，[SName]，[AmountPaid]，[Class] 
  FROM  FeeTrans 
）< span class =code-keyword> AS  DT 
 PIVOT（SUM（[AmountPaid]） FOR  [FeeType]  IN （ @cols ）） as  PT 
 < span class =code-keyword> WHERE  [Class] = @ class 
 
  END  

Class.    Feetype
I.           Jan
I.           Feb
I.           Mar
II.          Jan

This is my table data. How can I use Feetype on pivot

Dim VFeeType As String = "SELECT COALESCE([' + FeeType + ']‘) FROM FeeMaster1"  
Dim strSQL6 As String = "select * from (select Regno, FeeType,SName, AmountPaid,Class from FeeTrans)FeeTrans PIVOT(Sum(AmountPaid) for [FeeType] in (' + VFeeType + ')) as AP where Class='" & cboClass.Text & "'"
        Dim DaAp6 As New SqlDataAdapter(strSQL6, con)
        Dim Dset6 As New DataTable
        DaAp6.Fill(Dset6)
        DataGridView2.DataSource = Dset6

解决方案

First of all, using SELECT * in pivot table is wrong! Pivot tables need to use at least one column to produce rows and at least one (or more) column that produced column-headers.
More about pivots here:
http://msdn.microsoft.com/en-US/library/ms177410%28v=sql.105%29.aspx[^]
http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=PIVOTData[^]

Secondly, NEVER do it in a code! Use stored procedure on a server side.
More about stored procedures:
http://msdn.microsoft.com/en-us/library/ms190669(v=sql.105).aspx[^]
http://msdn.microsoft.com/en-us/library/ms187961.aspx[^]

Probably, your stored procedure should looks like:

CREATE PROCEDURE GetMyPivot()
    @class NVARCHAR(20)

AS
BEGIN

DECLARE @cols NVARCAHR(200)
			
SET @col = STUFF((SELECT DISTINCT '],[' + [FeeType] 
                 FROM FeeMaster1
                 ORDER BY '],[' + [FeeType]
                 FOR XML PATH('')),1,2,'') + ']' 

SELECT [Regno], [SName], [Class], @cols
FROM (
      SELECT [Regno], [FeeType], [SName], [AmountPaid], [Class] 
      FROM FeeTrans
     ) AS DT
PIVOT(SUM([AmountPaid]) FOR [FeeType] IN (@cols)) as PT 
WHERE [Class]=@class 

END

这篇关于DGV显示中的枢轴表错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！