February 10, 2011 at 7:08 am
I have the following table:
ColAColBColC
72010
302010
302011
302012
72013
252013
72014
252014
302015
302016
4462017
4462018
7213
20213
25213
30213
302111
4462117
4462118
I want to make a new table which assigns each distinct ColA, ColB pair a value such that this new value is unique if that pair had a unique combination of ColC values, but not when it didn't.
I think the difficulty I'm having writing the SQL to do this is also down to the difficulty I'm having putting it into words, perhaps an example using the data above would help.
Taking only the ColB=21 rows, I have five different ColAs (7, 20, 25, 30, 446). My resulting table should look like the following such that each unique composite of the values for the pair in ColC has been assigned a unique value:
ColAColBColD(ColC Composite)
72113
202113
252113
302123, 11
44621317, 18
I get the feeling that there is a really easy way to write this, but I can't quite grasp it, and my current implementation has been known to take over 50 hours!
If anybody could point me in the right direction it would be much appreciated.
Mike
February 10, 2011 at 8:57 am
The following will order the compsite alphabetically but it will have an unique value:
;WITH Composites
AS
(
SELECT A.ColA, A.ColB
, STUFF(
(SELECT ' ' + CAST(B.ColC as varchar(255)) + ','
FROM YourTable B
WHERE A.ColA = B.ColA
AND A.ColB = B.ColB
FOR XML PATH('') )
,1
,1
,'') AS Composite
FROM YourTable A
GROUP BY A.ColA, A.ColB
)
SELECT DISTINCT ColA, ColB, Composite
,DENSE_RANK() OVER (PARTITION BY ColB ORDER BY Composite) AS ColC1
,DENSE_RANK() OVER (ORDER BY ColB, Composite) AS ColC2
FROM Composites
ORDER BY ColB, ColA
February 10, 2011 at 10:54 am
Wow, that's amazing. I really didn't expect you to actually do it for me. Thanks
February 10, 2011 at 11:05 am
Apologies for the lack of 'netiquette', this is my first post and I'll strive to do better in future. I can see how I confused you - I didn't actually want to keep the composite column - it's just how I'd work out in my own head what values to assign to the pairs. Poorly explained I know, but the other guy sorted it, so I'll spare you any second attempt:) Thanks again.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply