[SOLVED] SQLite and Transform
lazarus / 16-Databases

----------------------------------------------------------------------
madref · 2016-09-11 19:36

SQLite does not know TRANSFORM as in MS Access.
How can i make the next query/view in SQLite?
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i TRANSFORM
FIRST(IIf(IsNull([srt_afkorting]),"","x")) AS AfkSELECT
qry_Overzicht_Leden.Lid_ID, qry_Overzicht_Leden.Lid_Achternaam AS
Achternaam, qry_Overzicht_Leden.Lid_Tussenvoegsel AS Tussen,
qry_Overzicht_Leden.Lid_Voornaam AS Voornaam,
qry_Overzicht_Leden.Lid_Initialen AS Initialen,
tbl_Administratie.Adm_Sei_ID AS Sei_IDFROM tbl_SoortLid INNER JOIN
(tbl_Teams INNER JOIN (qry_Overzicht_Leden RIGHT JOIN
tbl_Administratie ON qry_Overzicht_Leden.Lid_ID =
tbl_Administratie.Adm_Lid_ID) ON tbl_Teams.Team_ID =
tbl_Administratie.Adm_Team) ON tbl_SoortLid.Srt_ID =
tbl_Administratie.Adm_SoortLidWHERE (((qry_Overzicht_Leden.Lid_ID) IS
NOT NULL) AND ((tbl_SoortLid.Srt_Afkorting)<>"D1" AND
(tbl_SoortLid.Srt_Afkorting)<>"D2" AND
(tbl_SoortLid.Srt_Afkorting)<>"FD"))GROUP BY
qry_Overzicht_Leden.Lid_ID, qry_Overzicht_Leden.Lid_Achternaam,
qry_Overzicht_Leden.Lid_Tussenvoegsel,
qry_Overzicht_Leden.Lid_Voornaam, qry_Overzicht_Leden.Lid_Initialen,
tbl_Administratie.Adm_Sei_IDORDER BY
qry_Overzicht_Leden.Lid_Achternaam,
qry_Overzicht_Leden.Lid_VoornaamPIVOT tbl_SoortLid.Srt_Afkorting;

----------------------------------------------------------------------
Thaddy · 2016-09-12 06:04

You can use a temp table and lots of left joins.
There a simple example here:
http://stackoverflow.com/questions/1237068/pivot-in-sqlite

----------------------------------------------------------------------
madref · 2016-09-12 14:18

i am starting my database anew.
So is there any sql that uses pivot besides MS Access that works with
Lazarus on a Mac?

----------------------------------------------------------------------
LacaK · 2016-09-12 16:40

MS SQL Server
Of course server must run on other machine, byt client can be Mac.

----------------------------------------------------------------------
madref · 2016-09-12 17:36

Damn i really need help with this.I am so limited in my knowledge of
sql and sqlite.

----------------------------------------------------------------------
madref · 2016-09-17 22:20

I finally got it working with some excellent help
This is the result:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE VIEW
qry_Seizoen_Overzicht_Leden AS SELECT Lid_ID, Lid_Achternaam AS
Achternaam, Lid_Tussenvoegsel AS Tussen, Lid_Voornaam AS Voornaam,
Lid_Initialen AS Initialen, Adm_Sei_ID AS Sei_ID, CASE WHEN SUM(CASE
WHEN Srt_Afkorting="BL" THEN 1 ELSE 0 END) > 0 THEN "X" ELSE NULL  END
AS BL, CASE WHEN SUM(CASE WHEN Srt_Afkorting="CO" THEN 1 ELSE 0 END) >
0 THEN "X" ELSE NULL  END AS CO, CASE WHEN SUM(CASE WHEN
Srt_Afkorting="CS" THEN 1 ELSE 0 END) > 0 THEN "X" ELSE NULL  END AS
CS, CASE WHEN SUM(CASE WHEN Srt_Afkorting="JS" THEN 1 ELSE 0 END) > 0
THEN "X" ELSE NULL  END AS JS, CASE WHEN SUM(CASE WHEN
Srt_Afkorting="LA" THEN 1 ELSE 0 END) > 0 THEN "X" ELSE NULL  END AS
LA, CASE WHEN SUM(CASE WHEN Srt_Afkorting="SC" THEN 1 ELSE 0 END) > 0
THEN "X" ELSE NULL  END AS SC, CASE WHEN SUM(CASE WHEN
Srt_Afkorting="SP" THEN 1 ELSE 0 END) > 0 THEN "X" ELSE NULL  END AS
SP, CASE WHEN SUM(CASE WHEN Srt_Afkorting="TB" THEN 1 ELSE 0 END) > 0
THEN "X" ELSE NULL  END AS TB, CASE WHEN SUM(CASE WHEN
Srt_Afkorting="TR" THEN 1 ELSE 0 END) > 0 THEN "X" ELSE NULL  END AS
TR, CASE WHEN SUM(CASE WHEN Srt_Afkorting="WS" THEN 1 ELSE 0 END) > 0
THEN "X" ELSE NULL  END AS WS, CASE WHEN SUM(CASE WHEN
Srt_Afkorting="ZT" THEN 1 ELSE 0 END) > 0 THEN "X" ELSE NULL  END AS
ZT, Adm_Team FROM qry_Overzicht_Leden LEFT JOIN tbl_Administratie ON
tbl_Administratie.Adm_Lid_ID=qry_Overzicht_Leden.Lid_ID LEFT JOIN
tbl_SoortLid ON tbl_SoortLid.Srt_ID = tbl_Administratie.Adm_SoortLid
GROUP BY 1,2,3,4,5,6 ORDER BY Lid_Achternaam, Lid_Voornaam, Adm_Sei_ID