Group using text field

Posted on 2007-10-18
Medium Priority
Last Modified: 2010-05-18
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?

Question by:johnnyg123
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20103463
>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?

Expert Comment

ID: 20103547
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.

Expert Comment

ID: 20103574
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!
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 20103752
Thanks for all the replies

I was hoping to get the response via pure sql....is 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
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 20104435
what about this:

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

Author Comment

ID: 20109923

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


I got it to work

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question