Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 strip out unmatched text with in a group .

Author  Topic 

Jampandu
Starting Member

12 Posts

Posted - 2013-02-24 : 21:22:20
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'

GROUPINGID DESCRIPTION
2 Hat red
2 Hat green
2 Hat yellow
3 Yellow Mens Sweater
3 Green Mens Sweater
4 ENDURO STYLE GRIPS BLUE
4 ENDURO STYLE GRIPS RED
4 OURY STD GRIP/BLACK/LOW FLANGE
5 PLEXUS 1/2 OUNCE
5 PLEXUS 7 OUNCE
5 PLEXUS 13 OUNCE

Expected output:

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


Please help me.

Thanks!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-25 : 07:57:15
Here is something to get you started. As I was writing it, it turned out to be more convoluted than what I thought it would be when I started writing it. So it is possible that you can write a simpler algorithm for it, so use this as something to compare to or something to get started with. You will need the DelimiterSplit8K from here: http://www.sqlservercentral.com/articles/Tally+Table/72993/
;WITH cte AS
(
SELECT * FROM
(
SELECT
*,
COUNT(Item) OVER (PARTITION BY Item,GroupingID) AS N1
FROM
(SELECT *,count(*) OVER (PARTITION BY GroupingID) AS N0 FROM #temp) AS t
CROSS APPLY dbo.DelimitedSplit8K(t.[DESCRIPTION],' ') dsk
) s
WHERE s.N0 = s.N1
)
SELECT
a.GroupingId,
LTRIM(d.Descriptions) AS Descriptions
FROM
(SELECT DISTINCT GroupingId FROM #temp ) AS a
OUTER APPLY
(
SELECT ' '+Item
FROM
(
SELECT DISTINCT groupingID, Item FROM cte b
WHERE b.GroupingId = a.GroupingId
) c
FOR XML PATH('')
) d(Descriptions)
Go to Top of Page

Jampandu
Starting Member

12 Posts

Posted - 2013-02-25 : 08:21:52
Thanks a lot James , this is what exactly I am looking for

But it is faling in below scenario :

10 KG FLEECE LINED HELMET BAG - RE
10 KG FLEECE LINED HELMET BAG - PU

in this case Expected output is "KG FLEECE LINED HELMET BAG -"
But the above Query is returning "- BAG FLEECE HELMET KG LINED" (order of words not correct )

also in 5 PLEXUS OUNCE

I guess it is ordering the text in alphabets wise , but not in actual order of words
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-25 : 09:13:26
There is no deterministic way that I can think of to order the rows in the general case in the scheme that I posted. For example, If you had rows like shown below, how would one determine the order?
10 KG FLEECE 10 KG FLEECE LINED HELMET BAG - RE
10 KG FLEECE LINED HELMET BAG - PU
FLEECE 10 LINED KG - BAG HELMET RX
One would have to think of another way to preserve the ordering or favor one ordering over another.
Go to Top of Page
   

- Advertisement -