Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Using Pivot

Posted on 2008-11-11
14
Medium Priority
?
379 Views
Last Modified: 2012-05-05
Hi experts,

Let say i have the following table:

   ID                                   Period                                    Status
-------                            -------------                              -----------
1                                  January                                      P
1                                  March                                        A
1                                  Febuary                                     W
2                                  March                                        W
2                                  January                                     A
2                                  Febuary                                    A
3                                  Febuary                                   P
3                                  January                                      P
3                                   March                                      W

How do i convert this table into the following table  using Pivot ( or any other way)


ID                       January          Febuary                March
1                            P                     W                         A
2                           A                      A                         W
3                            P                     P                          W

Something like that for using ADO.net queries
0
Comment
Question by:aaxen
  • 7
  • 7
14 Comments
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 22932836
Something like this should work:
+Pivot by Period
+Take max value of Status -- should be highest letter
+Grouped by ID
TRANSFORM Max([Status]) AS MaxOfStatus 
SELECT [ID] 
FROM M_Sales 
GROUP BY [ID]
PIVOT Period In ("January","February","March","April","May","June","July","August","September","October","November","December");

Open in new window

0
 

Author Comment

by:aaxen
ID: 22932873
let me try
0
 

Author Comment

by:aaxen
ID: 22932953
hmm doesnt work :(

This is my full code
Private Sub btn_Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Search.Click
 
        Try
            Dim ds1 As New DataSet
            Dim SQL As String
            SQL = "TRANSFORM Max([Status]) AS MaxOfStatus "
            SQL &= "Select Case [Property_ID]"
            SQL &= "FROM Payment"
            SQL &= "GROUP BY [Property_ID]"
            SQL &= "PIVOT Period In ('January','February','March','April','May','June','July','August','September','October','November','December')"
            ds1.Clear()
            MessageBox.Show(SQL)
            OleDbDataAdapter1.SelectCommand.CommandText = SQL
            OleDbDataAdapter1.Fill(ds1, "rentdue")
            DataGrid2.DataSource = ds1.Tables("rentdue")
        Catch
        End Try
 
    End Sub

Open in new window

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 61

Expert Comment

by:Kevin Cross
ID: 22933102
You have a CASE statement there for some reason.

SQL &= "Select Case [Property_ID]"

This is MS Access as the zone suggests, correct?

Removing 'Case' from the select statement should fix this, but if not take a look at this line:
SQL &= "PIVOT Period In ('January','February','March','April','May','June','July','August','September','October','November','December')"

To get double quotes in VB you have to use two to get one (i.e. ""), so code would become:

SQL &= "PIVOT Period In (""January"",""February"",""March"",""April"",""May"",""June"",""July"",""August"",""September"",""October"",""November"",""December"")"

Hope that helps.

If this is SQL Server backend, then you will have to construct SQL much differently so please advise.
0
 

Author Comment

by:aaxen
ID: 22933138
still doesnt work...
Private Sub btn_Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Search.Click
 
        Try
            Dim ds1 As New DataSet
            Dim SQL As String
            SQL = "TRANSFORM Max(status) AS MaxOfStatus "
            SQL &= "Select Property_ID"
            SQL &= "FROM Payment"
            SQL &= "GROUP BY Property_ID"
            SQL &= "PIVOT Period (""January"",""February"",""March"",""April"",""May"",""June"",""July"",""August"",""September"",""October"",""November,""December"")"
            ds1.Clear()
            MessageBox.Show(SQL)
            OleDbDataAdapter1.SelectCommand.CommandText = SQL
            OleDbDataAdapter1.Fill(ds1, "rentdue")
            DataGrid2.DataSource = ds1.Tables("rentdue")
        Catch
        End Try
 
    End Sub

Open in new window

0
 

Author Comment

by:aaxen
ID: 22933150
suppose to be query for vb.net using ado to connect acess so i assume its the same query send to acess
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 22933236
What error message are you getting?

Need more details on "doesn't work", please...
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 22933245
BTW, you removed the IN statement from the PIVOT:

SQL &= "PIVOT Period (""January"",""February"",""March"",""April"",""May"",""June"",""July"",""August"",""September"",""October"",""November,""December"")"

Should be:

SQL &= "PIVOT Period IN(""January"",""February"",""March"",""April"",""May"",""June"",""July"",""August"",""September"",""October"",""November,""December"")"
0
 

Author Comment

by:aaxen
ID: 22933246
i click the button the data grid doesnt show any table
0
 

Author Comment

by:aaxen
ID: 22933259
tried that as well still no table show...
0
 
LVL 61

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 22933268
Here, copy and past this directly over same code:
Private Sub btn_Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Search.Click
 
        Try
            Dim ds1 As New DataSet
            Dim SQL As String
            SQL = "TRANSFORM Max(status) AS MaxOfStatus"
            SQL &= " Select Property_ID"
            SQL &= " FROM Payment"
            SQL &= " GROUP BY Property_ID"
            SQL &= " PIVOT Period IN (""January"",""February"",""March"",""April"",""May"",""June"",""July"",""August"",""September"",""October"",""November,""December"");"
            ds1.Clear()
            MessageBox.Show(SQL)
            OleDbDataAdapter1.SelectCommand.CommandText = SQL
            OleDbDataAdapter1.Fill(ds1, "rentdue")
            DataGrid2.DataSource = ds1.Tables("rentdue")
        Catch
        End Try
 
    End Sub

Open in new window

0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 22933304
You have a Try/Catch, but are not doing anything in the Catch.  You may want to add something while debugging to display what the error message is or comment out the try/catch code until working.
0
 

Author Comment

by:aaxen
ID: 22935570
Ahhh already got it.. thanks Its because of the space between each sql &= line
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 22935791
Glad you saw that is what I changed above.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Implementing simple internal controls in the Microsoft Access application.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which ā€¦
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final pā€¦

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question