List Combinations

1

I have 2 cols Id and port as shown

I1 P1
I2 P2
I3 P3

I want to list down all the combination as shown

I1 > P1 , I2 > P1 , I3 > P1 ---- this line should be unique
I1>P1 , I2>P1 , I3 > P1
I1 > P1 , I1> P2 , I1 > P3

so on

basically the single line should be unique , here there are 3 ID and 3 port so 9 cases are possible.

Is there any way to do this in sql

Thanks

2

Is this a real world scenario?
And why is it you want to do this?

3

Hi,

Is that what you looking for?

Declare @table Table(id varchar(2),iport varchar(2))
insert @table
(id,iport)
select id,iport from (
	select id='I1',iport='P1' union all
	select  'I2','P2' union all
	select  'I3','P3'
) k 


;WITH a1(N)
     AS (SELECT id
         FROM  @table),
		 a2(N2)
     AS (SELECT iport
         FROM  @table)
select * from a1
cross join (
	select * from a2
)k

Or

select f1.id,
       f2.iport
       from @table as f1 cross join @table as f2
       where f1.id < f2.iport
       order by f1.id;

Thanks.

Regards,
Micheale

4

Yes but i need i combination in one column
Like
id1 port 1 id2 port 2 id3 port 3
Id1 port1 id2 port1 id3 port1

Like this each record needs to be unique

5

Hi,

Refer to this post:

Thanks.

Regards,
Micheale

6

Hi,

Just now busy with meeting. Now, I re-look your post.
Try this:

    Declare @table Table(id varchar(max))
    Declare @table2 Table(id varchar(max))
    Declare @table3 Table(id varchar(max))
   ;with id as(
    	select id from (
    		select id='Id1' union all
    		select  'Id2' union all
    		select  'Id3'
    	) k 
    ), iport as(
    	select iport from (
    		select iport='Port1' union all
    		select  'Port2' union all
    		select  'Port3'
    	) k 
    )
    insert into @table
    select col1=c1.id+'>'+c2.iport
    from id c1 
        cross join iport c2 
    where c1.id < c2.iport
    order by c1.id, c2.iport


    ;with cteAllColumns as (
        select id as col
            from @table
    )
    insert into @table2
    (id)
    select col1=c1.col+','+c2.col
    from cteAllColumns c1 
        cross join cteAllColumns c2 
    where c1.col < c2.col
    order by c1.col, c2.col


    ;with cteAllColumns as (
       select distinct id as col from @table2
    )
    insert into @table3
    (id)
    select col1=c1.col+','+c2.col
    from cteAllColumns c1 
        cross join cteAllColumns c2 
    where c1.col < c2.col
    GROUP BY c1.col+','+c2.col
    order by c1.col+','+c2.col



    select id=replace([dbo].[SplitAndRemoveDuplicates](',',id),'&gt;','>') 
    from @table3 
    where  (LEN(replace([dbo].[SplitAndRemoveDuplicates](',',id),'&gt;','>')) - LEN(REPLACE(replace([dbo].[SplitAndRemoveDuplicates](',',id),'&gt;','>'), ';', '')))/LEN(';')   =3
    group by replace([dbo].[SplitAndRemoveDuplicates](',',id),'&gt;','>')



CREATE FUNCTION [dbo].[SplitAndRemoveDuplicates] (@sep VARCHAR(32), @s VARCHAR(MAX))

    RETURNS VARCHAR(MAX)  
    AS
    BEGIN

       DECLARE @t TABLE (val VARCHAR(MAX)) 

       DECLARE @xml XML
       SET @xml = N'<root><r>' + REPLACE(@s, @sep, '</r><r>') + '</r></root>'

       INSERT INTO @t(val) SELECT r.value('.','VARCHAR(MAX)') as Item FROM @xml.nodes('//root/r') AS RECORDS(r)

       ;WITH cte
        AS (SELECT ROW_NUMBER() OVER (PARTITION BY val ORDER BY val desc) RN
        FROM  @t)
        DELETE FROM cte
        WHERE  RN > 1

        RETURN (SELECT val + ';' from @t WHERE val <> '' FOR XML PATH ('') )
    END

Regards,
Micheale

7

can it be done using Redshift ? I think the above solution uses function outside of redshift

8

Hi Niraj

this post is from 6 months ago

I tried to do this !!!!
Please review and give your feedback .. !!!

please click arrow to the left for DROP Create Sample DATA 
DROP TABLE #DATA 
GO 

CREATE TABLE #DATA 
(
id VARCHAR(5),
PORT VARCHAR(5)
)
GO 

INSERT INTO #DATA SELECT 'I1','P1'
INSERT INTO #DATA SELECT 'I2','P2'
INSERT INTO #DATA SELECT 'I3','P3'
GO 

SELECT *FROM #data

image

please click arrow to the left for SQL ... 
;WITH CTE AS 
(
SELECT id+'>'+PORT AS OK FROM #DATA
)
SELECT 
      'SQL OUTPUT'
	, A.OK+','+B.OK+','+C.OK 
FROM 
      CTE A 
        CROSS JOIN  CTE B 
	       CROSS JOIN CTE C
WHERE 
      A.OK <> B.OK 
        AND 
	  B.OK <> C.OK 
	    AND 
	  A.OK <> C.OK
GO

image