September 15, 2018 at 12:32 pm
Hello Guys,
How are you?
Could you please help me with a question?
I have a table in sql with the databelow:
| GPID
| NAME
| Functions
| Function Description
|
| 09193580
| JACK
| MM05
| Process Goods Receipts
|
| 09193580
| JACK
| MM18
| MEM Stock Movements
|
| 09193580
| JACK
| O209
| Process Customer Credit Note - FI
|
| 09194040
| PAUL
| O208
| Post Customer Down-Payment
|
| 09194040
| PAUL
| O201
| Block or Release Documents for Credit Purposes
|
| 09194040
| PAUL
| FI13
| Post Parked Document
|
I need to create another table orcolumn using the field "Functions". The first value (MM05) + theother subsequent lines. However, It is necessary consider the field “NAME” andcopy just the Function related to the “NAME” The result can be:
| GPID
| NAME
| Function
| Function 2
|
| 09193580
| JACK
| MM05
| MM05
|
| 09193580
| JACK
| MM05
| MM18
|
| 09193580
| JACK
| MM05
| O209
|
| 09193580
| JACK
| MM18
| MM05
|
| 09193580
| JACK
| MM18
| MM18
|
| 09193580
| JACK
| MM18
| O209
|
| 09193580
| JACK
| O209
| MM05
|
| 09193580
| JACK
| O209
| MM18
|
| 09193580
| JACK
| O209
| O209
|
| 09194040
| PAUL
| O208
| O208
|
| 09194040
| PAUL
| O208
| O201
|
| 09194040
| PAUL
| O208
| FI13
|
| 09194040
| PAUL
| O201
| O208
|
| 09194040
| PAUL
| O201
| O201
|
| 09194040
| PAUL
| O201
| FI13
|
| 09194040
| PAUL
| FI13
| O208
|
| 09194040
| PAUL
| FI13
| O201
|
| 09194040
| PAUL
| FI13
| FI13
|
What'sthe best way to build this?
Thanks so much,
Erick
September 15, 2018 at 2:39 pm
No, I can just replay the "FUNCTIONS", It is necessary consider the field “NAME” andcopy just the Function related to the “NAME”.
September 16, 2018 at 11:03 am
erickegea - Saturday, September 15, 2018 12:32 PMHello Guys,
How are you?
Could you please help me with a question?
I have a table in sql with the databelow:
GPID
NAME
Functions
Function Description
09193580
JACK
MM05
Process Goods Receipts
09193580
JACK
MM18
MEM Stock Movements
09193580
JACK
O209
Process Customer Credit Note - FI
09194040
PAUL
O208
Post Customer Down-Payment
09194040
PAUL
O201
Block or Release Documents for Credit Purposes
09194040
PAUL
FI13
Post Parked Document
I need to create another table orcolumn using the field "Functions". The first value (MM05) + theother subsequent lines. However, It is necessary consider the field “NAME†andcopy just the Function related to the “NAME†The result can be:
GPID
NAME
Function
Function 2
09193580
JACK
MM05
MM05
09193580
JACK
MM05
MM18
09193580
JACK
MM05
O209
09193580
JACK
MM18
MM05
09193580
JACK
MM18
MM18
09193580
JACK
MM18
O209
09193580
JACK
O209
MM05
09193580
JACK
O209
MM18
09193580
JACK
O209
O209
09194040
PAUL
O208
O208
09194040
PAUL
O208
O201
09194040
PAUL
O208
FI13
09194040
PAUL
O201
O208
09194040
PAUL
O201
O201
09194040
PAUL
O201
FI13
09194040
PAUL
FI13
O208
09194040
PAUL
FI13
O201
09194040
PAUL
FI13
FI13
What'sthe best way to build this?
Thanks so much,
Erick
The best way to do this is to NOT make the second table a permanent table because it will need to be maintained. As Jack suggested, a self-join using CROSS JOIN would do it for you and not need to be maintained.
I'd also suggest that, just like a kid's "times table", you only really need about half the data in the form of a "Triangular Join" instead of a fully squared-up Cartesian Product because the set known to contain A,B will be equal to the set containing B,A. Instead of having x^2 rows, you'd end up with only ((x^2)+x)/2 rows.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 17, 2018 at 8:49 am
I don't consider this a CROSS JOIN. It's an INNER JOIN on GPID and Name. A CROSS JOIN would produce 36 rows, whereas this only has 18.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 17, 2018 at 9:22 am
drew.allen - Monday, September 17, 2018 8:49 AMI don't consider this a CROSS JOIN. It's an INNER JOIN on GPID and Name. A CROSS JOIN would produce 36 rows, whereas this only has 18.Drew
Looking with a fresh set of eye, Ia gree. i thought (nitially) it was something like:SELECT {Columns}
FROM [Table] T1
CROSS JOIN [Table] T2
WHERE T1.[Column] IN ('a','b','c');
I note now, however, that it's not. I can't, however, see any rhyme or reason for which functions are returned in the Function 1 and 2.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 17, 2018 at 9:30 am
Thom A - Monday, September 17, 2018 9:22 AMdrew.allen - Monday, September 17, 2018 8:49 AMI don't consider this a CROSS JOIN. It's an INNER JOIN on GPID and Name. A CROSS JOIN would produce 36 rows, whereas this only has 18.Drew
Looking with a fresh set of eye, Ia gree. i thought (nitially) it was something like:
SELECT {Columns}
FROM [Table] T1
CROSS JOIN [Table] T2
WHERE T1.[Column] IN ('a','b','c');
I note now, however, that it's not. I can't, however, see any rhyme or reason for which functions are returned in the Function 1 and 2.
It's all possible combinations of functions for the respective GPID and Name.
SELECT T1.GPID, T1.Name, T1.Functions, T2.Functions
FROM YourTable T1
INNER JOIN YourTable T2
ON T1.GPID = T2.GPID
AND T1.Name = T2.Name
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 22, 2018 at 6:56 pm
Hello Guys,
Thanks so much for the help.
I use every answers here and I got the solution.
Thanks
Erick
September 23, 2018 at 8:28 am
drew.allen - Monday, September 17, 2018 8:49 AMI don't consider this a CROSS JOIN. It's an INNER JOIN on GPID and Name. A CROSS JOIN would produce 36 rows, whereas this only has 18.Drew
It's true that the words CROSS JOIN aren't used but the end result would be a CROSS JOIN if there weren't criteria to limit it to "only" 36 rows as in a "Triangular Join", which is still a form of CROSS JOIN. Heh... the term CROSS JOIN is easier to say than something like "Constrained Many-to-Many Join" or some such and is a bit more clear for people who don't understand what a "Triangular Join" is, regardless of the syntax used to get there.
I do agree, however, that a lot of people might go looking for the words "CROSS JOIN" instead of looking for the "implicit" cross join formed by the inner join.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply