Check It Out!
1. Sample Table and DataCREATE TABLE Table1
(
[ID] INT ,
[Value] VARCHAR(5)
)
GO
INSERT [dbo].[Table1] ([ID], [Value]) VALUES (1, 'A')
INSERT [dbo].[Table1] ([ID], [Value]) VALUES (1, 'B')
INSERT [dbo].[Table1] ([ID], [Value]) VALUES (2, 'A')
INSERT [dbo].[Table1] ([ID], [Value]) VALUES (2, 'C')
INSERT [dbo].[Table1] ([ID], [Value]) VALUES (2, 'B')
INSERT [dbo].[Table1] ([ID], [Value]) VALUES (3, 'A')
INSERT [dbo].[Table1] ([ID], [Value]) VALUES (3, 'B')
INSERT [dbo].[Table1] ([ID], [Value]) VALUES (3, 'C')
INSERT [dbo].[Table1] ([ID], [Value]) VALUES (3, 'D')
GO
2. Script for Not Common/*NOT COMMON*/
;WITH Combination
AS
(
SELECT DISTINCT A.ID, B.Value FROM Table1 A, Table1 B
), [Not Common]
AS
(
SELECT DISTINCT C.value [Not Common] FROM Combination C LEFT JOIN Table1 T1
ON (C.ID = T1.ID AND C.Value = T1.Value)
WHERE (T1.ID IS NULL AND T1.Value IS NULL)
)
SELECT [Not Common] FROM [Not Common]
GO
Not Common
----------
C
D
3. Script for Common/*COMMON*/
;WITH Combination
AS
(
SELECT DISTINCT A.ID, B.Value FROM Table1 A, Table1 B
), [Not Common]
AS
(
SELECT DISTINCT C.value [Not Common] FROM Combination C LEFT JOIN Table1 T1
ON (C.ID = T1.ID AND C.Value = T1.Value)
WHERE (T1.ID IS NULL AND T1.Value IS NULL)
), [Common]
AS
(
SELECT T1.* FROM Table1 T1 WHERE NOT EXISTS(SELECT 1 FROM [Not Common] NC WHERE NC.[Not Common] = T1.Value)
)
SELECT DISTINCT Value [Common] FROM [Common]
GO
Common
----------
A
B
Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions
Sankar20092010, if this helps please login to Mark As Answer. | Alert Moderator