unique combination of concatenated columns
-
Hi, All. Could you, please, help me with sql query ? For example, if I have a table1 like the following
(fields/values separated by commas):
component, "bin", "prd"
2.1, "1", "217"
6.5, "4", "217"
7.1 ,"3", "217"
7.6 ,"5", "217"
7.7,"5", "217"
1.3 ,"2", "217"
1.1,"1", "298"
3.1 ,"1", "298"
6.2 ,"2", "298"
7.3 ,"5", "298"
8.1 ,"3", "298"
8.4 ,"4", "298"
1.1 ,"5", "298"
and I want to produce a table2 below:
prd ,combo
217 ,2.1|1.3|7.1|6.5|7.6
217 ,2.1|1.3|7.1|6.5|7.7
298 ,1.1|6.2|8.1|8.4|1.1
298 ,1.1|6.2|8.1|8.4|7.3
298 ,3.1|6.2|8.1|8.4|1.1
298 ,3.1|6.2|8.1|8.4|7.3
whereas column 'combo' contains all possible unique combinations of components in table1 written in ascending order of table1 'bin' values.
Thank you in advance as your help is very much appreciated.
Cheers,
ric
-
DECLARE @table1 TABLE(component VARCHAR(10), bin INT, prd INT)
INSERT INTO @table1(component, bin, prd)
VALUES('2.1' , 1, 217),
('6.5' , 4, 217),
('7.1' ,3, 217),
('7.6' ,5, 217),
('7.7' ,5, 217),
('1.3' ,2, 217),
('1.1' ,1, 298),
('3.1' ,1, 298),
('6.2' ,2, 298),
('7.3' ,5, 298),
('8.1' ,3, 298),
('8.4' ,4, 298),
('1.1' ,5, 298);
WITH CTE AS (
SELECT component, bin, prd,
DENSE_RANK() OVER(PARTITION BY prd ORDER BY bin) AS dr
FROM @table1),
Recur AS (
SELECT prd,dr,CAST(component AS VARCHAR(1000)) AS combo
FROM CTE
WHERE dr=1
UNION ALL
SELECT c.prd,c.dr,CAST(r.combo + '|' + c.component AS VARCHAR(1000))
FROM Recur r
INNER JOIN CTE c ON c.dr = r.dr+1
AND c.prd = r.prd),
Results AS (
SELECT prd,combo,
RANK() OVER(PARTITION BY prd ORDER by dr DESC) AS rn
FROM Recur)
SELECT prd,combo
FROM Results
WHERE rn=1
ORDER BY prd,combo;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Sorry! Strike that. The solution I posted didn't work quite as advertised.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
A recursive CTE approach does appear to be the way to go for this. Perhaps this one is a little simpler to understand.
DECLARE @table1 TABLE(component VARCHAR(10), bin INT, prd INT)
INSERT INTO @table1(component, bin, prd)
VALUES('2.1' , 1, 217),('6.5' , 4, 217),('7.1' ,3, 217),('7.6' ,5, 217),('7.7' ,5, 217),('1.3' ,2, 217),
('1.1' ,1, 298),('3.1' ,1, 298),('6.2' ,2, 298),('7.3' ,5, 298),('8.1' ,3, 298),('8.4' ,4, 298),('1.1' ,5, 298);
;WITH UNIQUEnTuples (n, prd, bin, component, ID) AS (
SELECT 1, prd, CAST(bin AS VARCHAR(8000))
,'[' + CAST(component AS VARCHAR(8000)) + ']'
,bin
FROM @table1
UNION ALL
SELECT 1 + n.n, prd, CAST(t.bin AS VARCHAR(8000)) + ',' + n.bin
,'[' + CAST(t.component AS VARCHAR(8000)) + ']' + n.component
,t.bin
FROM UNIQUEnTuples n
CROSS APPLY (
SELECT bin, component
FROM @table1 t
WHERE t.bin < n.ID AND t.prd = n.prd) t
)
SELECT n, a.prd, a.component
FROM (
SELECT n, prd, bin, component
,m=MAX(n) OVER (PARTITION BY prd)
FROM UNIQUEnTuples) a
WHERE n = m
ORDER BY n, a.prd, a.bin
The UNIQUEnTuples rCTE was first described in this article: http://www.sqlservercentral.com/articles/sql+n-Tuples/89809/
This is actually a slightly performance-improved version that I posted late in the discussion thread.
Hopefully the OP will let us know what worked for him.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply