Abu Dina (5/7/2013)

---

Morning all,

Could someone help me generate the following grouping please? Sample data plus expected output below:

Input Table: SiteMatches

Expected Output: SiteMatches_Output

CREATE TABLE dbo.SiteMatches (RetainedRID BIGINT, DroppedRidd BIGINT, Country VARCHAR(100))

INSERT INTO dbo.SiteMatches

SELECT 137478, 87728738, 'UK' UNION ALL

SELECT 137478, 95615, 'UK' UNION ALL

SELECT 851566, 447422, 'SCOTLAND' UNION ALL

SELECT 851566, 188827, 'SCOTLAND' UNION ALL

SELECT 851566, 326887, 'SCOTLAND'

CREATE TABLE dbo.SiteMatches_Output (ID BIGINT, SiteID BIGINT, Country VARCHAR(30))

INSERT INTO SiteMatches_Output

SELECT 137478, 1, 'UK' UNION ALL

SELECT 87728738, 1, 'UK' UNION ALL

SELECT 95615, 1, 'UK' UNION ALL

SELECT 851566, 2, 'SCOTLAND' UNION ALL

SELECT 447422, 2, 'SCOTLAND' UNION ALL

SELECT 188827, 2, 'SCOTLAND' UNION ALL

SELECT 326887, 2, 'SCOTLAND'

select * from SiteMatches

select * from SiteMatches_Output

drop table dbo.SiteMatches

drop table dbo.SiteMatches_Output