Click here to Skip to main content
12,954,280 members (66,561 online)
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
Maciej Los 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
OriginalGriff 6,524
CHill60 3,490
Maciej Los 3,123
ppolymorphe 2,030
Jochen Arndt 1,975

Advertise | Privacy | Mobile
Web02 | 2.8.170525.1 | Last Updated 13 Feb 2013
Copyright © CodeProject, 1999-2017
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