CODEWITHSUNDEEP
t-sql
user856358
user856358

Reputation: 593

Get cartesian product of two columns

How can I get the cartesian product of two columns in one table?

I have table

A    1
A    2
B    3
B    4

and I want a new table

A    1
A    2
A    3
A    4
B    1
B    2
B    3
B    4

Upvotes: 1

Views: 1735

Answers (3)

vhadalgi
vhadalgi

Reputation: 7189

fiddle demo

your table

enter image description here

try this using joins

select  distinct b.let,a.id from [dbo].[cartesian] a join [dbo].[cartesian] b on a.id<>b.id

will result like this enter image description here

Upvotes: 2

Dominic P
Dominic P

Reputation: 2901

To clarify loup's answer (in more detail that allowable in a comment), any join with no relevant criteria specified will naturally produce a Cartesian product (which is why a glib answer to your question might be "all too easily"-- mistakenly doing t1 INNER JOIN t2 ON t1.Key = t1.Key will produce the same result).

However, SQL Server does offer an explicit option to make your intentions known. The CROSS JOIN is essentially what you're looking for. But like INNER JOIN devolving to a Cartesian product without a useful join condition, CROSS JOIN devolves to a simple inner join if you go out of your way to add join criteria in the WHERE clause.

If this is a one-off operation, it probably doesn't matter which you use. But if you want to make it clear for posterity, consider CROSS JOIN instead.

Upvotes: 0

Ardalan Shahgholi
Ardalan Shahgholi

Reputation: 12575

Create this table :

CREATE TABLE [dbo].[Table_1]
(
  [A] [int] NOT NULL ,
  [B] [nvarchar](50) NULL ,
  CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED ( [A] ASC )
    WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
           IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
           ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
)
ON  [PRIMARY]

Fill table like this :

INSERT  INTO [dbo].[Table_1]
VALUES  ( 1, 'A' )
INSERT  INTO [dbo].[Table_1]
VALUES  ( 2, 'A' )
INSERT  INTO [dbo].[Table_1]
VALUES  ( 3, 'B' )
INSERT  INTO [dbo].[Table_1]
VALUES  ( 4, 'C' )

SELECT  *
FROM    [dbo].[Table_1]

enter image description here

Use this query

SELECT DISTINCT
    T1.B ,
    T2.A
FROM    dbo.Table_1 AS T1 ,
    dbo.Table_1 AS T2
ORDER BY T1.B

enter image description here

Upvotes: 0

Related Questions