PanosPlat PanosPlat - 5 months ago 33
SQL Question

SQL Server find unique combinations

I have a table

rate_id service_id
1 1
1 2
2 1
2 3
3 1
3 2
4 1
4 2
4 3


I need to find and insert in a table the unique combinations of sevice_ids by rate_id...but when the combination is repeated in another rate_id I do not want it to be inserted

In the above example there are 3 combinations
1,2
1,3
1,2,3


How can I query the first table to get the unique combinations?

Thanx!

Answer

Try doing something like this:

DECLARE @TempTable TABLE ([rate_id] INT, [service_id] INT)

INSERT INTO @TempTable
VALUES (1,1),(1,2),(2,1),(2,3),(3,1),(3,2),(4,1),(4,2),(4,3)

SELECT DISTINCT
    --[rate_id], --include if required
    (
    SELECT
        CAST(t2.[service_id] AS VARCHAR) + ' '
    FROM
        @TempTable t2
    WHERE
        t1.[rate_id] = t2.[rate_id]
    ORDER BY
        t2.[rate_id]
    FOR XML PATH ('')
    ) AS 'Combinations'
FROM 
    @TempTable t1

I put the values in a table variable just for ease of testing the SELECT query.