group number

i have the following data in the database:
hk a aa aaa
hk b bb bbb
hk c cc ccc
uk d dd ddd
uk e ee eee
us f ff fff
and they are displayed in a matrix like below
hk a aa aaa
b bb bbb
c cc ccc
uk d dd ddd
e ee eee
us f ff fff
i would like to add a number to any new row like below
1 hk a aa aaa
b bb bbb
c cc ccc
2 uk.....
3Us...
how can i do this...i have tried rownumber and runningvalue but it doesnot
work
please help~~
thank you so much in advanceTake a look at
http://solidqualitylearning.com/blogs/dejan/archive/2004/10/21/199.aspx.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
news:32D440E6-EC85-4C1C-A4EA-913BD543C7F6@.microsoft.com...
> i have the following data in the database:
> hk a aa aaa
> hk b bb bbb
> hk c cc ccc
> uk d dd ddd
> uk e ee eee
> us f ff fff
> and they are displayed in a matrix like below
> hk a aa aaa
> b bb bbb
> c cc ccc
> uk d dd ddd
> e ee eee
> us f ff fff
> i would like to add a number to any new row like below
> 1 hk a aa aaa
> b bb bbb
> c cc ccc
> 2 uk.....
> 3Us...
> how can i do this...i have tried rownumber and runningvalue but it doesnot
> work
> please help~~
> thank you so much in advance

Group Heading text different on repeated headers

Is there any way to have the data displayed in a group header different on the first occurrence of the header from subsequent occurrences within that same group?

I have the Repeat Header option checked, so the header appears on every page. I want the pages where it is a continuation (has been repeated because the option is on) to look different, e.g., the group's name cell should be "<name> (cont.)", where on the first page it appears it should be just "<name>". I can use an expression in a cell in the header, but I don't see any convenient function or other way to detect whether this is the first, or a subsequent, instance of the header.

I thought of using the RowNumber function, scoped to the header grouping, but this seems to return the row number of the bottom-most (last) detail row of the group - that is, the number of rows in the group - so that doesn't seem to help.

Have you found any solution to the issue in your post? I would like to do the same on a grouped table report and cannot seem to find the key.

Group Heading text different on repeated headers

Is there any way to have the data displayed in a group header different on the first occurrence of the header from subsequent occurrences within that same group?

I have the Repeat Header option checked, so the header appears on every page. I want the pages where it is a continuation (has been repeated because the option is on) to look different, e.g., the group's name cell should be "<name> (cont.)", where on the first page it appears it should be just "<name>". I can use an expression in a cell in the header, but I don't see any convenient function or other way to detect whether this is the first, or a subsequent, instance of the header.

I thought of using the RowNumber function, scoped to the header grouping, but this seems to return the row number of the bottom-most (last) detail row of the group - that is, the number of rows in the group - so that doesn't seem to help.

Have you found any solution to the issue in your post? I would like to do the same on a grouped table report and cannot seem to find the key.

Group By Week

Hi,
I have a matrix with month and week as the column grouping. Somehow
the week gets displayed twice when half of the week falls at the end
of the month and the other half in the beginning of the following
month. How do i set it so that the week only displays once?
TIA.
JMset the hide duplicates property on the cell property
mike
"Jordan" wrote:
> Hi,
> I have a matrix with month and week as the column grouping. Somehow
> the week gets displayed twice when half of the week falls at the end
> of the month and the other half in the beginning of the following
> month. How do i set it so that the week only displays once?
> TIA.
> JM
>|||Hi Mike,
I've tried that but the it only hides the header. In the end i still
have 2 columns displayed but one without the week number.
Thanks,
JM
"mike" <mike@.discussions.microsoft.com> wrote in message news:<1D104872-83DE-4D65-AD38-33A3EF0B8511@.microsoft.com>...
> set the hide duplicates property on the cell property
> mike
> "Jordan" wrote:
> > Hi,
> >
> > I have a matrix with month and week as the column grouping. Somehow
> > the week gets displayed twice when half of the week falls at the end
> > of the month and the other half in the beginning of the following
> > month. How do i set it so that the week only displays once?
> >
> > TIA.
> >
> > JM
> >|||Hi,
Here's my situation. After configuring the report to group by month
and by week, here's the output.
| January | Febuary
---
| WK 1 | WK 2 | WK 3 | WK 4 | WK 4 | WK 5 |
As illustrated here, there's a repeat of WK 4 because part of week 4
continues in febuary. Is there any way i can rectify this and only
display WK 4 under january or whichever month that has the most days
of that particular week?
Thanks.
Regards,
JM
jordanm@.37.com (Jordan) wrote in message news:<9ae5dece.0411181827.18b1289c@.posting.google.com>...
> Hi Mike,
> I've tried that but the it only hides the header. In the end i still
> have 2 columns displayed but one without the week number.
> Thanks,
> JM
>
> "mike" <mike@.discussions.microsoft.com> wrote in message news:<1D104872-83DE-4D65-AD38-33A3EF0B8511@.microsoft.com>...
> > set the hide duplicates property on the cell property
> >
> > mike
> >
> > "Jordan" wrote:
> >
> > > Hi,
> > >
> > > I have a matrix with month and week as the column grouping. Somehow
> > > the week gets displayed twice when half of the week falls at the end
> > > of the month and the other half in the beginning of the following
> > > month. How do i set it so that the week only displays once?
> > >
> > > TIA.
> > >
> > > JM
> > >|||The answer to issue is posted here:
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&p=1&tid=9d321a2a-cc24-4999-be05-1bca5950dee1&mid=9d321a2a-cc24-4999-be05-1bca5950dee1
hth, cheers,
Cos
"Jordan" wrote:
> Hi,
> Here's my situation. After configuring the report to group by month
> and by week, here's the output.
> | January | Febuary
> ---
> | WK 1 | WK 2 | WK 3 | WK 4 | WK 4 | WK 5 |
> As illustrated here, there's a repeat of WK 4 because part of week 4
> continues in febuary. Is there any way i can rectify this and only
> display WK 4 under january or whichever month that has the most days
> of that particular week?
> Thanks.
> Regards,
> JM
> jordanm@.37.com (Jordan) wrote in message news:<9ae5dece.0411181827.18b1289c@.posting.google.com>...
> > Hi Mike,
> >
> > I've tried that but the it only hides the header. In the end i still
> > have 2 columns displayed but one without the week number.
> >
> > Thanks,
> > JM
> >
> >
> > "mike" <mike@.discussions.microsoft.com> wrote in message news:<1D104872-83DE-4D65-AD38-33A3EF0B8511@.microsoft.com>...
> > > set the hide duplicates property on the cell property
> > >
> > > mike
> > >
> > > "Jordan" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a matrix with month and week as the column grouping. Somehow
> > > > the week gets displayed twice when half of the week falls at the end
> > > > of the month and the other half in the beginning of the following
> > > > month. How do i set it so that the week only displays once?
> > > >
> > > > TIA.
> > > >
> > > > JM
> > > >
>

Group By Count * >1?

Can this be used to prevent the repetition of records displayed in a page?

Code Snippet

SELECT T_ProgramGuests, GuestName
FROM T_ProgramGuests
GROUP BY ProgramID, GuestName
HAVING (COUNT(*) > 1)

I'm trying to prevent names being repeated. I only want the name to show once followed by the next name and so on. But only once.

Does this do what you want?

SELECT GuestName, MAX(ProgramID) AS pid

FROM T_ProgramGuests

GROUP BY GuestName

ORDER BY GuestName ASC

The above prints the last programId/guest name pair in your table. If you want the first, you can replace the max with min.

Hope this helps!

John (MSFT)

Grid results in Management Studio

Hi
When I run a query in Management Studio, I get this message -
The query has exceeded the maximum number of result sets that can be
displayed in the results grid. Only the first 100 result sets are
displayed in the grid.
Is there any way to change this limit so I can get all results?
Regards
SteenWhich version are you using ? On SQL 2005 Ent. RTM version I don't have
this problem. I can select 1000000 rows and all are displayed in the
grid.
Markus|||Rows is not the same as resultsets. Steen has the problem that the query returns > 100 result sets.
AFAIK, this is not a configurable limit.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1132222959.738766.69660@.g47g2000cwa.googlegroups.com...
> Which version are you using ? On SQL 2005 Ent. RTM version I don't have
> this problem. I can select 1000000 rows and all are displayed in the
> grid.
> Markus
>|||Tibor Karaszi wrote:
> Rows is not the same as resultsets. Steen has the problem that the query returns > 100 result sets.
> AFAIK, this is not a configurable limit.
>
Thanks Tibor. It's not a major problem - it's just annoying when it
happens..;-). I've also looked around to see if I could find somewhere
where it could be configured but with no luck.
Regards
Steen