Dołączenie danych (wiersze jako kolumna) z tej samej tabeli (JOIN ?)

Mam w excelu tabelę tego typu.

Docelowo chciałbym uzyskać taką tabelę:

Select Owoc, Dok, Odbiorca, sum(Ilość) as Ilość, sum(Masa) as Masa, sum(Wartość) as Wartość From [arkusz1$] where Dok in ('FV/F','FD2/') group by Owoc, Dok, Odbiorca

Powyższe polecenie generuje mi tabelę do kolumny Wartość. Nie potrafię natomiast dodać kolumny Wartość_KWPZ.

A to pole Wartość_KWPZ to jakaś wartość wyliczalna, funkcja którą się da zapisać? I dlaczego raz występuje a raz wiersz jest pusty?

Zobacz tabelę źródłową. Dokument KWPZ występuje 3 razy.
2x dla owocu truskawka i odbiorcy Franek = 87+87= 174 zł
1x dla owocu truskawka i odbiorcy Kazik = 44 zł

Nie potrzebujesz joina:

Select 
	Owoc
	, Dok
	, Odbiorca
	, sum(case when Dok in ('FV/F','FD2/') then Ilość else 0 end) as Ilość
	, sum(case when Dok in ('FV/F','FD2/') then Masa else 0 end) as Masa
	, sum(case when Dok in ('FV/F','FD2/') then Wartość else 0 end) as Wartość 
	, sum(case when Dok in ('KWPZ') then Wartość else 0 end) as Wartość_kwpz 
From 
	[arkusz1$] 
where 
	Dok in ('FV/F','FD2/','KWPZ') 
group by 
	Owoc
	, Dok
	, Odbiorca

Przepraszam, że nie wyraziłem się jasno na początku. Działam pod VBA (Excel) i konstrukcja case when nie jest obsługiwana.

Piszesz w bazach danych, oznaczasz jako mssql, nawet w pierwszym poście dajesz sql-a którego używasz, to gdzie te dane są?

Dane mam w Excel'u, muszę obrobić w VBA.
Zamieściłem w bazach, ponieważ mam problem z zapytaniem SQL.

Kod VBA:

Sub UTA()
Dim ws As Worksheet
Dim conn As ADODB.Connection
set conn = New ADODB.Connection

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & ActiveWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=YES"";"
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim qSelectInvDetails As String

qSelectInvDetails = "Select Owoc, Dok, Odbiorca, sum(Ilość) as Ilość, sum(Masa) as Masa, sum(Wartość) as Wartość From [arkusz1$] where Dok in ('FV/F','FD2/') group by Owoc, Dok, Odbiorca"

rs.Open qSelectInvDetails, conn
ws.Range("P2").CopyFromRecordset rs

rs.Close
conn.Close

Set rs = Nothing
Set conn = Nothing

End Sub

to zamien case na iif iif(Dok = "FV/F" or Dok = "FD2/",Wartość,0)

OK. chyba mam coś blisko, nie jest to piękne rozwiązanie ale wygląa żę działa.
Tylko usuń z kwerendy Dok. Bo robi zamieszanie.
u mnie działa to:

sql = "Select Owoc, Odbiorca, sum(Ilosc) as Ilosc, sum(Masa) as Masa, sum(Wartosc) as Wartosc, sum(WartoscKWPZ) as WartoscKWPZ From (Select Owoc, Odbiorca, sum(Ilosc) as Ilosc, sum(Masa) as Masa, sum(Wartosc) as Wartosc, sum(0) as WartoscKWPZ From [Arkusz1$] where Dok in ('FV/F','FD2/') group by Owoc, Odbiorca "
sql = sql & "UNION ALL "
sql = sql & "Select Owoc, Odbiorca, sum(0) as Ilosc, sum(0) as Masa, sum(0) as Wartosc, sum(Wartosc) as WartoscKWPZ From [Arkusz1$] where Dok in ('KWPZ') group by Owoc, Odbiorca) group by Owoc, Odbiorca"

a wynik (bez nagłóków) mam taki:
BORÓWKA;Stefan;310;245;51;0;
MALINA;Franek;864;864;201;0;
MALINA;Kazik;768;768;190;0;
TRUSKAWKA;Czesław;30;15;3;0;
TRUSKAWKA;Franek;1152;1152;268;174;
TRUSKAWKA;Kazik;384;384;95;44;
TRUSKAWKA;Lutek;3855;3855;134;0;
TRUSKAWKA;Piotr;540;480;103;0;

Ten numer dokumentu o któym wspominam robi zamieszanie przy agregacji.

ps
usunąłem Polskei znaki zarówno z arkusza jak i z kwerendy więc u siebie musisz poprawić.

Panczo napisał(a):

to zamien case na iif iif(Dok = "FV/F" or Dok = "FD2/",Wartość,0)

Zamieniłem kod na:

Sql = "Select Owoc, Dok, Odbiorca"
Sql = Sql & ", sum(iif(Dok = 'FV/F' or Dok = 'FD2/',Ilosc,0)) as Ilosc "
Sql = Sql & ", sum(iif(Dok = 'FV/F' or Dok = 'FD2/',Masa,0)) as Masa "
Sql = Sql & ", sum(iif(Dok = 'FV/F' or Dok = 'FD2/',Wartosc,0)) as Wartosc "
Sql = Sql & ", sum(iif(Dok = 'KWPZ',Wartosc,0)) as KWPZ "
Sql = Sql & "From [arkusz1$] "
Sql = Sql & "where Dok in ('FV/F','FD2/','KWPZ') Group by Owoc, Dok, Odbiorca"

ale dostaję taki wynik (te wartości powinny tam gdzie obramowane na czerwono pola):

TytusRomek napisał(a):

OK. chyba mam coś blisko, nie jest to piękne rozwiązanie ale wygląa żę działa.
Tylko usuń z kwerendy Dok. Bo robi zamieszanie.
Ten numer dokumentu o któym wspominam robi zamieszanie przy agregacji.

Jest faktycznie blisko, z tym, że brak numeru Dok.

Numer Dok przy agregacji jest niestety konieczny, świadczy o typie wysyłki (krajowa czy zagraniczna). Muszę uzyskać tabelę wyjściową jak w pierwszym poście.

zielinskit napisał(a):

TytusRomek napisał(a):

OK. chyba mam coś blisko, nie jest to piękne rozwiązanie ale wygląa żę działa.
Tylko usuń z kwerendy Dok. Bo robi zamieszanie.
Ten numer dokumentu o któym wspominam robi zamieszanie przy agregacji.

Jest faktycznie blisko, z tym, że brak numeru Dok.

Numer Dok przy agregacji jest niestety konieczny, świadczy o typie wysyłki (krajowa czy zagraniczna). Muszę uzyskać tabelę wyjściową jak w pierwszym poście.

Rozumiem. Przy całej mojej wiedzy nie da się tego zrobić za pomocą kwerendy sql - sam już widzisz co powoduje numer Dok użyty w agregacji.
Jeśli używasz VBA, to może spróbuj dokleić te dane (kolumnę KPWZ) pętlą z jakiejś tablicy i sumy jeżeli.

zielinskit napisał(a):

TytusRomek napisał(a):

OK. chyba mam coś blisko, nie jest to piękne rozwiązanie ale wygląa żę działa.
Tylko usuń z kwerendy Dok. Bo robi zamieszanie.
Ten numer dokumentu o któym wspominam robi zamieszanie przy agregacji.

Jest faktycznie blisko, z tym, że brak numeru Dok.

Numer Dok przy agregacji jest niestety konieczny, świadczy o typie wysyłki (krajowa czy zagraniczna). Muszę uzyskać tabelę wyjściową jak w pierwszym poście.

Jeszcze jedna myśl. Patrząc na oryginalną tabelę i tę którą chcesz otrzymać, to też nie jesteś w stanie zrobić tego sam.
Jeśli dla Truskawki/Franka będziesz miał więcej niż jeden typ dokumentu- czyli kraj czy zagranica- to nie ma logiki w tych danych która mogłaby pozowolić przyporządkować
agragację odpowiednio.
Coś istotnego pominąłeś bo według Twojego przykładu u Franka jest tylko jeden rodzaj wysyłki.

TytusRomek napisał(a):

Jeszcze jedna myśl. Patrząc na oryginalną tabelę i tę którą chcesz otrzymać, to też nie jesteś w stanie zrobić tego sam.
Jeśli dla Truskawki/Franka będziesz miał więcej niż jeden typ dokumentu- czyli kraj czy zagranica- to nie ma logiki w tych danych która mogłaby pozowolić przyporządkować
agragację odpowiednio.
Coś istotnego pominąłeś bo według Twojego przykładu u Franka jest tylko jeden rodzaj wysyłki.

Tak wiem, na szczęście nie ma takiej możliwości. Tabela jest czysto teoretyczna.
W praktyce przykładowy Franek to odbiorca krajowy i będzie miał tylko jeden typ dokumentu - FV/F, natomiast Kazik to odbiorca zagraniczny (typ dokumentu FD2/).

zielinskit napisał(a):

TytusRomek napisał(a):

Jeszcze jedna myśl. Patrząc na oryginalną tabelę i tę którą chcesz otrzymać, to też nie jesteś w stanie zrobić tego sam.
Jeśli dla Truskawki/Franka będziesz miał więcej niż jeden typ dokumentu- czyli kraj czy zagranica- to nie ma logiki w tych danych która mogłaby pozowolić przyporządkować
agragację odpowiednio.
Coś istotnego pominąłeś bo według Twojego przykładu u Franka jest tylko jeden rodzaj wysyłki.

Tak wiem, na szczęście nie ma takiej możliwości. Tabela jest czysto teoretyczna.
W praktyce przykładowy Franek to odbiorca krajowy i będzie miał tylko jeden typ dokumentu - FV/F, natomiast Kazik to odbiorca zagraniczny (typ dokumentu FD2/).

W takim razie Dok nie jest Ci potrzebny do niczego w samej kwerendzie.

Dok, faktycznie jest potrzebny, ale można joinem:

SELECT
	m.owoc
	,m.dok
	,m.odbiorca
	,m.ilość
	,m.Masa
	,m.Wartość
	,kwpz.wartość_kwpz
FROM
	(Select 
		Owoc
		, Dok
		, Odbiorca
		, sum(ilość) as Ilość
		, sum(masa) as Masa
		, sum(wartość) as Wartość 
	From 
		[arkusz1$] 
	where 
		Dok in ('FV/F','FD2/') 
	group by 
		Owoc
		, Dok
		, Odbiorca) as m
	LEFT JOIN (Select 
				Owoc
				, Odbiorca
				, sum(Wartość) as Wartość_kwpz 
			From 
				[arkusz1$] 
			where 
				Dok = 'KWPZ'
			group by 
				Owoc
				, Odbiorca) as kwpz on kwpz.owoc=m.owoc and kwpz.odbiorca = m.odbiorca

Panczo napisał(a):

Dok, faktycznie jest potrzebny, ale można joinem:

to ja będę się upierał - dlaczego i gdzie jest potrzebny? też nie korzystasz w join z niego. I wiesz, że z Dok logika zapytania nie do zrobienia.
jeśli Dok ma odposiwednik w polu Odbiorca to z teorii nie zachowujemy postaci normalnych.

Swoją drogą, podoba mi się Twój skrypt, ale działa tak jak mój tylko dlatego że nie używasz Dok. Dwie różne wartości Dok dla Franka/Truskawki rozsypią te rozwiązania.

ps chyba (nie wykluczam) że czegoś nie wiem?

Panczo napisał(a):

Dok, faktycznie jest potrzebny, ale można joinem:

Kopiuj

SELECT
	m.owoc
	,m.dok
	,m.odbiorca
	,m.ilość
	,m.Masa
	,m.Wartość
	,kwpz.wartość_kwpz
FROM
	(Select 
		Owoc
		, Dok
		, Odbiorca
		, sum(ilość) as Ilość
		, sum(masa) as Masa
		, sum(wartość) as Wartość 
	From 
		[arkusz1$] 
	where 
		Dok in ('FV/F','FD2/') 
	group by 
		Owoc
		, Dok
		, Odbiorca) as m
	LEFT JOIN (Select 
				Owoc
				, Odbiorca
				, sum(Wartość) as Wartość_kwpz 
			From 
				[arkusz1$] 
			where 
				Dok = 'KWPZ'
			group by 
				Owoc
				, Odbiorca) as kwpz on kwpz.owoc=m.owoc and kwpz.odbiorca = m.odbiorca

Działa perfekcyjnie. O to chodziło. Wielkie dzięki za pomoc.