September 12, 2016 at 7:25 am
SQLite does not know TRANSFORM/PIVOT as in MS Access.
How can i make the next query/view in SQLite?
TRANSFORM First(IIf(IsNull([srt_afkorting]),"","x")) AS Afk
SELECT 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_ID
FROM 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_SoortLid
WHERE (((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_ID
ORDER BY qry_Overzicht_Leden.Lid_Achternaam, qry_Overzicht_Leden.Lid_Voornaam
PIVOT tbl_SoortLid.Srt_Afkorting;
I really do need help with this one or a damn good example which i can use to create my query/view
September 14, 2016 at 4:24 pm
madref (9/12/2016)
SQLite does not know TRANSFORM/PIVOT as in MS Access.How can i make the next query/view in SQLite?
TRANSFORM First(IIf(IsNull([srt_afkorting]),"","x")) AS Afk
SELECT 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_ID
FROM 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_SoortLid
WHERE (((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_ID
ORDER BY qry_Overzicht_Leden.Lid_Achternaam, qry_Overzicht_Leden.Lid_Voornaam
PIVOT tbl_SoortLid.Srt_Afkorting;I really do need help with this one or a damn good example which i can use to create my query/view
Probably not a lot of SQLite users on a SQL Server forum. Try googling SQLite and Pivot. Search on those two and examples will come up for you to go through.
Sue
Sue
September 14, 2016 at 4:35 pm
I finally got it to work with some help.
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
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy