asked on

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?

Guy Hengel [angelIII / a3]

>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?

ReconIT

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.

grendel777

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!

johnnyg123

ASKER

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

johnnyg123

ASKER

Actually,

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

https://www.experts-exchange.com/questions/21615213/Combining-Multiple-rows-into-one-row.html

I got it to work