quote:

---

Originally posted by petebob796

I have an odd table I would like to group together to only get unique records where the data is the same in 4 fields but the data could be in any order over the 4 fields example below:
Field 1 Field 2 Field 3 Field 4
10 20 30 40
20 10 40 30
50 10 30 20

so if my database was like the above the first 2 records would be grouped because they contain the same data just in a different order (not bothered which is selected). There could in the future be more fields so I would like to get it right now.

---

-- prepare sample data
declare @tbl table (col1 int, col2 int, col3 int, col4 int)

insert @tbl
select	10, 20, 30, 40 union all
select	20, 10, 40, 30 union all
select	20, 10, 40, 30 union all
select	50, 10, 30, 20

-- show the expected output
SELECT DISTINCT	p.[1] AS col1,
		p.[2] AS col2,
		p.[3] AS col3,
		p.[4] AS col4
FROM		(
			SELECT		d.RowID, -- Not used in outer query but vital for correct result
					ROW_NUMBER() OVER (PARTITION BY d.RowID ORDER BY d.x) AS ColID,
					d.x
			FROM		(
						SELECT DISTINCT	RANK() OVER (ORDER BY t.col1, t.col2, t.col3, t.col4) AS RowID,
								c.x
						FROM		@tbl AS t
						CROSS APPLY	(
									SELECT t.col1 AS x UNION ALL
									SELECT t.col2 UNION ALL
									SELECT t.col3 UNION ALL
									SELECT t.col4
								) AS c
					) AS d
		) AS y
PIVOT		(
			MIN(y.x) FOR y.ColID IN ([1], [2], [3], [4]) -- MAX, AVG or SUM are equally good for this solution
		) AS p