February 24, 2013 at 7:27 pm
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
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(*)
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
Thanks Chrish , Can you give me the DelimitedSplit8K function defination
February 25, 2013 at 5:13 am
Sure, it's in here[/url]. The followup discussion is well worth a read.
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
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
;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
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