SQLite and Transform

Question

SQLite and Transform

madref

SSC Veteran

Points: 230
More actions
September 12, 2016 at 7:25 am

#77307

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

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

madref SSC Veteran Points: 230 More actions · Answer 2

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