Group By Problem.. on Retrieving Data from Table

开发者_如何学Go

I am Beginner in Sql, I m trying SQL Group By statement, my requirement is below.

Table Structure  <br> 
USerID  int <br> 
BrowserName nvarchar(200) <br> 
BrowserVersion nvarchar <br> 
LoggedOntime  Time 

here is my requirement. I want select the all details based on the user id along with it should Group By Browser Name. I tried the follwing query its returining. but how to use this in visual studio to Display in Gridview as Group FOr each Browser. when the user Clicks a group in gridview it should display all details of that browser.

for eg. If i click FireFox then It should shows the detail for Firfox Group with all Version, And LoggedOnTime Details for the particular user id

select  
  BrowserName, 
  LoggedOnTime, 
  BrowserVersion
from 
  BrowserSession 
  inner join Users on UserId = Users.UsersId
where 
  UserId=21 
group by 
  BrowserName, 
  LoggedOnTime,
  BrowserVersion 
order by 
  BrowserName, 
  LoggedOnTime

---

If the query is static you can do like this, otherwise you can use parameters on the query:

        SqlConnection con = new SqlConnection(connectionString);

        string sqlQuery = "select  BrowserName, LoggedOnTime, BrowserVersion"
                            + " from BrowserSession inner join Users on UserId=Users.UsersId"
                            + " where UserId=21 group by BrowserName, LoggedOnTime,BrowserVersion order by BrowserName, LoggedOnTime";

        SqlCommand cmd = new SqlCommand(sqlQuery, con);
        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();

        adapter.Fill(ds, "TableName");

        GridView1.DataSource = ds;
        GridView1.DataBind();

Using Parameters:

        string sqlQuery = "select  BrowserName, LoggedOnTime, BrowserVersion"
                            + " from BrowserSession inner join Users on UserId=Users.UsersId"
                            + " where UserId=@userID group by BrowserName, LoggedOnTime,BrowserVersion order by BrowserName, LoggedOnTime";

        SqlCommand cmd = new SqlCommand(sqlQuery, con);
        cmd.Parameters.AddWithValue("@userID", 21);

---

You can create data grid for every group and insert into the parent grid. Bind the sub-data grid to the specific data group.

Here are few links.

1. Step by step I was not able to access the live demo. This looks like what you want.
2. 4 Guys from Rolla
3. Report kind of layout in asp.net 1.1 CodeProject