Group By with Text field question

Posted on 2006-05-08
Last Modified: 2011-01-05
I have a table named Case_Text with the following layout
(Note: This is a vendor table so changing the table layout is not an option)

ColumnName               Type      Length

company_id               CHAR      3
case_id                               INT                     10
case_text_seq              INT                    10
case_text                             TEXT      

the following are some sample records:

company_id   case_id    case_text_seq       case_text
BSG                1                1                     This is case issue 1
BSG                1                2                     This is case issue 2
BSG                2                1                     This is case 2 issue 1

I am tying to write a group by sql statement that will group by company_id and case_id.

my objecitve is to get a result that looks like the following:

company_id              case_id    Case_Text
BSG                          1            This is case issue 1

                                             This is case issue 2                          
BSG                          2           This is case 2 issue 1  

I'm getting the dreaded "The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

I guess I could try to cast the text field to nvarchar but the text field can have way over 4000 characters which is what I think the limit of nvarchar is.

I was thinking of maybe using a derived table but not sure how to do that

Any help/ideas would be greatly appreciated



Question by:johnnyg123
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    Columns of type text, ntext, and image cannot be used in group_by_expression
    LVL 20

    Accepted Solution

    LVL 28

    Assisted Solution

    you have to limit it to 4000

    select company_id, cast(cast_text as varchar(4000)) from yourtable group by company_id, cast(cast_text as varchar(4000))

    Author Comment

    Thanks to all the took the time to verified the sinking feeling I had that waht I was trying to do can't be done the way I would like.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Suggested Solutions

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    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
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    737 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

    18 Experts available now in Live!

    Get 1:1 Help Now