May 10, 2011 at 10:07 am
Hello all,
I have an interesting question.
I have a table with following data.
column1 column2 country
'abc' 'def' USA
'def' 'abc' USA
'xxx' 'yyy' Canada
'yyy' 'xxx' Canada
I need the result set to be the distinct values with any combination of column1 and column2
The result set should look as follows
result1
column1 column2 country
'abc' 'def' USA
'xxx' 'yyy' Canada
or
result2
column1 column2 country
'def' 'abc' USA
'yyy' 'xxx' Canada
it should display only unique values for the combination of column1 and column2
Do we have any sql function that can get this resolved ?
Bcoz i want to display the result in a view. so I am not concerned abt cursors.
Thanks in advance.
May 10, 2011 at 12:01 pm
If the results don't matter, then why do it? What if there is a 3rd entry for Canada? What are the results supposed to be?
Here's a script to generate the data, just looking for some clarification
declare @t table (column1 char(3), column2 Char(3), country varchar(10))
insert into @t values('abc', 'def', 'USA'),
('def', 'abc', 'USA'),
('xxx', 'yyy', 'Canada'),
('yyy', 'xxx', 'Canada')
What happens after these inserts?
insert into @t values('abc', 'yyy', 'Canada'),
('xxx', 'def', 'Canada')
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 10, 2011 at 12:53 pm
Something like this?
; WITH CTE AS
(
SELECT RN = ROW_NUMBER() OVER ( PARTITION BY Country , ( CHECKSUM( column1 + column2 ) + CHECKSUM( column2 + column1 )) ORDER BY ( SELECT NULL ))
,Column1 , column2 , country
from @t
)
SELECT column1 , column2 , country
FROM CTE
WHERE RN = 1
Please be advised that CHECKSUM is unreliable though, for certain cases 🙂
May 10, 2011 at 4:44 pm
OK, here is another solution, which is primarily for sets with more than 3 columns. For 2 columns, the other solution will scale up well ( imho )
; WITH UnPivotData AS
(
SELECT Vals ,
RN = ROW_NUMBER() OVER (ORDER BY Vals)
FROM @t SOURCE_TABLE
UNPIVOT
( Vals FOR ColName IN ( [column1] , [column2]) ) UNPIVOT_HANDLE
-- You'll 've to hardcode your column names above if the matching sets
-- are scattered in more than 2 columns
GROUP BY Vals
),
GroupedData AS
(
SELECT SrcData.column1 , SrcData.column2 , SrcData.country,
-- Add the columns here as well
RowNum = ROW_NUMBER() OVER ( PARTITION BY Country ,SUM ( Unpvt.RN ) ORDER BY ( SELECT 0) )
FROM UnPivotData Unpvt
INNER JOIN @t SrcData
ON ( SrcData.column1 = Unpvt.Vals OR SrcData.column2 = Unpvt.Vals )
-- You'll 've to add the others columns to the OR clause
-- if the matching sets are scattered in more than 2 columns
GROUP BY
SrcData.column1 , SrcData.column2 , SrcData.country
-- Add the columns here as well
)
SELECT column1 , column2 , country
FROM GroupedData
WHERE RowNum = 1
May 15, 2011 at 5:18 pm
CELKO (5/12/2011)
CREATE VIEW Foobar (country_name, col_1, col_2)AS
SELECT DISTINCT country_name,
CASE WHEN col_1 <= col_2 THEN col_1 ELSE col_2 END AS col_1,
CASE WHEN col_2 <= col_1 THEN col_1 ELSE col_2 END AS col_2
FROM Foob;
Ya just gotta love simple! 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply