SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 strip out unmatched text with in a group .
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Jampandu
Starting Member

India
11 Posts

Posted - 02/24/2013 :  21:22:20  Show Profile  Reply with Quote
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!

Edited by - Jampandu on 02/25/2013 01:51:18

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 02/25/2013 :  07:57:15  Show Profile  Reply with Quote
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)

Edited by - James K on 02/25/2013 08:00:08
Go to Top of Page

Jampandu
Starting Member

India
11 Posts

Posted - 02/25/2013 :  08:21:52  Show Profile  Reply with Quote
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

Edited by - Jampandu on 02/25/2013 08:43:04
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 02/25/2013 :  09:13:26  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000