473,703 Members | 2,541 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

GROUP BY

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
Dec 2 '05 #1
5 2220
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

Dec 2 '05 #2
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***@videotro n.ca> wrote in message
news:PE******** ***********@web er.videotron.ne t...
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

Dec 2 '05 #3
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.mach in AT dot.state.fl.us > wrote
in message news:eH******** ******@tk2msftn gp13.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***@videotro n.ca> wrote in message
news:PE******** ***********@web er.videotron.ne t...
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


Dec 2 '05 #4
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.co m
"Roy Gourgi" <ro***@videotro n.ca> wrote in message
news:PE******** ***********@web er.videotron.ne t...
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

Dec 2 '05 #5
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.c om> wrote in
message news:er******** ******@TK2MSFTN GP11.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.co m
"Roy Gourgi" <ro***@videotro n.ca> wrote in message
news:PE******** ***********@web er.videotron.ne t...
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


Dec 2 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
14166
by: Mike | last post by:
I am sure that I am making a simple boneheaded mistake and I would appreciate your help in spotting in. I have just installed apache_2.0.53-win32-x86-no_ssl.exe php-5.0.3-Win32.zip Smarty-2.6.7.tar.gz on a system running WindowsXP SP2. Apache and PHP tested out fine. After adding Smarty, I ran the following http://localhost/testphp.php
2
12689
by: Tom Loach | last post by:
Our system administrator set up an NT server group in order to allow our users to login to our application via https to our sql server. The group appears as a User in SQL Server when you look at it in Enterprise Manager. That said, I can not see the users associated with the group from Enterprise Manager, but know they can login to the database. The problem is this. When we login via the web we get access to the database without...
4
28821
by: Chad Richardson | last post by:
I've always been mistified why you can't use a column alias in the group by clause (i.e. you have to re-iterate the entire expression in the group by clause after having already done it once in the select statement). I'm mostly a SQL hobbiest, so it's possible that I am not doing this in the most efficient manner. Anyone care to comment on this with relation to the following example (is there a way to acheive this without re-stating the...
2
3349
by: BillD | last post by:
I'm trying to derive a schema from a base schema. I want to redefine a "group" from the base schema in my derived schema in order to add more options to the "choice" aggregate (see schema1.xsd sample). schema1.xsd sample: <!-- Here is a clipped down version of the group I want to redefine. --> <xsd:group name="INSURANCESVCRQMSGS"> <xsd:choice> <xsd:element ref="HomePolicyAddRq" minOccurs="1" maxOccurs="1"/>
16
4447
by: michael | last post by:
Is it possible to get all href URLs contained in a unordered list and place them in an array? Or in fact two different arrays, differently named one for each <ul> group? <ul> <li><a href="lemurs.html">Lemurs</a></li> <li><a href="chameleons.html">Chameleons</a></li> </ul> <ul>
7
4537
by: Darin | last post by:
I have a report that sub-totals on a group, then grand-totals at the report footer. If there's only one group, the sub-total and grand total are redundant, so I only want to show one of them. I know how to count the groups, then hide the report footer if there's only one group, but my problem is I want to hide the group footer (sub-total), not the report footer (because the report footer references what the grand total is for, which is...
1
7318
by: David Horowitz | last post by:
Hi folks. I need to create a report that has a Group Header that pulls certain data from the Detail section. It's something like this: +--Report---------------------------------------- | +--Group Header---------------------------------- | | Codes: XYZ, XXX, YYY
7
5599
by: Sameh Ahmed | last post by:
Hello there IsInrole gives ya the means to check if the current or impersonated user belongs to a specific windows role or group. is there a way to do the same without using ADSI to check if "domain\user" belongs to "domain\group"? the reason is, when getting the "memberof" property of a user, then checking if it contains the desired group or not. this will only work if the user is a member of the group itself but not when he is a member...
2
5153
by: jon|k | last post by:
hi all-- i need to do a transformation that removes duplicates (among other things). to accomplish that, i'm trying to use for-each-group, but it doesn't work. i need to select for duplicates by looking at the child node sequence (see sample below). note that when i do an xsl-message on the group-by expression inside the for-each-group, it has exactly what i'd like to group by listed, but i guess it doesn't like to have a sequence of...
3
4108
by: Sebastian | last post by:
Hello all I have a report where I have two nested groups. I know there are only three standard options for running sum: None, Over Group and Over All. I have a MyTextBox in detail section where the data is summed over group. But the data is summed over the second group. When a new group begins MyTextBox value is resetting to zero. I need a running sum over the first group so when another second group begins MyTextBox value will
0
8740
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8657
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9236
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9094
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
7839
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5918
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4415
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3108
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2047
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.