-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?

  • 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. – Scott Hodgin Nov 6 at 21:46
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://docs.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.