Grouping two similar column names but different data?

Hi All,
I have a need to group a column with he same name.
I have a column called "AccountType" which has data such as :
A1
A2
A3
A4
I am using an aggrate for this column:
SELECT
SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
FROM Tbl1
GROUP BY AccountType
I want to also group by the actual group type. Something like:
SELECT
AccountType,
SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
FROM Tbl1
GROUP BY AccountType, AccountType
Can someone please give me a little help with this?
Thanks very much,
John.John,
Can you post an example of the expected result?
AMB
"John" wrote:

> Hi All,
> I have a need to group a column with he same name.
> I have a column called "AccountType" which has data such as :
> A1
> A2
> A3
> A4
> I am using an aggrate for this column:
> SELECT
> SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
> FROM Tbl1
> GROUP BY AccountType
> I want to also group by the actual group type. Something like:
> SELECT
> AccountType,
> SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
> FROM Tbl1
> GROUP BY AccountType, AccountType
> Can someone please give me a little help with this?
> Thanks very much,
> John.
>
>|||John:
without knowing exactly what you want, its difficult to answer.
Is this what you want:
select t.[account type], count(*)
from (
SELECT
SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
FROM Tbl1
GROUP BY AccountType
) t
group by t.[Account Type]
If not then try posting some sample data set and the required output and i
am sure someone will be able to help you on that.
just incase if you wanna play around and understand what the above code is
doing then use northwind and execute this query
use northwind
go
select t.lessOrMore, count(*) , sum(t.OrderCount)
from (
select orderID, count(*) as OrderCount
, case when orderID < '11000' then 'less' else 'more' end as "LessOrMore"
from [Order Details]
group by OrderID ) t
group by t.LessOrMore
Hope the above helps
Abhishek
"John" wrote:

> Hi All,
> I have a need to group a column with he same name.
> I have a column called "AccountType" which has data such as :
> A1
> A2
> A3
> A4
> I am using an aggrate for this column:
> SELECT
> SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
> FROM Tbl1
> GROUP BY AccountType
> I want to also group by the actual group type. Something like:
> SELECT
> AccountType,
> SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
> FROM Tbl1
> GROUP BY AccountType, AccountType
> Can someone please give me a little help with this?
> Thanks very much,
> John.
>
>|||My current data result is something like this:
LastName | Account Type | NumCount
Miller | Good | 20
Miller | Not Good | 5
Jones | Not Good | 37
Miller | Not Good | 9
What I would like to see is the following:
LastName | Account Type Actual Type | NumCount
Miller | Good | A1 |
20
Miller | Not Good | A2 | 5
Jones | Not Good | A3 | 37
Miller | Not Good | A4 |
9
In the first example I am grouping by LastName, [Account Type]
In the second example I need to Group by the same and addition to the Actual
Account Type.
The problem here though is that the column "AccountType" needs to be used
twice and I don't know how to handle this. Unfortunately I can not use a
unique alias for each one that can be Grouped.
John.
"Abhishek Pandey" <AbhishekPandey@.discussions.microsoft.com> wrote in
message news:FFCAE864-F9B5-426E-B0FA-8CE9B95B489D@.microsoft.com...
> John:
> without knowing exactly what you want, its difficult to answer.
> Is this what you want:
> select t.[account type], count(*)
> from (
> SELECT
> SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account
> Type]
> FROM Tbl1
> GROUP BY AccountType
> ) t
> group by t.[Account Type]
>
> If not then try posting some sample data set and the required output and i
> am sure someone will be able to help you on that.
> just incase if you wanna play around and understand what the above code is
> doing then use northwind and execute this query
> use northwind
> go
> select t.lessOrMore, count(*) , sum(t.OrderCount)
> from (
> select orderID, count(*) as OrderCount
> , case when orderID < '11000' then 'less' else 'more' end as "LessOrMore"
> from [Order Details]
> group by OrderID ) t
> group by t.LessOrMore
>
> Hope the above helps
> Abhishek
> "John" wrote:
>|||John:
It seems you dont need a second groupby.. coz you are not doing another
group by. It seems you just need and extra column. This is what is reflected
in the result set you posted (NumCount remains the same and you just need an
extra column for actual account type)
But then again you will need to be more clear in what exacly you want
is this what you want:
LastName | Account Type | Actual Type | NumCount
Miller | Good | A1 | 20
Miller | Not Good | A2 | 3
Miller | Not Good | A3 | 2
Jones | Not Good | A3 | 30
Jones | Not Good | A4 | 7
Miller | Not Good | A4 | 9
Notice that for miller not good account i have further divided into 2 actual
account type and the sum of count 3+2 = 5.
similarly for Jones it is 30+7 = 37.
If above is what you want then you can simply code it like this
SELECT Lastname
, (CASE
WHEN AccountType = 'A1'
Then 'Good'
ELSE 'Not Good'
END) AS [Account Type]
, [Account type] AS [Actual type]
, count(*) as [NumCount]
FROM Tbl1
GROUP BY LastName, AccountType
Hope the above helps. Do let me know if this is what you were looking for.
Abhishek
"John" wrote:

> My current data result is something like this:
> LastName | Account Type | NumCount
> Miller | Good | 20
> Miller | Not Good | 5
> Jones | Not Good | 37
> Miller | Not Good | 9
> What I would like to see is the following:
> LastName | Account Type Actual Type | NumCount
> Miller | Good | A1 |
> 20
> Miller | Not Good | A2 |
5
> Jones | Not Good | A3 |
37
> Miller | Not Good | A4 |
> 9
> In the first example I am grouping by LastName, [Account Type]
> In the second example I need to Group by the same and addition to the Actu
al
> Account Type.
> The problem here though is that the column "AccountType" needs to be used
> twice and I don't know how to handle this. Unfortunately I can not use a
> unique alias for each one that can be Grouped.
> John.
> "Abhishek Pandey" <AbhishekPandey@.discussions.microsoft.com> wrote in
> message news:FFCAE864-F9B5-426E-B0FA-8CE9B95B489D@.microsoft.com...
>
>

grouping similar data

In layout view: how do I group all the company names together who have the same value for a certain field? For instance, 10 companies all have the same booth size, but in Preview mode, it lists each company seprately with the booth size on each line. I need to have the booth size listed once with all the companies who share that common size. I have tried adding a group and using the expression for the company name as well as the booth size and it does not work. Can someone provide some detailed instructions?
Thank you,
Bil
From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comIf I am understanding correctly you want to have the booth size and then
show all companies under that that match that description. Have you tried
grouping it by the booth size and then put your companies in the detail?
"Bkana" <nospam@.developmentnow.com> wrote in message
news:97f68ac4-a6e2-429d-a4e5-3b98fed63020@.developmentnow.com...
> In layout view: how do I group all the company names together who have the
> same value for a certain field? For instance, 10 companies all have the
> same booth size, but in Preview mode, it lists each company seprately with
> the booth size on each line. I need to have the booth size listed once
> with all the companies who share that common size. I have tried adding a
> group and using the expression for the company name as well as the booth
> size and it does not work. Can someone provide some detailed instructions?
> Thank you,
> Bill
> From
> http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com

Grouping numbers

I have a table which lists player names, teams played for and the
years they played there and my code looks like this

SELECT AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
AlsoPlayedFor.TeamName, Min([AlsoPlayedFor].[Year]) & "-" &
Max([AlsoPlayedFor].[Year]) AS [Year]
FROM AlsoPlayedFor
GROUP BY AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
AlsoPlayedFor.TeamName;

which takes the Min year and the Max Year and displays it like "Year-
Year"

But lets say for example the player played for 5 years so it 1990,
1991, 1992, 1993, 1995

It would display as 1990-1995 but I want it to display as 1990-1993,
1995, is this possiable? Also I need it to gothe other wayso if the
years are 1990, 1992, 1993, 1994, 1995 I want that to display as 1990,
1992-1995.

PLEASE HELPChris (chrislabs12@.gmail.com) writes:

Quote:

Originally Posted by

I have a table which lists player names, teams played for and the
years they played there and my code looks like this
>
SELECT AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
AlsoPlayedFor.TeamName, Min([AlsoPlayedFor].[Year]) & "-" &
Max([AlsoPlayedFor].[Year]) AS [Year]
FROM AlsoPlayedFor
GROUP BY AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
AlsoPlayedFor.TeamName;
>
which takes the Min year and the Max Year and displays it like "Year-
Year"
>
But lets say for example the player played for 5 years so it 1990,
1991, 1992, 1993, 1995
>
It would display as 1990-1995 but I want it to display as 1990-1993,
1995, is this possiable? Also I need it to gothe other wayso if the
years are 1990, 1992, 1993, 1994, 1995 I want that to display as 1990,
1992-1995.

I could suggest a query which in SQL 2005 at least give you a comma-
separated list of the years. Collapsing adjacent years into ranges appears
to make things a lot more complicated.

However, the query you posted has syntax which is not legal in SQL Server,
but has a touch of Access, a product of which I have no experience.

Could you clarify which product and which version of that product you
are using? If you are using Access, I recommend that you try an Access
newsgroup instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

What you posted implies a serious design error about history tables.
What you are askimg for is a violaiton of 1NF and the principle that
display is done in the front end and never the backend in a tiered
architecture. And finally the syntax you posted is not valid.

Want to try again?|||Erland Sommarskog wrote:

Quote:

Originally Posted by

>
>
I could suggest a query which in SQL 2005 at least give you a comma-
separated list of the years. Collapsing adjacent years into ranges appears
to make things a lot more complicated.

Here is a recursive solution that will do the job when
MAXRECURSION is no greater than the number of separate
years for one individual player. Some of the complication
is to get around limitations in what a recursive query
can contain (no GROUP BY, for example). The idea is
slippery, but not quite as messy as it looks.

CREATE TABLE T (
Pid INT,
yr INT,
primary key (Pid,yr)
)
go

INSERT T (Pid,yr) VALUES(1,1)
INSERT T (Pid,yr) VALUES(1,4)
INSERT T (Pid,yr) VALUES(1,3)
INSERT T (Pid,yr) VALUES(1,5)
INSERT T (Pid,yr) VALUES(1,6)
INSERT T (Pid,yr) VALUES(1,9)
INSERT T (Pid,yr) VALUES(1,10)
INSERT T (Pid,yr) VALUES(2,29)
INSERT T (Pid,yr) VALUES(2,30)
INSERT T (Pid,yr) VALUES(2,31)
INSERT T (Pid,yr) VALUES(2,9)
INSERT T (Pid,yr) VALUES(2,130)
INSERT T (Pid,yr) VALUES(2,131)
INSERT T (Pid,yr) VALUES(2,132)
go

with Mins(iter,Pid,lastwrite,lastfound,rowYr,yrs) as (
select
0,
Pid,
min(yr),
min(yr),
min(yr),
cast(min(yr) as varchar(max))
from T
group by Pid
union all
select
Mins.iter+1,
Mins.Pid,
case when min(T.yr) over (partition by Mins.Pid) = Mins.lastfound + 1
--and Mins.rightest < Mins.upto
then Mins.lastwrite else min(T.yr) over (partition by Mins.Pid) end,
min(T.yr) over (partition by Mins.Pid),
T.yr,
Mins.yrs
+ case when min(T.yr) over (partition by Mins.Pid) Mins.lastfound + 1
then case when Mins.lastfound Mins.lastwrite
then rtrim(Mins.lastfound) else '' end
+ ',' + rtrim(min(T.yr) over (partition by Mins.Pid))
else case when Mins.lastfound = Mins.lastwrite
then '-' else '' end
end
from Mins join T
on Mins.Pid = T.Pid
and Mins.lastfound < T.yr
and Mins.rowYr = Mins.lastfound
), AllSteps(Pid,yrs,lastwrite,lastfound,rk) as (
select distinct Pid, yrs,lastwrite,lastfound,
rank() over (partition by Pid order by iter desc)
from Mins
)
select
Pid,lastwrite,
yrs + case when lastwrite < lastfound then rtrim(lastfound) else ','+rtrim(lastfound) end
from AllSteps
where rk = 1

go

-- Steve Kass
-- Drew University
-- http://www.stevekass.com
-- 95508D54-0B01-431B-8B58-880146787216|||Correction: The final SELECT should be

select
Pid,lastwrite,
yrs + case when lastwrite < lastfound then rtrim(lastfound) else '' end
from AllSteps
where rk = 1

The version I posted lists the last year twice, if it is not
part of a preceding range of years.

SK

Steve Kass wrote:

Quote:

Originally Posted by

Erland Sommarskog wrote:
>

Quote:

Originally Posted by

>
>
I could suggest a query which in SQL 2005 at least give you a comma-
separated list of the years. Collapsing adjacent years into ranges

appears

Quote:

Originally Posted by

to make things a lot more complicated.

>
Here is a recursive solution that will do the job when
MAXRECURSION is no greater than the number of separate
years for one individual player. Some of the complication
is to get around limitations in what a recursive query
can contain (no GROUP BY, for example). The idea is
slippery, but not quite as messy as it looks.
>

<snip>

Quote:

Originally Posted by

select
Pid,lastwrite,
yrs + case when lastwrite < lastfound then rtrim(lastfound) else
','+rtrim(lastfound) end
from AllSteps
where rk = 1
>
go
>
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
-- 95508D54-0B01-431B-8B58-880146787216
>
>

|||Here is a guess at what you should have used for DDL if you had fgiven
us specs.

CREATE TABLE PlayerHistory
(player_id INTEGER NOT NULL
REFERENCES Pleyers(player_id)
team_name CHAR(15) NOT NULL
REFERENCES Teams(team_name),
start_year INTEGER NOT NULL
CHECK(start_year BETWEEN 1950 AND 9999),
end_year INTEGER
CHECK(start_year BETWEEN 1950 AND 9999),
CHECK(start_year <= end_year),
PRIMARY KEY ((player_id , team_name ,start_year)
);

A null end_year means the player is still with that team. You use a
VIEW with WHERE end_year IS NULL to get the current situation; you do
not put it in a separate table. What you seem to have is a table in
which an attribute (temproal duration) is split over several rows.

See how simple basic RDBMS design can save you from complex kludges?
Here is a guess at what you should have used for DDL if you had fgiven
us specs.

CREATE TABLE PlayerHistory
(player_id INTEGER NOT NULL
REFERENCES Pleyers(player_id)
team_name CHAR(15) NOT NULL
REFERENCES Teams(team_name),
start_year INTEGER NOT NULL
CHECK(start_year BETWEEN 1950 AND 9999),
end_year INTEGER
CHECK(start_year BETWEEN 1950 AND 9999),
CHECK(start_year <= end_year),
PRIMARY KEY ((player_id , team_name ,start_year)
);

A null end_year means the player is still with that team. You use a
VIEW with WHERE end_year IS NULL to get the current situation; you do
not put it in a separate table. What you seem to have is a table in
which an attribute (temproal duration) is split over several rows.

See how simple basic RDBMS design can save you from complex kludges?

Grouping in List Control

Hi,

I am new with RS2000. I am working on a Reporting application. I have some Application names on X-axis of a bar chart say they are 90 in number. I want to group this chart in a list control in such a way that in each portion the chart must contain 15 application names if suppose there are 90 application names.

Add a (detail) group to the list with the following grouping expression:

=Int((RowNumber(Nothing)-1)/15)

This should result in the desired grouping of 15 items per (repeating) list instance. Then just put the chart inside the list.

-- Robert

|||But i need to add a list fir that.are there any other work arounds?

Grouping in List Control

Hi,

I am new with RS2000. I am working on a Reporting application. I have some Application names on X-axis of a bar chart say they are 90 in number. I want to group this chart in a list control in such a way that in each portion the chart must contain 15 application names if suppose there are 90 application names.

Add a (detail) group to the list with the following grouping expression:

=Int((RowNumber(Nothing)-1)/15)

This should result in the desired grouping of 15 items per (repeating) list instance. Then just put the chart inside the list.

-- Robert

|||But i need to add a list fir that.are there any other work arounds?sql

Grouping & Concatenation?

Hello,

I have a report with a table containing 2 groups (Role and Person, where person is a subgroup of role). I am trying to concatenate the names of a series of persons who are grouped by their role within an organisation so that a report that would usually appear as:
...
Board of Directors
Director1
DIrector2
Director3
Advisory Board
Adv1
Adv2...
...
Will appear as:
...
Board of Directors
Director1, Director2, Director3
Advisory Board
Adv1, Adv2...
...
This can easily be done in crystal reports by concatenating the person names in the header section of group 2 (persons) and printing the result in the footer section of group 1 (role). However, in SSRS it appears that a group's footer is output before the contents (details or subgroup) of the group and this throws out the whole concatenation process so that I end up with:
...
Board of Directors
Advisory Board
Director1, Director2, Director3
...
How can I get around this?
Thank you,
Stephen.

You may want to read this blog article: http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

It describes the custom aggregate approach. In your particular example, the custom code function would just concatenate strings (instead of adding certain values as shown in the blog article).

-- Robert

|||

Thanks for the response. I will give that a try and let you know how I get on.

Thanks again,

Stephen.

|||

I've taken a look at the suggested solution and, correct me if I'm wrong but it appears to be doing much the same as what I am already doing. In the header of the inner group, I am concatenating the list of persons:
...
Shared _memberList As String

Shared Function AddMember(ByVal member As String) As String
If (_memberList Is Nothing) Then _memberList = String.Empty
If (_memberList.IndexOf(member) = -1) Then _memberList &= member
Return String.Empty
End Function
...
Then in the footer of the outer group, I am printing the concatenated list:
...
Shared Function GetMembers() As String
Dim tempList As String = _memberList
_memberList = String.Empty
Return tempList
End Function
...
As I say, this is causing problems because it appears that the outer group's footer is processed before the inner group's header (which just doesn't seem logical to me), so the list of persons are allways printed either in the following group or following record.

|||

OK. I've finally found a solution. Instead of using a table to achieve this, I place a list object inside a table header and manually configured the groups within the list object. This way, I was able to ensure the order of precedence was header->inner group->footer.

Regards,

Stephen.

|||

Can you please be more specific? Did you create a separate dataset for the list and added groupings in that? Did you hide the list display in the header and took the concatenated result from it and displayed in the body of the table?

Thank you,

Vinita

Grouping & Concatenation?

Hello,

I have a report with a table containing 2 groups (Role and Person, where person is a subgroup of role). I am trying to concatenate the names of a series of persons who are grouped by their role within an organisation so that a report that would usually appear as:
...
Board of Directors
Director1
DIrector2
Director3
Advisory Board
Adv1
Adv2...
...
Will appear as:
...
Board of Directors
Director1, Director2, Director3
Advisory Board
Adv1, Adv2...
...
This can easily be done in crystal reports by concatenating the person names in the header section of group 2 (persons) and printing the result in the footer section of group 1 (role). However, in SSRS it appears that a group's footer is output before the contents (details or subgroup) of the group and this throws out the whole concatenation process so that I end up with:
...
Board of Directors
Advisory Board
Director1, Director2, Director3
...
How can I get around this?
Thank you,
Stephen.

You may want to read this blog article: http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

It describes the custom aggregate approach. In your particular example, the custom code function would just concatenate strings (instead of adding certain values as shown in the blog article).

-- Robert

|||

Thanks for the response. I will give that a try and let you know how I get on.

Thanks again,

Stephen.

|||

I've taken a look at the suggested solution and, correct me if I'm wrong but it appears to be doing much the same as what I am already doing. In the header of the inner group, I am concatenating the list of persons:
...
Shared _memberList As String

Shared Function AddMember(ByVal member As String) As String
If (_memberList Is Nothing) Then _memberList = String.Empty
If (_memberList.IndexOf(member) = -1) Then _memberList &= member
Return String.Empty
End Function
...
Then in the footer of the outer group, I am printing the concatenated list:
...
Shared Function GetMembers() As String
Dim tempList As String = _memberList
_memberList = String.Empty
Return tempList
End Function
...
As I say, this is causing problems because it appears that the outer group's footer is processed before the inner group's header (which just doesn't seem logical to me), so the list of persons are allways printed either in the following group or following record.

|||

OK. I've finally found a solution. Instead of using a table to achieve this, I place a list object inside a table header and manually configured the groups within the list object. This way, I was able to ensure the order of precedence was header->inner group->footer.

Regards,

Stephen.

|||

Can you please be more specific? Did you create a separate dataset for the list and added groupings in that? Did you hide the list display in the header and took the concatenated result from it and displayed in the body of the table?

Thank you,

Vinita

Grouped by Using aliased names

Hi,

I have a select statement that gives me an output as follows:

Date Store Num Location
4-5-2007 0001 NY
4-5-2007 0002 NY
4-5-2007 0002 NY
4-4-2007 0003 PA
4-4-2007 0002 PA

The store num and location columns are derived like so:
LEFT(Table.WholeField, 4) AS 'Store Num',
RIGHT(LEFT(Table.WholeField, 6), 2) AS 'Location'

The problem I am running into is that I have been tasked to write a select statement that sums up distinct values for Store Num and Location. The output should look something like this :

Date Store Num Location Num
4-5-2007 0002 NY 2

However, 'Store Num' and 'Location' comes from one field by design. I have written a select statement that uses the GROUP BY function to get the correct output. However, I am receiving an invalid column name error because I am using an aliased name.

Does anyone have any insight into the error or a possible workaround.

Thanks,
V.Don't use the alias in the Group By, use the expression instead.

GROUP BY LEFT(Table.WholeField, 4), RIGHT(LEFT(Table.WholeField, 6), 2)

or just bury the original query as a subquery, and then sum and group by.

The first version seems "cleaner" to me though|||Thanks for the quick response. The Grouping by for the expressions works.

However, I am still getting:

Date Store Num Location Num
4-5-2007 0002 NY 1
4-5-2007 0002 NY 1

However, I am looking for :

Date Store Num Location Num
4-5-2007 0002 NY 2

Shouldn't the group by statement work with a correct count(*) or do I have to issue counts for the two separate columns?|||Can you post the SQL statement?|||Actually, I figured it out. Thanks for your help! Not thinking straight for some reason today!|||I don't see why this wouldn't work

SELECT [DATE],
LEFT(#TMP.WholeField, 4) AS 'Store Num',
RIGHT(LEFT(#TMP.WholeField, 6), 2) AS 'Location',
COUNT(*)
FROM #TMP
GROUP BY [DATE],
LEFT(#TMP.WholeField, 4),
RIGHT(LEFT(#TMP.WholeField, 6), 2)

That yields

Date Store Num Location Num
4-4-2007 0002 PA 1
4-4-2007 0003 PA 1
4-5-2007 0001 NY 1
4-5-2007 0002 NY 2|||I was using a convert function to take the timestamp field Date and convert it to a MM-DD-YYYY format. However, in the group by statement i was using just the Fieldname Date. When I added the convert function to the group by, it worked.

Group or Not group

Select FN,LN
Group by FN from A
Gives me
Bob
Bob
Tom
Tom
Tom
Bill
Bill
Need to achive output of names not after one another if they are same
name
Bob
Tom
Bill
Bob
Tom
Bob
Bill
Order is not important as long as they dont repaeat same name for next
rowOn 21 Dec 2005 13:50:36 -0800, Matt wrote:
>Select FN,LN
>Group by FN from A
>Gives me
>Bob
>Bob
>Tom
>Tom
>Tom
>Bill
>Bill
Hi Matt,
You must have made a mistake in this post. That query can never result
in anything but an error message.
>Need to achive output of names not after one another if they are same
>name
>Bob
>Tom
>Bill
>Bob
>Tom
>Bob
>Bill
>Order is not important as long as they dont repaeat same name for next
>row
Not sure I understand the requirements, but it sounds like a task for
the front end.
If you really want to do this server-side, then please proviude better
specs. Check out www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Since you specify no ORDER BY, SQL Server may return data in any sequence.
To return data in a pseudo random order, you can include ORDER BY NEWID().
> Need to achive output of names not after one another if they are same
> name
> Order is not important as long as they dont repaeat same name for next
> row
Is random order acceptable? In order words, is it ok if 2 or more same
names are consecutive, if only by coincidence?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt" <metehanIT@.Hotmail.com> wrote in message
news:1135201836.056730.13200@.o13g2000cwo.googlegroups.com...
> Select FN,LN
> Group by FN from A
> Gives me
> Bob
> Bob
> Tom
> Tom
> Tom
> Bill
> Bill
> Need to achive output of names not after one another if they are same
> name
> Bob
> Tom
> Bill
> Bob
> Tom
> Bob
> Bill
> Order is not important as long as they dont repaeat same name for next
> row
>|||Pushing those recordsets to another system, system does not allow me to
push smilar account one another.
So i cant push right after another. I need to push different name.
Since there are no other Indicator to differenciate the records
Above query was an example
any idea?|||As Hugo requested, we really need DDL, a working query and sample to data to
help you out. Presuming that FN is the differentiator, what should be done
when it is impossible to order results to prevent consecutive duplicates?
Consider the following:
Tom
Bob
Tom
Tom
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt" <metehanIT@.Hotmail.com> wrote in message
news:1135260352.114345.114900@.g47g2000cwa.googlegroups.com...
> Pushing those recordsets to another system, system does not allow me to
> push smilar account one another.
> So i cant push right after another. I need to push different name.
> Since there are no other Indicator to differenciate the records
> Above query was an example
> any idea?
>|||SQL is almost 300 line thats why could not posted there
This is one something like
SELECT ACCOUNT
FROM TEMP
WHERE CLS_DATE > '12/30/03'
GROUP BY (ACCOUNT)
UNION
SELECT ACCOUNT
FROM TEMP
WHERE CLS_DATE > '12/30/02'
GROUP BY (ACCOUNT)
Gives me result set of
7321234
7321234
7324567
7324567
I need result set to be
7321234
7324567
so on ,, as long as they dont repeat one after another.
Cheers|||> SQL is almost 300 line thats why could not posted there
300 lines is reasonable. Please post.
I still don't understand your problem. The query you posted will eliminate
duplicate account numbers due to the UNION as illustrated by the example
below.
CREATE TABLE TEMP
(
ACCOUNT int NOT NULL,
CLS_DATE smalldatetime NOT NULL
CONSTRAINT PK_TEMP PRIMARY KEY
(
ACCOUNT,
CLS_DATE
)
)
INSERT INTO TEMP VALUES (7321234, '20021229')
INSERT INTO TEMP VALUES (7321234, '20021230')
INSERT INTO TEMP VALUES (7321234, '20021231')
INSERT INTO TEMP VALUES (7321234, '20031229')
INSERT INTO TEMP VALUES (7321234, '20031230')
INSERT INTO TEMP VALUES (7321234, '20031231')
INSERT INTO TEMP VALUES (7324567, '20021229')
INSERT INTO TEMP VALUES (7324567, '20021230')
INSERT INTO TEMP VALUES (7324567, '20021231')
INSERT INTO TEMP VALUES (7324567, '20031229')
INSERT INTO TEMP VALUES (7324567, '20031230')
INSERT INTO TEMP VALUES (7324567, '20031231')
SELECT ACCOUNT
FROM TEMP
WHERE CLS_DATE > '20031230'
GROUP BY (ACCOUNT)
UNION
SELECT ACCOUNT
FROM TEMP
WHERE CLS_DATE > '20021230'
GROUP BY (ACCOUNT)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt" <metehanIT@.Hotmail.com> wrote in message
news:1135265718.603518.119100@.g47g2000cwa.googlegroups.com...
> SQL is almost 300 line thats why could not posted there
> This is one something like
> SELECT ACCOUNT
> FROM TEMP
> WHERE CLS_DATE > '12/30/03'
> GROUP BY (ACCOUNT)
> UNION
> SELECT ACCOUNT
> FROM TEMP
> WHERE CLS_DATE > '12/30/02'
> GROUP BY (ACCOUNT)
> Gives me result set of
> 7321234
> 7321234
> 7324567
> 7324567
> I need result set to be
> 7321234
> 7324567
> so on ,, as long as they dont repeat one after another.
> Cheers
>|||Thanks Don.

Group or Not group

Select FN,LN
Group by FN from A
Gives me
Bob
Bob
Tom
Tom
Tom
Bill
Bill
Need to achive output of names not after one another if they are same
name
Bob
Tom
Bill
Bob
Tom
Bob
Bill
Order is not important as long as they dont repaeat same name for next
row
On 21 Dec 2005 13:50:36 -0800, Matt wrote:

>Select FN,LN
>Group by FN from A
>Gives me
>Bob
>Bob
>Tom
>Tom
>Tom
>Bill
>Bill
Hi Matt,
You must have made a mistake in this post. That query can never result
in anything but an error message.

>Need to achive output of names not after one another if they are same
>name
>Bob
>Tom
>Bill
>Bob
>Tom
>Bob
>Bill
>Order is not important as long as they dont repaeat same name for next
>row
Not sure I understand the requirements, but it sounds like a task for
the front end.
If you really want to do this server-side, then please proviude better
specs. Check out www.aspfaq.com/5006.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Since you specify no ORDER BY, SQL Server may return data in any sequence.
To return data in a pseudo random order, you can include ORDER BY NEWID().

> Need to achive output of names not after one another if they are same
> name

> Order is not important as long as they dont repaeat same name for next
> row
Is random order acceptable? In order words, is it ok if 2 or more same
names are consecutive, if only by coincidence?
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt" <metehanIT@.Hotmail.com> wrote in message
news:1135201836.056730.13200@.o13g2000cwo.googlegro ups.com...
> Select FN,LN
> Group by FN from A
> Gives me
> Bob
> Bob
> Tom
> Tom
> Tom
> Bill
> Bill
> Need to achive output of names not after one another if they are same
> name
> Bob
> Tom
> Bill
> Bob
> Tom
> Bob
> Bill
> Order is not important as long as they dont repaeat same name for next
> row
>
|||Pushing those recordsets to another system, system does not allow me to
push smilar account one another.
So i cant push right after another. I need to push different name.
Since there are no other Indicator to differenciate the records
Above query was an example
any idea?
|||As Hugo requested, we really need DDL, a working query and sample to data to
help you out. Presuming that FN is the differentiator, what should be done
when it is impossible to order results to prevent consecutive duplicates?
Consider the following:
Tom
Bob
Tom
Tom
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt" <metehanIT@.Hotmail.com> wrote in message
news:1135260352.114345.114900@.g47g2000cwa.googlegr oups.com...
> Pushing those recordsets to another system, system does not allow me to
> push smilar account one another.
> So i cant push right after another. I need to push different name.
> Since there are no other Indicator to differenciate the records
> Above query was an example
> any idea?
>
|||SQL is almost 300 line thats why could not posted there
This is one something like
SELECT ACCOUNT
FROM TEMP
WHERE CLS_DATE > '12/30/03'
GROUP BY (ACCOUNT)
UNION
SELECT ACCOUNT
FROM TEMP
WHERE CLS_DATE > '12/30/02'
GROUP BY (ACCOUNT)
Gives me result set of
7321234
7321234
7324567
7324567
I need result set to be
7321234
7324567
so on ,, as long as they dont repeat one after another.
Cheers
|||> SQL is almost 300 line thats why could not posted there
300 lines is reasonable. Please post.
I still don't understand your problem. The query you posted will eliminate
duplicate account numbers due to the UNION as illustrated by the example
below.
CREATE TABLE TEMP
(
ACCOUNT int NOT NULL,
CLS_DATE smalldatetime NOT NULL
CONSTRAINT PK_TEMP PRIMARY KEY
(
ACCOUNT,
CLS_DATE
)
)
INSERT INTO TEMP VALUES (7321234, '20021229')
INSERT INTO TEMP VALUES (7321234, '20021230')
INSERT INTO TEMP VALUES (7321234, '20021231')
INSERT INTO TEMP VALUES (7321234, '20031229')
INSERT INTO TEMP VALUES (7321234, '20031230')
INSERT INTO TEMP VALUES (7321234, '20031231')
INSERT INTO TEMP VALUES (7324567, '20021229')
INSERT INTO TEMP VALUES (7324567, '20021230')
INSERT INTO TEMP VALUES (7324567, '20021231')
INSERT INTO TEMP VALUES (7324567, '20031229')
INSERT INTO TEMP VALUES (7324567, '20031230')
INSERT INTO TEMP VALUES (7324567, '20031231')
SELECT ACCOUNT
FROM TEMP
WHERE CLS_DATE > '20031230'
GROUP BY (ACCOUNT)
UNION
SELECT ACCOUNT
FROM TEMP
WHERE CLS_DATE > '20021230'
GROUP BY (ACCOUNT)
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt" <metehanIT@.Hotmail.com> wrote in message
news:1135265718.603518.119100@.g47g2000cwa.googlegr oups.com...
> SQL is almost 300 line thats why could not posted there
> This is one something like
> SELECT ACCOUNT
> FROM TEMP
> WHERE CLS_DATE > '12/30/03'
> GROUP BY (ACCOUNT)
> UNION
> SELECT ACCOUNT
> FROM TEMP
> WHERE CLS_DATE > '12/30/02'
> GROUP BY (ACCOUNT)
> Gives me result set of
> 7321234
> 7321234
> 7324567
> 7324567
> I need result set to be
> 7321234
> 7324567
> so on ,, as long as they dont repeat one after another.
> Cheers
>
|||Thanks Don.

Group or Not group

Select FN,LN
Group by FN from A
Gives me
Bob
Bob
Tom
Tom
Tom
Bill
Bill
Need to achive output of names not after one another if they are same
name
Bob
Tom
Bill
Bob
Tom
Bob
Bill
Order is not important as long as they dont repaeat same name for next
rowOn 21 Dec 2005 13:50:36 -0800, Matt wrote:

>Select FN,LN
>Group by FN from A
>Gives me
>Bob
>Bob
>Tom
>Tom
>Tom
>Bill
>Bill
Hi Matt,
You must have made a mistake in this post. That query can never result
in anything but an error message.

>Need to achive output of names not after one another if they are same
>name
>Bob
>Tom
>Bill
>Bob
>Tom
>Bob
>Bill
>Order is not important as long as they dont repaeat same name for next
>row
Not sure I understand the requirements, but it sounds like a task for
the front end.
If you really want to do this server-side, then please proviude better
specs. Check out www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Since you specify no ORDER BY, SQL Server may return data in any sequence.
To return data in a pseudo random order, you can include ORDER BY NEWID().

> Need to achive output of names not after one another if they are same
> name

> Order is not important as long as they dont repaeat same name for next
> row
Is random order acceptable? In order words, is it ok if 2 or more same
names are consecutive, if only by coincidence?
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt" <metehanIT@.Hotmail.com> wrote in message
news:1135201836.056730.13200@.o13g2000cwo.googlegroups.com...
> Select FN,LN
> Group by FN from A
> Gives me
> Bob
> Bob
> Tom
> Tom
> Tom
> Bill
> Bill
> Need to achive output of names not after one another if they are same
> name
> Bob
> Tom
> Bill
> Bob
> Tom
> Bob
> Bill
> Order is not important as long as they dont repaeat same name for next
> row
>|||Pushing those recordsets to another system, system does not allow me to
push smilar account one another.
So i cant push right after another. I need to push different name.
Since there are no other Indicator to differenciate the records
Above query was an example
any idea?|||As Hugo requested, we really need DDL, a working query and sample to data to
help you out. Presuming that FN is the differentiator, what should be done
when it is impossible to order results to prevent consecutive duplicates?
Consider the following:
Tom
Bob
Tom
Tom
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt" <metehanIT@.Hotmail.com> wrote in message
news:1135260352.114345.114900@.g47g2000cwa.googlegroups.com...
> Pushing those recordsets to another system, system does not allow me to
> push smilar account one another.
> So i cant push right after another. I need to push different name.
> Since there are no other Indicator to differenciate the records
> Above query was an example
> any idea?
>|||SQL is almost 300 line thats why could not posted there
This is one something like
SELECT ACCOUNT
FROM TEMP
WHERE CLS_DATE > '12/30/03'
GROUP BY (ACCOUNT)
UNION
SELECT ACCOUNT
FROM TEMP
WHERE CLS_DATE > '12/30/02'
GROUP BY (ACCOUNT)
Gives me result set of
7321234
7321234
7324567
7324567
I need result set to be
7321234
7324567
so on ,, as long as they dont repeat one after another.
Cheers|||> SQL is almost 300 line thats why could not posted there
300 lines is reasonable. Please post.
I still don't understand your problem. The query you posted will eliminate
duplicate account numbers due to the UNION as illustrated by the example
below.
CREATE TABLE TEMP
(
ACCOUNT int NOT NULL,
CLS_DATE smalldatetime NOT NULL
CONSTRAINT PK_TEMP PRIMARY KEY
(
ACCOUNT,
CLS_DATE
)
)
INSERT INTO TEMP VALUES (7321234, '20021229')
INSERT INTO TEMP VALUES (7321234, '20021230')
INSERT INTO TEMP VALUES (7321234, '20021231')
INSERT INTO TEMP VALUES (7321234, '20031229')
INSERT INTO TEMP VALUES (7321234, '20031230')
INSERT INTO TEMP VALUES (7321234, '20031231')
INSERT INTO TEMP VALUES (7324567, '20021229')
INSERT INTO TEMP VALUES (7324567, '20021230')
INSERT INTO TEMP VALUES (7324567, '20021231')
INSERT INTO TEMP VALUES (7324567, '20031229')
INSERT INTO TEMP VALUES (7324567, '20031230')
INSERT INTO TEMP VALUES (7324567, '20031231')
SELECT ACCOUNT
FROM TEMP
WHERE CLS_DATE > '20031230'
GROUP BY (ACCOUNT)
UNION
SELECT ACCOUNT
FROM TEMP
WHERE CLS_DATE > '20021230'
GROUP BY (ACCOUNT)
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt" <metehanIT@.Hotmail.com> wrote in message
news:1135265718.603518.119100@.g47g2000cwa.googlegroups.com...
> SQL is almost 300 line thats why could not posted there
> This is one something like
> SELECT ACCOUNT
> FROM TEMP
> WHERE CLS_DATE > '12/30/03'
> GROUP BY (ACCOUNT)
> UNION
> SELECT ACCOUNT
> FROM TEMP
> WHERE CLS_DATE > '12/30/02'
> GROUP BY (ACCOUNT)
> Gives me result set of
> 7321234
> 7321234
> 7324567
> 7324567
> I need result set to be
> 7321234
> 7324567
> so on ,, as long as they dont repeat one after another.
> Cheers
>|||Thanks Don.

Group by

I have a table defined with the following field names.
TableID Varchar(20)
CrcNbr Int
RegionName Varchar(25)
Sample Data:
--
pr_mstr,11500,Test
pr_mstr,11500,Trng
prd_det,12000,Test
prd_det,12005,Trng
prd_det,12005,Prod
I want to produce a report from this data that gives me this info. Saying
In what Regions does the table exist and are there a difference in the
CrcNbr's.
Table-Name Test Trng Prod Test/Trng Crc
Trng/Prod Crc
pr_mstr Y Y N Match
If 'N' under Prod tab leave this blank
prd_det Y Y Y Test Doesn't match Trng
MatchHi
It is usually better to do this one the client, but if not you can self join
the table and use case to determine the Ys or Ns such as:
SELECT T.Name,
CASE WHEN D1.RegionName IS NULL THEN 'N' ELSE 'Y' END AS Test,
CASE WHEN D2.RegionName IS NULL THEN 'N' ELSE 'Y' END AS Trng,
CASE WHEN D3.RegionName IS NULL THEN 'N' ELSE 'Y' END AS Prod,
CASE WHEN D1.CrcNbr <> D2.CrcNbr AND D1.CrcNbr <> D3.CrcNbr AND D3.CrcNbr <>
D2.CrcNbr THEN 'No Matches'
WHEN D1.CrcNbr <> D2.CrcNbr AND D1.CrcNbr <> D3.CrcNbr AND D3.CrcNbr =
D2.CrcNbr THEN 'Trng Matches Prod'
...
END AS [Crc Checks]
FROM MyTables T
LEFT JOIN MyData d1 on T.TableId = D1.TableId AND D1.RegionName = 'Test'
LEFT JOIN MyData d2 on T.TableId = D2.TableId AND D2.RegionName = 'Trng'
LEFT JOIN MyData d3 on T.TableId = D2.TableId AND D3.RegionName = 'Prod'
John
"Hoosbruin" wrote:

> I have a table defined with the following field names.
> TableID Varchar(20)
> CrcNbr Int
> RegionName Varchar(25)
> Sample Data:
> --
> pr_mstr,11500,Test
> pr_mstr,11500,Trng
> prd_det,12000,Test
> prd_det,12005,Trng
> prd_det,12005,Prod
>
> I want to produce a report from this data that gives me this info. Saying
> In what Regions does the table exist and are there a difference in the
> CrcNbr's.
> Table-Name Test Trng Prod Test/Trng Crc
> Trng/Prod Crc
> pr_mstr Y Y N Match
> If 'N' under Prod tab leave this blank
> prd_det Y Y Y Test Doesn't match Trng
> Match
>
>
>
>|||hi,
Select TableName,
Max (Case When RegionName = 'Test' Then 'Y' Else 'N' End) As Test,
Max (Case When RegionName = 'Trng' Then 'Y' Else 'N' End) As Trng,
Max (Case When RegionName = 'Prod' Then 'Y' Else 'N' End) As Prod,
Case When Sum (Case When RegionName = 'Test' Then CrcNbr
When RegionName = 'Trng' Then -1 * CrcNbr
Else 0
End) = 0 Then 'Match'
When Sum (Case When RegionName = 'Test' Then 1
When RegionName = 'Trng' Then 1
Else 0
End) = 2 Then 'NoMatch'
Else ''
End As 'Test/Trng',
Case When Sum (Case When RegionName = 'Prod' Then CrcNbr
When RegionName = 'Trng' Then -1 * CrcNbr
Else 0
End) = 0 Then 'Match'
When Sum (Case When RegionName = 'Prod' Then 1
When RegionName = 'Trng' Then 1
Else 0
End) = 2 Then 'NoMatch'
Else ''
End As 'Prod/Trng'
From <YourTable>
Group by TableName
"Hoosbruin" <Hoosbruin@.Kconline.com> wrote in message
news:TKudnS3FiNW6jkLfRVn-3w@.kconline.com...
>I have a table defined with the following field names.
> TableID Varchar(20)
> CrcNbr Int
> RegionName Varchar(25)
> Sample Data:
> --
> pr_mstr,11500,Test
> pr_mstr,11500,Trng
> prd_det,12000,Test
> prd_det,12005,Trng
> prd_det,12005,Prod
>
> I want to produce a report from this data that gives me this info. Saying
> In what Regions does the table exist and are there a difference in the
> CrcNbr's.
> Table-Name Test Trng Prod Test/Trng Crc Trng/Prod Crc
> pr_mstr Y Y N Match If 'N' under Prod tab
> leave this blank
> prd_det Y Y Y Test Doesn't match Trng
> Match
>
>
>
>|||Thanks...
worked GREAT !!!!!!
"arik" <arikf@.top4.com> wrote in message
news:OmQytdcjFHA.3580@.TK2MSFTNGP09.phx.gbl...
> hi,
> Select TableName,
> Max (Case When RegionName = 'Test' Then 'Y' Else 'N' End) As Test,
> Max (Case When RegionName = 'Trng' Then 'Y' Else 'N' End) As Trng,
> Max (Case When RegionName = 'Prod' Then 'Y' Else 'N' End) As Prod,
> Case When Sum (Case When RegionName = 'Test' Then CrcNbr
> When RegionName = 'Trng' Then -1 * CrcNbr
> Else 0
> End) = 0 Then 'Match'
> When Sum (Case When RegionName = 'Test' Then 1
> When RegionName = 'Trng' Then 1
> Else 0
> End) = 2 Then 'NoMatch'
> Else ''
> End As 'Test/Trng',
> Case When Sum (Case When RegionName = 'Prod' Then CrcNbr
> When RegionName = 'Trng' Then -1 * CrcNbr
> Else 0
> End) = 0 Then 'Match'
> When Sum (Case When RegionName = 'Prod' Then 1
> When RegionName = 'Trng' Then 1
> Else 0
> End) = 2 Then 'NoMatch'
> Else ''
> End As 'Prod/Trng'
> From <YourTable>
> Group by TableName
>
> "Hoosbruin" <Hoosbruin@.Kconline.com> wrote in message
> news:TKudnS3FiNW6jkLfRVn-3w@.kconline.com...
>