-3

I have 2 separate tables with no links between them. A customer table and a product table.

For each customer I wish to create a new row for each product in the product table I want the results to look as follows

CustomerNumber     Product          
1                   Item1   
1                   Item2           
1                   Item3 
2                   Item1   
2                   Item2           
2                   Item3 
3                   Item1   
3                   Item2           
3                   Item3 

How can achieve this with a single select statement?

1
  • Please update your question with actual table definitions in the form of DDL, sample data in the form of actual insert statements and your expected results from your query. Nov 6, 2019 at 21:46

2 Answers 2

2

A CROSS JOIN or CROSS APPLY will return a 'Cartesian product' or 'cross product' (i.e.: every combination of rows from the two tables.) In this case, those operators are functionally equivalent.

-- set up our tables
CREATE TABLE Customer (
    CustomerNumber int
)

CREATE TABLE Product (
    Product varchar(15)
)

INSERT INTO Customer ( CustomerNumber )
VALUES 
    ( 1 )
    , ( 2 )
    , ( 3 )

INSERT INTO Product ( Product )
VALUES
    ( 'Item1' )
    , ( 'Item2' )
    , ( 'Item3' )

-- use a CROSS JOIN
SELECT
    CustomerNumber
    , Product
FROM
    Customer
CROSS JOIN
    Product

-- OR use a CROSS APPLY
SELECT
    CustomerNumber
    , Product
FROM
    Customer
CROSS APPLY
    Product

The difference between the JOIN and APPLY operators is that an APPLY can have a table-valued function on the right side that uses parameter values from column values on the left side. So the APPLY can do some fancier stuff.

All the details are here: https://learn.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql

0

Try using CROSS APPLY. Here is an example:

--demo setup
Declare @Customer table (id int)
Declare @Product table (ProductDesc varchar(100))

insert into @Customer(id) values(1),(2),(3)

insert into @Product(ProductDesc) values('Item1'),('Item2'),('Item3')

--solution using cross apply
SELECT c.id
    ,p.ProductDesc
FROM @Customer c
CROSS APPLY @Product p
ORDER BY c.id

id  ProductDesc
1   Item1
1   Item2
1   Item3
2   Item1
2   Item2
2   Item3
3   Item1
3   Item2
3   Item3

Not the answer you're looking for? Browse other questions tagged or ask your own question.