July 22, 2015 at 3:46 am
Hi Guys ,
I have a table with below structure
PD D1 D2 D3
010 011 012 013
010 013 011 012
010 014 012 010
Now I want to find distinct rows from this table.
Now there is one catch in it the D1,D2,D3 columns values can be interchanged in rows.
For example in the above table the rows 1 and 2 are to be counted as one row,as D1 in row 1 has come in D2 in row 2,D2 in row 1 has come in D3 in row 2 and D3 in row1 has come in D1 in row 2 .So the same values are there in both rows only their columns are changed.
Is there a easy way to find out the distinct values from the above table with above condition.
In the above table only two distinct rows should come
Please help me
July 22, 2015 at 7:47 am
Maybe someone can find a better way of doing this, but here's an option.
It unpivots the table to order the 3 values, assigns a row number for each value and uses that value to create a cross tab. When we have the ordered values, we can remove duplicates.
When posting sample data, please try to do it the way I did in here so we don't have to waste time creating the tables and sample data to test. You'll get better and faster answers if you do.
CREATE TABLE SampleData(
PD char(3),
D1 char(3),
D2 char(3),
D3 char(3)
);
INSERT INTO SampleData VALUES
('010', '011', '012', '013'),
('010', '013', '011', '012'),
('010', '014', '012', '010');
SELECT DISTINCT --Remove duplicates
UP.PD,
MAX(CASE WHEN UP.rn = 1 THEN UP.DValue END) D1,
MAX(CASE WHEN UP.rn = 2 THEN UP.DValue END) D2,
MAX(CASE WHEN UP.rn = 3 THEN UP.DValue END) D3
FROM SampleData s
CROSS APPLY ( SELECT
PD,
ROW_NUMBER() OVER(PARTITION BY PD ORDER BY DValue) rn,
DValue
FROM (VALUES(PD, D1),
(PD, D2),
(PD, D3))x(PD, DValue)) UP(PD, rn, DValue)
GROUP BY UP.PD,
s.D1, s.D2, s.D3; --Use this rows to identify each original row
July 22, 2015 at 11:51 pm
Many thanks Luis
July 23, 2015 at 7:35 am
sunil88_pal88 (7/22/2015)
Many thanks Luis
You're welcome.
Do you understand how it works? Please post any questions that you have about this.
Here are some references:
Unpivot using CROSS APPLY: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
Cross tabs: http://www.sqlservercentral.com/articles/T-SQL/63681/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply