Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
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
Posted 1-Feb-13 9:04am
Edited 1-Feb-13 9:06am
v2
Comments
Sandeep Mewara at 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 at 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
good
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:
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
  Permalink  
Comments
Navas Khanj at 14-Feb-13 12:04pm
   
Thanks
Maciej Los at 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
0 OriginalGriff 467
1 CPallini 400
2 Sergey Alexandrovich Kryukov 348
3 BillWoodruff 254
4 George Jonsson 251
0 OriginalGriff 5,370
1 CPallini 4,360
2 Sergey Alexandrovich Kryukov 3,822
3 George Jonsson 3,037
4 Gihan Liyanage 2,435


Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 13 Feb 2013
Copyright © CodeProject, 1999-2014
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