Click here to Skip to main content
13,559,281 members
Rate this:
Please Sign up or sign in to vote.
See more:
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
        DataGridView2.DataSource = Dset6
Posted 1-Feb-13 9:04am
Updated 1-Feb-13 9:06am
Sandeep-Mewara 1-Feb-13 15:09pm
1. What error?
2. You need to be clear on what output format you are expecting data
3. Is PIVOT really needed?
Navas-Khanj 2-Feb-13 1:08am
My Error Is : i declar PIVOT code in (VFeeType)that one get data from table.
my DGV Want Display
Regno SName Class (FeeType Data(VFeeType)) For Example

Regno SName Class Jan Feb Mar
how can i get (Jan Feb Mar , etc..) TableFiled want show to DGV.. Please tell me some idea how can i get..

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

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:[^][^]

Secondly, NEVER do it in a code! Use stored procedure on a server side.
More about stored procedures:[^][^]

Probably, your stored procedure should looks like:
    @class NVARCHAR(20)


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

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

Navas-Khanj 14-Feb-13 12:04pm
losmac 14-Feb-13 12:09pm
You're welcome ;)
If my answer was helpful, please, rate it and mark as solved.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web02 | 2.8.180515.1 | Last Updated 13 Feb 2013
Copyright © CodeProject, 1999-2018
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100