I have a table,
CREATE TABLE ResultItem ( IDResultItem INT PRIMARY KEY IDENTITY(1,1), ItemValue VARCHAR(20) NOT NULL, ResultID INT FOREIGN KEY REFERENCES Result(IDResult) )
I need to find all the most recent records in Result that have the same group of values in ResultItem in a previous Result, and concatenate the ItemValues in another column; for example:
INSERT INTO ResultItem (ItemValue, ResultID) VALUES ('a', 1), ('b', 1), ('a', 2), ('b', 2), ('c', 3), ('d', 3), ('c', 4), ('d', 4), ('a', 5), ('d', 5), ('a', 6), ('a', 7) ;
the query should return:
| 2 | 'ab' | | 4 | 'cd' | | 7 | 'a' |
as 1 and 2 are the same, 3 and 4 are the same, and 6 and 7 are the same. 5 has not yet been repeated so I do not want it. How would I go about doing this?