Post reply

Collecting a number of text fields together using group by

  • July 10, 2009 at 9:33 am

    #132951

    Hi,

    Thanks in advance.

    I have an id column and dexcription column, for example:-

    id Description

    1 Toys

    1 Bikes

    1 Marbles

    2 Clothes

    2 Helmets

    3 Telescope

    3 Paint

    I would like to group them by the id into another table so that it will then be like the following:-

    id Description

    1 Toys Bikes Marbles

    2 Clothes Helmets

    3 Telescope Paint

    I can't seem to figure out a way to do it because of the text element. Any ideas on how I can overcome this in sql. I am using MSSQL 2005.

    Thanks

  • July 10, 2009 at 9:50 am

    #1023164

    You could probably do it with a Pivot table. It might not present you the results the same way you want to see them though. EG:

    DECLARE @TempTable TABLE

    (

    ID INT,

    [Description] VARCHAR(100)

    )

    INSERT INTO @TempTable (ID, [Description])

    SELECT 1, 'Toys'

    UNION

    SELECT 1, 'Bikes'

    UNION

    SELECT 1, 'Marbles'

    UNION

    SELECT 2, 'Clothes'

    UNION

    SELECT 2, 'Helmets'

    UNION

    SELECT 3, 'Telescope'

    UNION

    SELECT 3, 'Paint'

    SELECT

    ID,

    [Toys] AS [Toys],

    [Bikes] AS [Bikes],

    [Marbles] AS [Marbles],

    [Clothes] AS [Clothes],

    [Helmets] AS [Helmets],

    [Telescope] AS [Telescope],

    [Paint] AS [Paint]

    FROM

    (

    SELECT

    ID,

    [Description]

    FROM @TempTable

    ) RS

    PIVOT

    (

    MAX([Description])

    FOR

    [Description] IN ([Toys],[Bikes],[Marbles],[Clothes],[Helmets],[Telescope],[Paint])

    ) AS pvt

    Results in:

    ID Toys Bikes Marbles Clothes Helmets Telescope Paint

    1 Toys Bikes Marbles NULL NULL NULL NULL

    2 NULL NULL NULL Clothes Helmets NULL NULL

    3 NULL NULL NULL NULL NULL Telescope Paint

  • July 10, 2009 at 9:59 am

    #1023171

    select Id,

    substring((SELECT ( ', ' + Description )

    FROM #Temp n1

    where n1.id = #temp.id

    ORDER BY n1.id

    FOR XML PATH( '' )

    ), 3, 8000 )

    FROM #temp

    GROUP BY ID

    #temp is the table I inserted all your sample rows into.

  • July 13, 2009 at 1:56 am

    #1023707

    Hi Guys,

    Thanks for your reply and the queries provided but I don't think I was clear in my explanation on the table data.

    The description field may be text of any kind not neccessarily the data I have put within ie. Toys, Bikes etc. Hence doing a union on fields with this data in it will not work.

    Is there a way of grouping for any number of the same id column and collating the text together?

  • July 13, 2009 at 6:04 am

    #1023808

    If you insert all your sample data into #temp, then run my query, how does it not do what you are asking?

    create table #temp

    (

    ID INT,

    [Description] VARCHAR(100)

    )

    INSERT INTO #temp (ID, [Description])

    SELECT 1, 'Toys'

    UNION

    SELECT 1, 'Bikes'

    UNION

    SELECT 1, 'Marbles'

    UNION

    SELECT 2, 'Clothes'

    UNION

    SELECT 2, 'Helmets'

    UNION

    SELECT 3, 'Telescope'

    UNION

    SELECT 3, 'Paint'

    select Id,

    substring((SELECT ( ' ' + [Description] )

    FROM #temp n1

    where n1.id = #temp.id

    ORDER BY [Description] DESC

    FOR XML PATH( '' )

    ), 3, 8000 )

    FROM #temp

    GROUP BY ID

    Returns:

    1 Toys Marbles Bikes

    2 Helmets Clothes

    3 Telescope Paint

  • July 13, 2009 at 8:03 am

    #1023894

    Thanks ManicStar, this does the job, just what i needed..

    Thanks. you've saved me some serious time.

  • July 13, 2009 at 9:51 am

    #1023998

    No Problem. This code is an implementation of a tip in this article:

    http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=createacommadelimitedlist

  • July 19, 2009 at 9:39 pm

    #1026708

    Uh huh... the real question would be, "Why does this type of denormalization need to be done?" 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • July 20, 2009 at 12:56 pm

    #1027248

    Well, for one, I have had many report requests of the form 'pull all the notes for an order and put them in ONE column on a spreadsheet seperated by commas'.

    :hehe:

  • August 18, 2009 at 3:44 pm

    #1041203

    I get requests like that regularly, and use the same technique. Output is Excel or Crystal Reports. The only reason I'd drop it into a table is as an intermediate step to workaround the performance issues inherent in running queries against a (copy of) an OLTP database. It isn't really all that big, but it sure is slow ...

    Regards,

    John Hopkins

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply