Hi All,

I need help in the below query:

Declare @table table
(
consumerID varchar(10),
customerNumber varchar(10)
)

Insert into @table
Select 1,123
union all
Select 2,123
union all
Select 3,123
Union all
Select 4,123
Union all
Select 5,323
Union all
Select 6,323
Union all
Select 7,323


select *from @table

--Expected output concatenation of consumer numbers all permitation and combinations having same customernumber
1 2
1 3
1 4
2 3
2 4
3 4
5 6
5 7
6 7

Regards,

Ram.

What is the logic behind the scene?  For  consumer 2 I would also expect having 1, no?

No all possible combinations but no repetition.

1 2 is same as 2 1 but 1 2 is more preferred based on consumerid asc

Regards,

Ram.

 

Take a look at Paul's solution

Declare @table table
(
consumerID varchar(10),
customerNumber varchar(10),
bit_value   AS 
                CONVERT
                (
                    integer, 
                    POWER(2, consumerID - 1)
                )
                PERSISTED UNIQUE CLUSTERED
)

Insert into @table
Select 1,123
union all
Select 2,123
union all
Select 3,123
Union all
Select 4,123

select *from @table



DECLARE 
    @max integer = 
    POWER(2,
        (
            SELECT COUNT(*) 
            FROM @table AS s
        )
    ) - 1;

SELECT
    combination =
        STUFF
        (
            (
                -- Choose items where the bit is set
                -- and concatenate all matches
                SELECT ',' + s.consumerID 
                FROM @table AS s
                WHERE
                    n.n & s.bit_value = s.bit_value
                ORDER BY
                    s.bit_value
                FOR XML 
                    PATH (''),
                    TYPE                    
            ).value('(./text())[1]', 'varchar(8000)'), 1, 1, ''
        )
-- A standard numbers table
-- (single column, integers from 1 to 1048576, indexed)
FROM dbo.Numbers AS N
WHERE
    N.n BETWEEN 1 AND @max;

Hi,

Am I missing something or its easy like this?

select a.consumerID, b.consumerID
from @table AS a
inner join @table AS b ON a.consumerID < b.consumerID AND a.customerNumber = b.customerNumber
order by 1, 2

• Edited by Steelleg4 a few seconds ago