Group using text field

I have a table in sql server 2000 named Shop_Text which is defined as

seq #    int  (this is an identity field)

shop#   varchar(4)

shop_issue (text)

here is some sample data

seq #   shop #         Owner               shop_issue
1          1234             John Doe          the shop was called on 10/10/07
2          1234             John Doe          Need to send disks
3          1234             John Doe          Shop was able to resolve issue by copying files from disk
4          1235             Jane Doe          the shop was called on 10/20/07
5          1235             Jane Doe          Need to send updates
6          1235             Jane Doe          Shop was able to resolve issue by installiing updates

I would like to write a query such that the issues are grouped by shop.

Using the above data I would like to have the results look like

shop#          issues
1234            the shop was called on 10/10/07
                    Need to send disks
                   Shop was able to resolve issue by copying files from disk

1235            the shop was called on 10/20/07
                    Need to send updates
                   Shop was able to resolve issue by installiing updates

if I try to group by or do a distinct I get error message saying can't because it is a text field

Any ideas of how I can get the results I'm after?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Using the above data I would like to have the results look like
only via pure sql?

also, does the field really need to be TEXT, is VARCHAR(8000) to store up to 8000 characters not enough?
I agree with angelIII. If it doesn't need to be text then make it varchar. However if it does need to be text I think you should be able to say group by CAST(shop_issue AS VARCHAR(8000)). I have not tested it with a group by but I've run into a similar issue before using a text field in a WHERE clause and it worked. However you should note that if it does work your only grouping by the first 8000 characters as anything longer will be truncated.
Hi johnnyq123,

Sorry, but you cannot use Group By on text, ntext, or image data types; however, if your Shop# column is really just filled with integer numbers, you should be able to alter the table and change it to an integer column without losing any data.  I'm not sure how that would work with any other scripts you may have, of course.

If that would interfere with existing applications, you could, though I shudder at the thought, create another column with INT data type and the same data as the Shop# column.

I know this doesn't answer your real question: "how do I do it anyway."

Hope this helps!
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

johnnyg123Author Commented:
Thanks for all the replies

I was hoping to get the response via pure that not possible?  

Not necessarily concerned about the issues each being on a seperate line

I was just looking to see if I can concatenate all the issues for a given shop into a single field
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:

select case when seq = ( select min(i.seq#) from Shop_Text i where = ) then shop# else null end as shop
, shop_issue
from Shop_Text s
order by, s.seq#

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnnyg123Author Commented:

I went back to a udf that angelIII had helped me with for a related problem awhile back

I got it to work
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.