Group using text field

Posted on 2007-10-18
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 142

    Expert Comment

    by: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?
    LVL 1

    Expert Comment

    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.
    LVL 6

    Expert Comment

    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!

    Author Comment

    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
    LVL 142

    Accepted Solution

    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#

    Author Comment


    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

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now