Find all 35 combinations in 7 numbers ?
-
I have a table like this :
How can I get all possible 35 combinations of each date by SQL query.
Thanks very much :-):-):-)
-
Can't see the image, but generally speaking, when you want "all possible combinations", you need a CROSS JOIN between two tables... If you had a table of Courses and a table of Students and you wanted all possible combinations of Course.CourseID and Student.StudentID, you would write something like this:
SELECT Student.StudentID,Course.CourseID
FROM Student CROSS JOIN Course;
-
Thanks very much
the image is updated ?
any detail SQL statement would you suggest ?
-
calvin_wk_wong (7/24/2016)
Thanks very muchthe image is updated ?
any detail SQL statement would you suggest ?
What is the domain of numbers involved?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
What are N1...N7? Are they the number of days to add to a date?
Maybe this article will help... should be required reading.
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Could you post CREATE TABLE and INSERT scripts so we can see what you're working with? Pictures are pretty, but they don't paste into SSMS really well...
-
pietlinden (7/24/2016)
What are N1...N7? Are they the number of days to add to a date?Maybe this article will help... should be required reading.
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Could you post CREATE TABLE and INSERT scripts so we can see what you're working with? Pictures are pretty, but they don't paste into SSMS really well...
This is a table
N1, N2, N3.....is the column name (header)
-
calvin_wk_wong (7/24/2016)
pietlinden (7/24/2016)
What are N1...N7? Are they the number of days to add to a date?Maybe this article will help... should be required reading.
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Could you post CREATE TABLE and INSERT scripts so we can see what you're working with? Pictures are pretty, but they don't paste into SSMS really well...
This is a table
N1, N2, N3.....is the column name (header)
That's how it's been asked on the test.
It's a theoretical question to test the skills, no actual business meaning behind it.
_____________
Code for TallyGenerator -
I am not sure what you mean by 35 combinations and your description is not detailed enough either.
Your request doesn't seem too complex and I am sure you will get a quick response if you provide us the below mentioned things.
1. DDL statements of the table(s) involved
2. Some sample data to populate the table(s), at least for 2 - 3 dates
3. Expected results in an easy to understand form
This will help us understand your question better and also test any solutions before posting it to you.
You can have a look at the link in my signature if you are not sure on how to do this.
Kingston DhasianHow to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
Complete stab in the dark, that what you are looking at is some kind of table with 5 rows and based on what can be seen, and assuming it is indeed only one table: Lots of if buts and maybes here, which is why it is important to provide the detail requested; how about:
CREATE TABLE dbo.MyHomework
(
MyDateCol date
, N1 INT
, N2 INT
, N3 INT
, N4 INT
, N5 INT
, N6 INT
, N7 INT
, CONSTRAINT PK_MyHW PRIMARY KEY CLUSTERED (MyDateCol) ON [PRIMARY]
);
INSERT INTO dbo.MyHomework (MyDateCol, N1, N2, N3, N4, N5, N6, N7)
VALUES
('04-07-2002', 5, 32, 34, 39, 49, 38, 8),
('06-07-2002', 21, 10, 33, 40, 20, 44, 34),
('08-07-2002', 10, 36, 48, 35, 30, 38, 12);
SELECT MyDateCol, Numb, MyCol
FROM dbo.MyHomework
UNPIVOT (
MyCol FOR Numb IN (
N1, N2, N3, N4, N5, N6, N7
)
) unpvt
...
-
That's how it's been asked on the test.
Then wouldn't it be more honest to just say you don't know?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
I think Andrew (Happy Geek) hit the nail on the head. Assuming that there are 5 dates/rows total there should be a total of 35 number/date combinations.
Taking Andrew's test data and adding two more rows we have this:
CREATE TABLE dbo.MyHomework
(
MyDateCol date
, N1 INT
, N2 INT
, N3 INT
, N4 INT
, N5 INT
, N6 INT
, N7 INT
, CONSTRAINT PK_MyHW PRIMARY KEY CLUSTERED (MyDateCol) ON [PRIMARY]
);
INSERT INTO dbo.MyHomework (MyDateCol, N1, N2, N3, N4, N5, N6, N7)
VALUES
('04-07-2002', 5, 32, 34, 39, 49, 38, 8),
('06-07-2002', 21, 10, 33, 40, 20, 44, 34),
('08-07-2002', 10, 36, 48, 35, 30, 38, 12),
('10-07-2002', 27, 26, 28, 31, 30, 36, 2),
('12-07-2002', 50, 46, 41, 75, 32, 3, 1);
Here's an alternative method for resolving this which will produces the same results and same execution plan minus the compute scalar and filter operators:
-- Unpivot alternative
SELECT MyDateCol, Numb, MyCol
FROM dbo.MyHomework
CROSS APPLY
(VALUES
('N1',N1),('N2',N2),('N3',N3),('N4',N4),('N5',N5),('N6',N6),('N7',N7)
) AS UnpivotTable (Numb, MyCol);
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply