Post reply

strip out unmatched text with in a group

  • February 24, 2013 at 7:27 pm

    #271336

    Hi Experts,

    I need to do is remove all the words that are not contained in every record of the each groupingid group .

    That is I need is a way to strip out any text from a group that is not contained in all rows of the group with a space as deliminator

    CREATE TABLE #TEMP

    (

    GROUPINGID INT ,

    DESCRIPTION VARCHAR(MAX)

    )

    INSERT INTO #TEMP

    SELECT 2,'Hat red' UNION ALL

    SELECT 2,'Hat green' UNION ALL

    SELECT 2,'Hat yellow' UNION ALL

    SELECT 3,'Yellow Mens Sweater' UNION ALL

    SELECT 3,'Green Mens Sweater' UNION ALL

    SELECT 4,'ENDURO STYLE GRIPS BLUE' UNION ALL

    SELECT 4,'ENDURO STYLE GRIPS RED' UNION ALL

    SELECT 4,'OURY STD GRIP/BLACK/LOW FLANGE' UNION ALL

    SELECT 5,'PLEXUS 1/2 OUNCE' UNION ALL

    SELECT 5,'PLEXUS 7 OUNCE' UNION ALL

    SELECT 5,'PLEXUS 13 OUNCE'

    GROUPINGIDDESCRIPTION

    2Hat red

    2Hat green

    2Hat yellow

    3Yellow Mens Sweater

    3Green Mens Sweater

    4ENDURO STYLE GRIPS BLUE

    4ENDURO STYLE GRIPS RED

    4OURY STD GRIP/BLACK/LOW FLANGE

    5PLEXUS 1/2 OUNCE

    5PLEXUS 7 OUNCE

    5PLEXUS 13 OUNCE

    Expected output:

    groupid description

    2 Hat

    3 Mens Sweater

    4NULL (this is null because the same word is not repeated in all rows)

    5PLEXUS OUNCE

    Please help me.

    Thanks!

  • February 25, 2013 at 5:05 am

    #1590756

    Can you finish it from here?

    ;WITH CTEAgg AS (

    SELECT

    GroupingID,

    GroupItemCount = COUNT(*) OVER (PARTITION BY GroupingID),

    [Description]

    FROM #TEMP

    )

    SELECT GroupingID, Item

    FROM (

    SELECT

    GroupingID, GroupItemCount, [Description], Item

    FROM CTEAgg

    CROSS APPLY (SELECT Item FROM DelimitedSplit8K ([Description],' ') WHERE Item <> '') x

    GROUP BY GroupingID, GroupItemCount, [Description], Item

    ) d

    GROUP BY GroupingID, GroupItemCount, Item

    HAVING GroupItemCount = COUNT(*)

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • February 25, 2013 at 5:09 am

    #1590758

    Thanks Chrish , Can you give me the DelimitedSplit8K function defination

  • February 25, 2013 at 5:13 am

    #1590761

    Sure, it's in here[/url]. The followup discussion is well worth a read.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • February 25, 2013 at 6:55 am

    #1590792

    Thanks for sharing me the very worth full article

    Could you help me to get the output like below :

    groupid description

    2 Hat

    3 Mens Sweater

    4 NULL (this is null because the same word is not repeated in all rows)

    5 PLEXUS OUNCE

  • February 25, 2013 at 7:57 am

    #1590813

    ;WITH CTEAgg AS (

    SELECT

    GroupingID,

    GroupItemCount = COUNT(*) OVER (PARTITION BY GroupingID),

    [Description]

    FROM #TEMP

    )

    SELECT DISTINCT t.GroupingID, Item

    FROM #TEMP t

    OUTER APPLY (

    SELECT Item = STUFF(

    (

    SELECT ','+Item

    FROM (

    SELECT Item, GroupItemCount, [Description]

    FROM CTEAgg c

    CROSS APPLY (SELECT Item FROM DelimitedSplit8K ([Description],' ') WHERE Item <> '') x

    WHERE c.GroupingID = t.GroupingID

    GROUP BY GroupItemCount, [Description], Item

    ) d

    GROUP BY GroupItemCount, Item

    HAVING GroupItemCount = COUNT(*)

    ORDER BY Item

    FOR XML PATH (''))

    ,1,1,'')

    ) x

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 6 posts - 1 through 5 (of 5 total)

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