Grouping Sorting String and Numerical Fields

I've got a report built and I'm trying to figure out how sorting and grouping works. I can group the report by Patient, Albumin and it groups as I would expect.

Patient Date Albumin
Adams, John 01/28/2007 4.1
Adams, John 12/30/2007 3.9
Adams, John 01/15/2007 3.2
Barker, Mark 01/18/2007 4.3
Barker, Mark 01/22/2007 4.1
Barker, Mark 01/05/2007 3.9

However, when I try to group by Albumin, Patient, it just sorts by Albumin.
Patient Date Albumin
Barker, Mark 01/18/2007 4.3
Adams, John 01/28/2007 4.1
Barker, Mark 01/22/2007 4.1
Adams, John 12/30/2007 3.9
Barker, Mark 01/05/2007 3.9
Adams, John 01/15/2007 3.2

What I'm looking for is this:
Patient Date Albumin
Barker, Mark 01/18/2007 4.3
Barker, Mark 01/22/2007 4.1
Barker, Mark 01/05/2007 3.9
Adams, John 01/28/2007 4.1
Adams, John 12/30/2007 3.9
Adams, John 01/15/2007 3.2

Is this something that can be done with grouping and sorting?

Thanks,
Chad

Hi,

guess that you did not want to group you wanted to just sort, right ? For getting the results pasted below you will have to Sort by Patient Desc, Albumin Desc. If that is not your intention, please post the RDL and the way you want to display the information.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

grouping by partial string

Hello All,

I am looking for an expression for a group in a matric. I am trying to figure out how to group by the a certain amount of letters in a string. For example if I have the followong fields I am grouping...

Bob001

Bob

Robert005

Doug053

Doug100

Douglas

Barney001

Frank

I want to group it up as...

Bob

Doug

Barney

Frank

And then be able to summarize the results in the matrix.

Thanks in advance for any help

-Clint

If the last 3 characters are always 3 digits then you can simply group by left(Name, len(Name) - 3)

Adamus

|||

Just be sure your SELECT left(Name, len(Name) - 3) contains the trim.

Adamus

|||Unfortunately they do not all end in 3 digits. Ill fix my example. thanks though|||

Ok next question:

If digits exist, will they always be 3 digits on the tail end of the name?

If so you can check CASE Name WHEN isnumeric(right(Name, 3)) ...

If not, you'll probably want to use regular expressions to grab the text only portion.

In that case, you'll have to create a UDF which you can find here

Adamus

|||

Thanks Adamus,

Yes, when there are numbers present at the end, there are always three.

What would the expression look like?

Thanks much

Clint

|||

CREATETABLE #temp(myName varchar(20))

INSERTINTO #temp SELECT'Bob001'

INSERTINTO #temp SELECT'Bob'

INSERTINTO #temp SELECT'Robert001'

INSERTINTO #temp SELECT'Robert'

SELECT*FROM

(

SELECTCASEWHENISNUMERIC(RIGHT(myName, 3))= 1

THENLEFT(myName,LEN(myName)- 3)ELSE myName ENDAS [Result]

FROM #temp

)as t

GROUPBY Result

DROPTABLE #temp

Adamus

|||

What happened to Robert in the second list? Was he fired? Did he quit?

On A serious note, someone posted the code you need to remove the digits...

though it does not appear that it will solve the problem as "Douglas" has no digits on the end of it.

What you are looking for, sounds to me is some type of pattern matching...

|||

nice touch.. Mr. Turner...

I hope he actually uses the correct sql to do a select into the temp table instead of doing it all literally.

|||Thanks for the help. I am trying to accomplish in reporting services itself, not in the dbase. From what I gather there is now expression that would do the same thing. Again,I appreciate the help.|||

You can do this in SQL in the DataSet instead of the database or you can do in an expression using the Len function to find out how long the initial string is, the InStr function to find the location of a number, and then the Mid function to extract a string that is the Total Length long minus the Index value of the number that was found. Wrap all this in an IIF to handle the cases when no number is present.

HTH

Dean

|||

After regrouping at another field that seperated out the fields ending in - 083, i was able to get it working with your expression modified. Thanks so much for your help. Here is what ended up working...

Code Snippet

=iif(Fields!BankNumber.Value="083",left(Fields!TestName.Value, len(Fields!TestName.Value) - 11),Fields!TestName.Value)

|||oops. turns out not to work quite right after all. I errors out the first field name but works for the rest. I get a warning saying that the Length statement must be equal to or greater than 0. Since we are using a negative number, it is popping a warning.|||

Exactly. Im trying to figure out how to trim the last 11 characters off certain fields. My real life example has some fields ending in - Bank 083 (or another 3 numbers). i am thinking of the "LIKE" to seach for the pattern and remove it for group sake. Anyone have an idea on that one?

Thanks,

Clint

group results into a string

how to group results from a select statement with single column into a comma
separated string? e.g.
results
--
a
b
c
...
string:
a, b, c, ...nono
I suggest you doing such operations on the client side
create table #test
(
col char(1)
)
insert into #test values ('a')
insert into #test values ('b')
insert into #test values ('c')
declare @.str varchar(20)
set @.str=''
select @.str=@.str+coalesce(col,'')+';' from #test
select @.str
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:BFA2B825-A041-4775-9692-1D1C5E0490AE@.microsoft.com...
> how to group results from a select statement with single column into a
comma
> separated string? e.g.
> results
> --
> a
> b
> c
> ...
> string:
> a, b, c, ...|||how can I do that in SQL Server?
"Uri Dimant" wrote:

> nono
> I suggest you doing such operations on the client side
> create table #test
> (
> col char(1)
> )
> insert into #test values ('a')
> insert into #test values ('b')
> insert into #test values ('c')
> declare @.str varchar(20)
> set @.str=''
> select @.str=@.str+coalesce(col,'')+';' from #test
> select @.str
>
> "nonno" <nonno@.discussions.microsoft.com> wrote in message
> news:BFA2B825-A041-4775-9692-1D1C5E0490AE@.microsoft.com...
> comma
>
>|||I did show you , did not I?
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:C9E42970-F176-4B03-ADA2-7ABFCEC1B944@.microsoft.com...
> how can I do that in SQL Server?
> "Uri Dimant" wrote:
>|||Thx Uri! It's really fantastic! Can u tell me what's the coalesce function
used for?
"Uri Dimant" wrote:

> I did show you , did not I?
>
> "nonno" <nonno@.discussions.microsoft.com> wrote in message
> news:C9E42970-F176-4B03-ADA2-7ABFCEC1B944@.microsoft.com...
>
>|||BOL says:
COALESCE
Returns the first nonnull expression among its arguments
Fore more details please refer to the BOL.
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:DB38C965-1514-4560-97ED-0C36AB7DC2F8@.microsoft.com...
> Thx Uri! It's really fantastic! Can u tell me what's the coalesce function
> used for?
> "Uri Dimant" wrote:
>
into a|||> Thx Uri! It's really fantastic!
Note that this method is also not supported, and does not guarantee correct
results in all possible cases.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Uri,
Would you mind mentioning the potential drawbacks of this method while
suggesting it, at least in the future? Several newbies with limited SQL
exposure tend to misunderstand such constructs as a valid SQL queries and
may even pass onto others as a recommended approach.
Anith|||Anith
If you read my post carefully I did mention what I would have done in such
situations

>I suggest you doing such operations on the client side
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23lPkKPqPFHA.3336@.TK2MSFTNGP09.phx.gbl...
> Uri,
>
> Would you mind mentioning the potential drawbacks of this method while
> suggesting it, at least in the future? Several newbies with limited SQL
> exposure tend to misunderstand such constructs as a valid SQL queries and
> may even pass onto others as a recommended approach.
> --
> Anith
>|||>> If you read my post carefully I did mention what I would have done in
Ah..I did not notice that, my apologies :-(
Anith