August 2, 2018 at 4:34 pm
Hello ,
I have this sample data:
create table ##Test ([Primary KW] varchar (100), [Secondary KW] varchar (100), [Teritary KW] varchar (100), [Quaternary KW] varchar (100))
insert into ##Test ([Primary KW],[Secondary KW], [Teritary KW], [Quaternary KW])
values ('red', '1','year old','car'),
values ('','2','','truck'),
values ('','3','','')
Not sure if i have this right, but end goal is to have
red 1 year old car
red 2 year old car
red 3 year old car
red 1 year old truck
red 2 year old truck
red 3 year old truck
etc.
Need help with creating temp table with sample data, error in sample data, and actual query.
In sample data - i have 1st column & 3rd column in 2nd row as blank as that should be part of the Cartesian.
August 2, 2018 at 5:06 pm
Not sure if i have this right, but end goal is to have
red 1 year old car
red 2 year old car
red 3 year old car
red 1 year old truck
red 2 year old truck
red 3 year old truck
CREATE TABLE Vehicles (VehicleType VARCHAR(5) PRIMARY KEY);
CREATE TABLE Colors (Color VARCHAR(4) PRIMARY KEY);
CREATE TABLE Ages(Age TINYINT PRIMARY KEY);
GO
INSERT INTO Vehicles (VehicleType) VALUES ('Car'),('Truck');
INSERT INTO Colors(Color) VALUES ('red'),('blue');
INSERT INTO Ages (Age) VALUES (1),(2),(3);
SELECT VehicleType, Color, Age
FROM Vehicle CROSS JOIN Colors CROSS JOIN Ages.
August 2, 2018 at 5:13 pm
Thanks when running I got
Incorrect syntax near '.'.
August 2, 2018 at 5:17 pm
Ok I modified to, CREATE TABLE #Vehicles (VehicleType VARCHAR(5) PRIMARY KEY);
CREATE TABLE #Colors (Color VARCHAR(4) PRIMARY KEY);
CREATE TABLE #Ages(Age TINYINT PRIMARY KEY);
GO
INSERT INTO #Vehicles (VehicleType) VALUES ('Car'),('Truck');
INSERT INTO #Colors(Color) VALUES ('red'),('blue');
INSERT INTO #Ages (Age) VALUES (1),(2),(3);
SELECT VehicleType, Color, Age
FROM #Vehicles CROSS JOIN #Colors CROSS JOIN #Ages
This didn't produce error, but looking for 1 final column as result.
August 2, 2018 at 5:18 pm
Typo in there somewhere... this works. Basically, if you don't have a join between the tables (or explicitly state a CROSS JOIN), you get all possible combinations of the values in each table in the CROSS JOIN.
CREATE TABLE Vehicles (VehicleType VARCHAR(5) PRIMARY KEY);
CREATE TABLE Colors (Color VARCHAR(4) PRIMARY KEY);
CREATE TABLE Ages(Age TINYINT PRIMARY KEY);
GO
INSERT INTO Vehicles (VehicleType) VALUES ('Car'),('Truck');
INSERT INTO Colors(Color) VALUES ('red'),('blue');
INSERT INTO Ages (Age) VALUES (1),(2),(3);
SELECT VehicleType, Color, Age
FROM Vehicles CROSS JOIN Colors CROSS JOIN Ages;
August 2, 2018 at 5:25 pm
Thanks for trying to help -- appreciate it.
Your code produced:
VehicleType Color Age
Car blue 1
Car blue 2
Car blue 3
Car red 1
Car red 2
Car red 3
Truck blue 1
Truck blue 2
Truck blue 3
Truck red 1
Truck red 2
Truck red 3
but im looking for one final result where it concatenates the combinations into single column like below, not 3 separate columns
car blue 1
car blue 2
car blue 3
car red 1
car red 2
car red 3
truck blue 1
truck blue 2
August 2, 2018 at 6:26 pm
VegasL - Thursday, August 2, 2018 5:25 PMThanks for trying to help -- appreciate it.Your code produced:
VehicleType Color Age
Car blue 1
Car blue 2
Car blue 3
Car red 1
Car red 2
Car red 3
Truck blue 1
Truck blue 2
Truck blue 3
Truck red 1
Truck red 2
Truck red 3but im looking for one final result where it concatenates the combinations into single column like below, not 3 separate columns
car blue 1
car blue 2
car blue 3
car red 1
car red 2
car red 3
truck blue 1
truck blue 2
[vehicleType] & ' ' & [Color] & ' ' & CAST([Qty] AS CHAR) ?
Or use CONCAT()?
Had to leave something for you to do.
August 6, 2018 at 4:57 am
this is a simple requirement, you yourself can write the query for this..
anyway, Mr.PietLinden has tried to help you with a sample query..
what I meant to say here is,
if you expect the 100% perfect query for your requirement from the blog or any online reference means, then you could not develop your programming skill in SQL, once you get the sample query, you should understand the logic and Idea behind the code and modify the sample code as per your requirement for the realtime scenario of your's..
I believe I haven't hurt you.. if so, please excuse me..
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply