GROUP BY

[Roy Gourgi]

Hi,

Is there a way to group rows by some columns in such a way that I can
clearly see them in a distinguished fashion when I look at a view or table.
This is my code below but when I try using GROUP BY it is not giving me the
desired effect that I would like?

For example, let's say I would like to group by the first 5 columns (i.e.
V1,V2,V3,V4,V5) and let's say the first 3 rows of my view or table contain
the values 1,2,3,4,5 for V1,V2,V3,V4,V5 respectively, and the next 3 rows
contain the values 1,2,3,4,6 for V1,V2,V3,V4,V5 respectively and so on. What
I would like to see when I look at my view or table, is some sort of visible
delineation or break of some sort by the grouping that I chose, so that I do
not have to do it visually myself because I have many records in my view.
See my view below with the 6 rows as an example.

SELECT TOP (100) PERCENT V1, V2, V3, V4, V5, V6, V7, V8, V9, V10
FROM dbo.tblNo1
WHERE (MP <= 30)
ORDER BY V1, V2, V3, V4, V5

These are the 6 rows in my table and so as you can see the first 3 rows
would be in one group and the next 3 rows would be in another group. What I
would like to do is have to sort of way to show a distinction or highlight
between the first 3 rows and the next 3 rows.

1,2,3,4,5, 8,9,11,13,16
1,2,3,4,5, 9,11,12,15,17
1,2,3,4,5, 11,15,19,21,23
1,2,3,4,6, 8,9,12,15,18
1,2,3,4,6, 9,12,16,18,21
1,2,3,4,6, 13,16,19,20,21

TIA
Roy

[Peter Bromberg [C# MVP]]

Roy,
this is clearly a SQL - related question. This is the C Sharp language
newsgroup. Post at the SQL Server group here:

http://msdn.microsoft.com/newsgroups...xp=&sloc=en-us

Peter
--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Roy Gourgi" wrote:

Hi,

Is there a way to group rows by some columns in such a way that I can
clearly see them in a distinguished fashion when I look at a view or table.
This is my code below but when I try using GROUP BY it is not giving me the
desired effect that I would like?

For example, let's say I would like to group by the first 5 columns (i.e.
V1,V2,V3,V4,V5) and let's say the first 3 rows of my view or table contain
the values 1,2,3,4,5 for V1,V2,V3,V4,V5 respectively, and the next 3 rows
contain the values 1,2,3,4,6 for V1,V2,V3,V4,V5 respectively and so on. What
I would like to see when I look at my view or table, is some sort of visible
delineation or break of some sort by the grouping that I chose, so that I do
not have to do it visually myself because I have many records in my view.
See my view below with the 6 rows as an example.

SELECT TOP (100) PERCENT V1, V2, V3, V4, V5, V6, V7, V8, V9, V10
FROM dbo.tblNo1
WHERE (MP <= 30)
ORDER BY V1, V2, V3, V4, V5

These are the 6 rows in my table and so as you can see the first 3 rows
would be in one group and the next 3 rows would be in another group. What I
would like to do is have to sort of way to show a distinction or highlight
between the first 3 rows and the next 3 rows.

1,2,3,4,5, 8,9,11,13,16
1,2,3,4,5, 9,11,12,15,17
1,2,3,4,5, 11,15,19,21,23
1,2,3,4,6, 8,9,12,15,18
1,2,3,4,6, 9,12,16,18,21
1,2,3,4,6, 13,16,19,20,21

TIA
Roy

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Humm, dont see the problem you are facing, it's not a SQL one, as your
query is fine.

It seems to be related to the UI, if so you need to provide more details:
1- Web or Win ?
2- What control r u using to display the data
3- How you store the daa in the APP

cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Roy Gourgi" <ro***@videotron.ca> wrote in message
news:PE*******************@weber.videotron.net...

Hi,

Is there a way to group rows by some columns in such a way that I can
clearly see them in a distinguished fashion when I look at a view or
table. This is my code below but when I try using GROUP BY it is not
giving me the desired effect that I would like?

For example, let's say I would like to group by the first 5 columns (i.e.
V1,V2,V3,V4,V5) and let's say the first 3 rows of my view or table contain
the values 1,2,3,4,5 for V1,V2,V3,V4,V5 respectively, and the next 3 rows
contain the values 1,2,3,4,6 for V1,V2,V3,V4,V5 respectively and so on.
What I would like to see when I look at my view or table, is some sort of
visible delineation or break of some sort by the grouping that I chose, so
that I do not have to do it visually myself because I have many records in
my view. See my view below with the 6 rows as an example.

SELECT TOP (100) PERCENT V1, V2, V3, V4, V5, V6, V7, V8, V9, V10
FROM dbo.tblNo1
WHERE (MP <= 30)
ORDER BY V1, V2, V3, V4, V5

These are the 6 rows in my table and so as you can see the first 3 rows
would be in one group and the next 3 rows would be in another group. What
I would like to do is have to sort of way to show a distinction or
highlight between the first 3 rows and the next 3 rows.

1,2,3,4,5, 8,9,11,13,16
1,2,3,4,5, 9,11,12,15,17
1,2,3,4,5, 11,15,19,21,23
1,2,3,4,6, 8,9,12,15,18
1,2,3,4,6, 9,12,16,18,21
1,2,3,4,6, 13,16,19,20,21

TIA
Roy

[Roy Gourgi]

Hi Ignacio,

It's just a normal view on a table, so I guess that makes it Win. I am not
using a control. I am using VS 2005 Express edition and all that I am doing
is making a connection to my database and then I am making a view based on
the rows in the table. The rows are already in the table so all I am doing
is creating a view and what I would like to be able to do in my view is to
see the rows grouped and delineated in some ways.

Is what I am asking it to do possible?

Thanks
Roy

"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us> wrote
in message news:eH**************@tk2msftngp13.phx.gbl...

Hi,

Humm, dont see the problem you are facing, it's not a SQL one, as your
query is fine.

It seems to be related to the UI, if so you need to provide more details:
1- Web or Win ?
2- What control r u using to display the data
3- How you store the daa in the APP

cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Roy Gourgi" <ro***@videotron.ca> wrote in message
news:PE*******************@weber.videotron.net...

Hi,

Is there a way to group rows by some columns in such a way that I can
clearly see them in a distinguished fashion when I look at a view or
table. This is my code below but when I try using GROUP BY it is not
giving me the desired effect that I would like?

For example, let's say I would like to group by the first 5 columns (i.e.
V1,V2,V3,V4,V5) and let's say the first 3 rows of my view or table
contain the values 1,2,3,4,5 for V1,V2,V3,V4,V5 respectively, and the
next 3 rows contain the values 1,2,3,4,6 for V1,V2,V3,V4,V5 respectively
and so on. What I would like to see when I look at my view or table, is
some sort of visible delineation or break of some sort by the grouping
that I chose, so that I do not have to do it visually myself because I
have many records in my view. See my view below with the 6 rows as an
example.

SELECT TOP (100) PERCENT V1, V2, V3, V4, V5, V6, V7, V8, V9, V10
FROM dbo.tblNo1
WHERE (MP <= 30)
ORDER BY V1, V2, V3, V4, V5

These are the 6 rows in my table and so as you can see the first 3 rows
would be in one group and the next 3 rows would be in another group. What
I would like to do is have to sort of way to show a distinction or
highlight between the first 3 rows and the next 3 rows.

1,2,3,4,5, 8,9,11,13,16
1,2,3,4,5, 9,11,12,15,17
1,2,3,4,5, 11,15,19,21,23
1,2,3,4,6, 8,9,12,15,18
1,2,3,4,6, 9,12,16,18,21
1,2,3,4,6, 13,16,19,20,21

TIA
Roy

[Nicholas Paldino [.NET/C# MVP]]

Roy,

There isn't an easy way to do this with the tools offered out of the box
in .NET.

First, when you select your data, you are going to get it in a table,
which is a two-dimensional structure that contains the row and column
information.

What would help is a master-detail view, with the master grid showing
the columns you have grouped on, and the child columns showing the rows that
are applicable for the currently selected record in the master grid.

When you get this result set back from the database, what you want to do
is create a master table in your dataset, with only the columns that are
applicable to the grouping. Once you do that, you populate the rows/columns
with the unique groups from the returned table.

Then, create a DataRelation in the data set which binds the master table
to the child table.

What you can then do is have two data grids, one bound to the master
data table, and another bound to the ^relation^. That way, you will see the
individual groups on the top, and the records pertaining to that group on
the bottom.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
"Roy Gourgi" <ro***@videotron.ca> wrote in message
news:PE*******************@weber.videotron.net...

Hi,

Is there a way to group rows by some columns in such a way that I can
clearly see them in a distinguished fashion when I look at a view or
table. This is my code below but when I try using GROUP BY it is not
giving me the desired effect that I would like?

For example, let's say I would like to group by the first 5 columns (i.e.
V1,V2,V3,V4,V5) and let's say the first 3 rows of my view or table contain
the values 1,2,3,4,5 for V1,V2,V3,V4,V5 respectively, and the next 3 rows
contain the values 1,2,3,4,6 for V1,V2,V3,V4,V5 respectively and so on.
What I would like to see when I look at my view or table, is some sort of
visible delineation or break of some sort by the grouping that I chose, so
that I do not have to do it visually myself because I have many records in
my view. See my view below with the 6 rows as an example.

SELECT TOP (100) PERCENT V1, V2, V3, V4, V5, V6, V7, V8, V9, V10
FROM dbo.tblNo1
WHERE (MP <= 30)
ORDER BY V1, V2, V3, V4, V5

These are the 6 rows in my table and so as you can see the first 3 rows
would be in one group and the next 3 rows would be in another group. What
I would like to do is have to sort of way to show a distinction or
highlight between the first 3 rows and the next 3 rows.

1,2,3,4,5, 8,9,11,13,16
1,2,3,4,5, 9,11,12,15,17
1,2,3,4,5, 11,15,19,21,23
1,2,3,4,6, 8,9,12,15,18
1,2,3,4,6, 9,12,16,18,21
1,2,3,4,6, 13,16,19,20,21

TIA
Roy

[Roy Gourgi]

Thanks Nicholas,

I have found a way of circumventing the problem without too many headaches.
What I did is to
execute the statement to search the number of instances in the table that
fullfill my criterai and I store the value into a variable and when I insert
a row I just add the variable + 1 to a one of the columns in my row so that
I always know how many rows there are in a group.

Nonetheless, I know that sooner or later I will have to do something akin to
what you are describing.

I am very impressed with VS 2005 Express (C# and SQL) and I am getting to
know my way around a little bit which makes it that much more enjoyable. I
have still got a long way to go, but my program is coming along fine.

I really think that Microsoft did a very smart thing with the .NET framework
as it was imperative that they break ties with the old API framework that
lent itself well in the past, but is really inadequate for today's
contemporary OOP model. Furthermore, the security will be enhanced
tremendously as well as compatibility.

Thanks
Roy
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:er**************@TK2MSFTNGP11.phx.gbl...

Roy,

There isn't an easy way to do this with the tools offered out of the
box in .NET.

First, when you select your data, you are going to get it in a table,
which is a two-dimensional structure that contains the row and column
information.

What would help is a master-detail view, with the master grid showing
the columns you have grouped on, and the child columns showing the rows
that are applicable for the currently selected record in the master grid.

When you get this result set back from the database, what you want to
do is create a master table in your dataset, with only the columns that
are applicable to the grouping. Once you do that, you populate the
rows/columns with the unique groups from the returned table.

Then, create a DataRelation in the data set which binds the master
table to the child table.

What you can then do is have two data grids, one bound to the master
data table, and another bound to the ^relation^. That way, you will see
the individual groups on the top, and the records pertaining to that group
on the bottom.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
"Roy Gourgi" <ro***@videotron.ca> wrote in message
news:PE*******************@weber.videotron.net...

Hi,

Is there a way to group rows by some columns in such a way that I can
clearly see them in a distinguished fashion when I look at a view or
table. This is my code below but when I try using GROUP BY it is not
giving me the desired effect that I would like?

For example, let's say I would like to group by the first 5 columns (i.e.
V1,V2,V3,V4,V5) and let's say the first 3 rows of my view or table
contain the values 1,2,3,4,5 for V1,V2,V3,V4,V5 respectively, and the
next 3 rows contain the values 1,2,3,4,6 for V1,V2,V3,V4,V5 respectively
and so on. What I would like to see when I look at my view or table, is
some sort of visible delineation or break of some sort by the grouping
that I chose, so that I do not have to do it visually myself because I
have many records in my view. See my view below with the 6 rows as an
example.

SELECT TOP (100) PERCENT V1, V2, V3, V4, V5, V6, V7, V8, V9, V10
FROM dbo.tblNo1
WHERE (MP <= 30)
ORDER BY V1, V2, V3, V4, V5

These are the 6 rows in my table and so as you can see the first 3 rows
would be in one group and the next 3 rows would be in another group. What
I would like to do is have to sort of way to show a distinction or
highlight between the first 3 rows and the next 3 rows.

1,2,3,4,5, 8,9,11,13,16
1,2,3,4,5, 9,11,12,15,17
1,2,3,4,5, 11,15,19,21,23
1,2,3,4,6, 8,9,12,15,18
1,2,3,4,6, 9,12,16,18,21
1,2,3,4,6, 13,16,19,20,21

TIA
Roy