A Question About Datagrid And Sql Query

I have 3 tables in database shown below. And I want to make a report just like shown link below. How can I do it with datagrid or datalist? Which one is the best chois? I have tried to do it for a week.

http://img123.imageshack.us/my.php?image=61519307xx5.jpg

COMPANY: ID_COMPANY, COMPANY_NAME

PRODUCT: ID_PRODUCT, PRODUCT_NAME

PRODUCT_SALE: ID_COMPANY, ID_PRODUCT, SALE_COUNT

From stackoverflow

• What version of SQL are you running? Using PIVOT might be a quick way to get the data into the form you want, and then you can use the generic DataGrid to just display the data in it's (almost) "raw" form - that is, the way the data is presented by the SQL server. You could then think of the DataGrid more like a spreadsheet than representative of a data table as found in a database.

  Here's a good starter document for how to use PIVOT to represent the data in the form you're after:

  http://www.tsqltutorials.com/pivot.php
  

  Of course, I think this might only be available in SQL 2005... so if you're running an older version this may be no help.

• Following will work if product numbers are static:

  < asp:gridview >

  < columns >

    < asp : boundfield datafield = "companyname" itemstyle-headertext="" / >
  
    < asp : boundfield datafield = "SALE_COUNT" itemstyle-headertext= '<%# FunctionToLoadurproduct(product1) %>' / >
  
    < asp : boundfield datafield = "SALE_COUNT" itemstyle-headertext= '<%# FunctionToLoadurproduct(product1) %>' / >
  

  and so on...

  < /columns>

  < /gridview >

  manage the product sales according to the product id in ur query using inline quries.

• Looks like a crosstab report to me. You have 2 options to go about this

  1. Pivot the data in the database and bind that to a datagrid. The exact syntax of doing this will vary based on the database engine you're using. Here's an example in SQL
  2. Use a reporting tool that can do cross tabs based on a simple join, like SQL Reporting Services, Crystal Reports, XtraReports, etc

• I'm so sorry to haven't warn you, I use oracle 10 g to do it. Is the pivot, can be use for oracle too?

• On Oracle 10g you're going to need the MODEL extension, it's not a world different than PIVOT in SQL but obviously Oracle 10g does things its own way:

  http://technology.amis.nl/blog/300/pivoting-in-sql-using-the-10g-model-clause

• Hi!

  I could do it, with your helps. However Now I have a small problem too.

  When I write query with pivot, products' name become column header. if a product name's length is bigger than 30 character, Oracle don't accept it as a column header. So I have croped and make the product names 30 character to solve this problem. After that a problem occured too.

  When I crop product name as 30 character, some products become same name and "ORA-00918: column ambiguously defined" error message occured.

  In this case what can be done?

• Use nested Repeaters Control. The outer repeater will repeat Companies and the inner will repeat Products.

• Can you give me a template html code about nested repeater? What should design be?

• Use a standard sql query (pivots are expensive in terms of performance) and create a custom pivot function in your server side code. Here are a couple of examples.

  ''' <summary>
  ''' Pivots columnX as new columns for the X axis (must be unique values) and the remaining columns as 
  ''' the Y axis. Optionally can include columns to exclude from the Y axis.
  ''' </summary>
  ''' <param name="dt"></param>
  ''' <param name="columnX"></param>
  ''' <param name="columnsToIgnore"></param>
  ''' <returns>DataTable</returns>
  ''' <remarks></remarks>
  Public Shared Function Pivot(ByVal dt As DataTable, ByVal columnX As String, ByVal ParamArray columnsToIgnore As String()) As DataTable
  
      Dim dt2 As New DataTable()
  
      If columnX = "" Then
          columnX = dt.Columns(0).ColumnName
      End If
  
      'Add a Column at the beginning of the table 
      dt2.Columns.Add(columnX)
  
      'Read all DISTINCT values from columnX Column in the provided DataTable 
      Dim columnXValues As New List(Of String)()
  
      'Create the list of columns to ignore 
      Dim listColumnsToIgnore As New List(Of String)()
      If columnsToIgnore.Length > 0 Then
          listColumnsToIgnore.AddRange(columnsToIgnore)
      End If
  
      If Not listColumnsToIgnore.Contains(columnX) Then
          listColumnsToIgnore.Add(columnX)
      End If
  
      ' Add the X axis columns
      For Each dr As DataRow In dt.Rows
          Dim columnXTemp As String = dr(columnX).ToString()
          If Not columnXValues.Contains(columnXTemp) Then
              columnXValues.Add(columnXTemp)
              dt2.Columns.Add(columnXTemp)
          Else
              Throw New Exception("The inversion used must have unique values for column " + columnX)
          End If
      Next
  
      'Add a row for each non-columnX of the DataTable 
      For Each dc As DataColumn In dt.Columns
          If Not columnXValues.Contains(dc.ColumnName) AndAlso Not listColumnsToIgnore.Contains(dc.ColumnName) Then
              Dim dr As DataRow = dt2.NewRow()
              dr(0) = dc.ColumnName
              dt2.Rows.Add(dr)
          End If
      Next
  
      'Complete the datatable with the values 
      For i As Integer = 0 To dt2.Rows.Count - 1
          For j As Integer = 1 To dt2.Columns.Count - 1
              dt2.Rows(i)(j) = dt.Rows(j - 1)(dt2.Rows(i)(0).ToString()).ToString()
          Next
      Next
  
      Return dt2
  
  End Function
  
  ''' <summary>
  ''' Can pivot any column as X, any column as Y, and any column as Z. Sort on X, sort on Y and optionally, the 
  ''' values at the intersection of x and y (Z axis) can be summed.
  ''' </summary>
  ''' <param name="dt"></param>
  ''' <param name="columnX"></param>
  ''' <param name="columnY"></param>
  ''' <param name="columnZ"></param>
  ''' <param name="nullValue"></param>
  ''' <param name="sumValues"></param>
  ''' <param name="xSort"></param>
  ''' <param name="ySort"></param>
  ''' <returns>DataTable</returns>
  ''' <remarks></remarks>
  Public Shared Function Pivot(ByVal dt As DataTable, ByVal columnX As String, ByVal columnY As String, ByVal columnZ As String, _
      ByVal nullValue As String, ByVal sumValues As Boolean, ByVal xSort As Sort, ByVal ySort As Sort) As DataTable
  
      Dim dt2 As New DataTable()
      Dim tickList As List(Of Long) = Nothing
  
      If columnX = "" Then
          columnX = dt.Columns(0).ColumnName
      End If
  
      'Add a Column at the beginning of the table 
      dt2.Columns.Add(columnY)
  
      'Read all DISTINCT values from columnX Column in the provided DataTable 
      Dim columnXValues As New List(Of String)()
      Dim cols As Integer = 0
  
      For Each dr As DataRow In dt.Rows
          If dr(columnX).ToString.Contains("'") Then
              dr(columnX) = dr(columnX).ToString.Replace("'", "")
          End If
          If Not columnXValues.Contains(dr(columnX).ToString) Then
              'Read each row value, if it's different from others provided, 
              'add to the list of values and creates a new Column with its value. 
              columnXValues.Add(dr(columnX).ToString)
          End If
      Next
  
      'Sort X if needed
      If Not xSort = Sort.None Then
          columnXValues = SortValues(columnXValues, xSort)
      End If
  
      'Add columnX
      For Each s As String In columnXValues
          dt2.Columns.Add(s)
      Next
  
      'Verify Y and Z Axis columns were provided 
      If columnY <> "" AndAlso columnZ <> "" Then
          'Read DISTINCT Values for Y Axis Column 
          Dim columnYValues As New List(Of String)()
  
          For Each dr As DataRow In dt.Rows
              If dr(columnY).ToString.Contains("'") Then
                  dr(columnY) = dr(columnY).ToString.Replace("'", "")
              End If
              If Not columnYValues.Contains(dr(columnY).ToString()) Then
                  columnYValues.Add(dr(columnY).ToString())
              End If
          Next
  
          ' Now we can sort the Y axis if needed. 
          If Not ySort = Sort.None Then
              columnYValues = SortValues(columnYValues, ySort)
          End If
  
          'Loop all Distinct ColumnY Values
          For Each columnYValue As String In columnYValues
              'Create a new Row 
              Dim drReturn As DataRow = dt2.NewRow()
              drReturn(0) = columnYValue
              Dim rows As DataRow() = dt.[Select](columnY + "='" + columnYValue + "'")
  
              'Read each row to fill the DataTable 
              For Each dr As DataRow In rows
                  Dim rowColumnTitle As String = dr(columnX).ToString()
  
                  'Read each column to fill the DataTable 
                  For Each dc As DataColumn In dt2.Columns
                      If dc.ColumnName = rowColumnTitle Then
                          'If sumValues, try to perform a Sum 
                          'If sum is not possible due to value types, use the nullValue string
                          If sumValues Then
                              If IsNumeric(dr(columnZ).ToString) Then
                                  drReturn(rowColumnTitle) = Val(drReturn(rowColumnTitle).ToString) + Val(dr(columnZ).ToString)
                              Else
                                  drReturn(rowColumnTitle) = nullValue
                              End If
                          Else
                              drReturn(rowColumnTitle) = dr(columnZ).ToString
                          End If
                      End If
                  Next
              Next
  
              dt2.Rows.Add(drReturn)
  
          Next
      Else
          Throw New Exception("The columns to perform inversion are not provided")
      End If
  
      'if nullValue param was provided, fill the datable with it 
      If nullValue <> "" Then
          For Each dr As DataRow In dt2.Rows
              For Each dc As DataColumn In dt2.Columns
                  If dr(dc.ColumnName).ToString() = "" Then
                      dr(dc.ColumnName) = nullValue
                  End If
              Next
          Next
      End If
  
      Return dt2
  
  End Function
  
  ''' <summary>
  ''' Sorts a list of strings checking to see if they are numeric or date types.
  ''' </summary>
  ''' <param name="list"></param>
  ''' <param name="srt"></param>
  ''' <returns></returns>
  ''' <remarks></remarks>
  Private Shared Function SortValues(ByVal list As List(Of String), ByVal srt As Sort) As List(Of String)
  
      Dim tickList As List(Of Long) = Nothing
      Dim dblList As List(Of Double) = Nothing
  
      ' Figure out how to sort columnX
      For Each s As String In list
          Dim colDate As Date = Nothing
          If Date.TryParse(s, colDate) Then
              tickList = New List(Of Long)
              Exit For
          End If
      Next
  
      Dim dateTicks As Long
  
      If Not tickList Is Nothing Then
          For Each s As String In list
              dateTicks = DateTime.Parse(s).Ticks
              If Not tickList.Contains(dateTicks) Then
                  tickList.Add(dateTicks)
              End If
          Next
  
          If srt = Sort.DESC Then
              tickList.Sort()
              tickList.Reverse()
          ElseIf srt = Sort.ASC Then
              tickList.Sort()
          End If
  
          list.Clear()
          For Each lng As Long In tickList
              list.Add(New Date(lng).ToString("G"))
          Next
      Else
          Dim dbl As Double = Nothing
  
          For Each s As String In list
              If IsNumeric(s) Then
                  dblList = New List(Of Double)
              End If
          Next
  
          If Not dblList Is Nothing Then
              'Doubles or Integers
              For Each s As String In list
                  dbl = Val(s)
                  If Not dblList.Contains(dbl) Then
                      dblList.Add(dbl)
                  End If
              Next
  
              If srt = Sort.DESC Then
                  dblList.Sort()
                  dblList.Reverse()
              ElseIf srt = Sort.ASC Then
                  dblList.Sort()
              End If
  
              list.Clear()
              For Each d As Double In dblList
                  list.Add(d.ToString)
              Next
          Else
              'Strings
              If srt = Sort.DESC Then
                  list.Sort()
                  list.Reverse()
              ElseIf srt = Sort.ASC Then
                  list.Sort()
              End If
          End If
  
      End If
  
      Return list
  
  End Function