show same date data in 1 row

[budihermawan]

I have simple problem but until now i still cannot figure it out.

I have data in the database as follows :

2010-02-01 07:40
2010-02-01 11:30
2010-02-02 08:03
2010-02-02 01:40

I need to display the data in gridview or reportviewer as follow:

2010-02-01 07:40 11:30
2010-02-01 08:03 01:40

Can somehow enlighten me on how to arrange the data in 1 row based on date in gridview or reportviewer.
A sample source code would be highly appreciated.

 

[jmcilhinney]

Your example is just an example. It doesn't tell us what the rules are governing how the data is to be transformed. Are we to assume that there will always be two records per date? Do you always want the first time in the second column and the second time in the third column?

 

[budihermawan]

Do you always want the first time in the second column and the second time in the third column?

Yes i want to transform the data into this way. No matter how many data on the same date, i want to arrange it in 1 horizontal line which is grouped by date.

 

[cjard]

It's called a pivot query, but it doesnt seem to have a header

You might find it easier to pivot the data manually yourself

Make a datatable in the designer that has a DAte primary key row, I'll call it PivoterTable, it has one column called theDate:

'get the data
myTableAdapterX.Fill(myXTable)

Dim pT as New PivoterTable

For Each xRo as XDataRow in myXTable
  Dim pRo as PivoterDataRow = pT.FindByTheDate(xRo.TheDate)
  If pRo is Nothing Then
    pT.AddNewPivoterRow(xRo.TheDate, xRo.TheTime)
  Else
    pRo.TheTimes += xRo.TheTime
  End If
Next

If you want the times to appear in separate columns:

'get the data
myTableAdapterX.Fill(myXTable)

Dim pT as New PivoterTable

For Each xRo as XDataRow in myXTable
  Dim pRo as PivoterDataRow = pT.FindByTheDate(xRo.TheDate)
  If pRo is Nothing Then
    pT.AddNewPivoterRow(xRo.TheDate, xRo.TheTime)
  Else
    Dim i as Integer = 1
    While i < pT.Columns.Count-1 AndAlso Not pRo.IsNull(i)
      i += 1
    End While
    If i = pT.Columns.Count Then
      pT.Columns.Add("Time"+i)
    End If
    pRo(i) = xRo.TheTime
  End If
Next

Note, this code is untested!

 

[cjard]

PS; investigate if your DB can do a pivot with no header row first, as it may save you some headache