March 17, 2012 at 11:45 am
Hi,
I revised the data in the source and result tables, as what I originally posted did not accurately capture the problem I am working on.
Please help me transform "SourceTbl" in the following SQL script to the "ResultTbl".
Thanks,
-- Brian
/* Find_dup_questionnaires */
USE QKB;
CREATE TABLE SourceTbl (
Col_1 VARCHAR(2) NOT NULL,
Col_2 VARCHAR(2) NOT NULL,
Col_3 VARCHAR(2) NOT NULL,
CONSTRAINT SourceTbl_PK PRIMARY KEY NONCLUSTERED (Col_1, Col_2, Col_3)
);
CREATE TABLE ResultTbl (
Col_1a VARCHAR(2) NOT NULL,
Col_1b VARCHAR(2) NOT NULL,
CONSTRAINT ResultTbl_PK PRIMARY KEY NONCLUSTERED (Col_1a, Col_1b)
);
INSERT INTO SourceTbl
(Col_1, Col_2, Col_3)
VALUES
('Q','A','D')
,('Q','B','E')
,('Q','C','F')
,('R','A','D')
,('R','B','E')
,('S','C','F')
,('S','A','D')
,('S','B','E')
,('T','C','F')
,('T','A','D')
,('T','B','E')
;
INSERT INTO ResultTbl
(Col_1a, Col_1b)
VALUES
('Q','S')
,('Q','T')
,('S','Q')
,('S','T')
,('T','Q')
,('T','S')
;
March 17, 2012 at 12:30 pm
Something like this?
SELECT
t1.col_1 AS col_1a,
t2.col_1 AS col_1b
FROM SourceTbl t1
INNER JOIN SourceTbl t2
ON t1.col_2=t2.col_2
AND t1.col_3=t2.col_3
AND t1.col_1<>t2.col_1
March 17, 2012 at 1:28 pm
I revised the data in the example I submitted, as the original example did not accurately capture the problem I am working on. Please take another look.
Thanks for the help,
-- Brian
March 18, 2012 at 5:30 pm
What are you trying to achieve, what is the logic for the rows in result ?
If you are trying to find duplicates:
select st.*
FROM SourceTbl st
JOIN
( -- find duplicate valus
select s.Col_2, s.Col_3
from SourceTbl s
group by s.Col_2, s.Col_3
having count(*)>1
) dup on st.Col_2=dup.Col_2 AND st.Col_3=dup.Col_3
ORDER BY st.Col_2, st.Col_3, st.Col_1
Result:
Col_1 Col_2 Col_3
----- ----- -----
Q A D
R A D
S A D
T A D
Q B E
R B E
S B E
T B E
Q C F
S C F
T C F
You see that in first group (A,D) we have Q, R, S, T in Col_1.
In second group (B, E) we also have Q, R, S, T in Col_1.
In third group (C, F) we have Q, S, T in Col_1 (without R).
If you would pick e.g. min value in Col_1 and update all Col_1 values in the group to that min value,
the select would look like this:
select *
--UPDATE t SET t.Col_1 = t.Col_1_NewValue -- Uncomment if you want to update and exclude row above
FROM
(
select st.*,
Col_1_NewValue = min(st.Col_1) OVER(PARTITION BY st.Col_2, st.Col_3)
FROM #SourceTbl st
JOIN
( -- find duplicate valus
select s.Col_2, s.Col_3
from #SourceTbl s
group by s.Col_2, s.Col_3
having count(*)>1
) dup on st.Col_2=dup.Col_2 AND st.Col_3=dup.Col_3
) t
WHERE t.Col_1 <> t.Col_1_NewValue
ORDER BY t.Col_2, t.Col_3, t.Col_1
Result:
Col_1 Col_2 Col_3 Col_1_NewValue
----- ----- ----- --------------
R A D Q
S A D Q
T A D Q
R B E Q
S B E Q
T B E Q
S C F Q
T C F Q
It is easy to convert that to an UPDATE by uncommenting the UPDATE line,
but update would not succeed because of unique constraint.
It would be much easier to give you the answer when I would know what do you really want.
Kind regards,
Vedran
March 20, 2012 at 11:48 am
If you can't explain the criteria for inclusion in the result table, I don't think we can help you. For example, why is 'R' not included anywhere in the result table, when rows with 'R' in col_1 have duplicate values in Col_2 and Col_3? Do all Col_2 and Col_3 have to match for a given pair of Col_1 values?Or is it just the 'C', 'F' combination in Col_2 and Col_3?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply