# datatool group by?

I try to display the results of a survey where students of 2 different classes could choose their favorite subject. In the result table I find Surname, Name, ClassID, Subject. The data in the file ff14.txt I'm using looks like this:

ClassID; Schueler_Name; Schueler_Vorname; Subject
5a; Stiner; Markus; Big Band
3c; Meier; Stefan; Latein
5b; Muggli; Elena; Instrument
3b; Strang; Flurin; Band
3b; Nomer; Ulrike; Big Band


Now I'd like to order the results by class and then display all the students in this class with their chosen favorite subject.

I've done something similar before using nlatexdb, where I used a nested construction. However, when I try to apply the same code to datatool I get a very different result. In the PDF the class is not grouped but displayed 27 times (as many as there are students in the class). So the resulting PDF consists of 27 tables each containing 27 students. But what I would like to have is 2 tables (there are 2 classes) with all the student in the corresponding class.

My MWE:

\documentclass[12pt]{article}

\usepackage{babel, fixltx2e}
\usepackage{xltxtra}
\usepackage{xunicode}
\usepackage{float}
\usepackage{datatool}

\DTLsetseparator{;}

\begin{document}

\DTLforeach{anmeldungen}{\class=ClassID}{

\subsection*{Klasse \class}

\begin{tabular}{ll} \hline
\textbf{Name} & \textbf{Vorname}
\DTLforeach{anmeldungen}{\fn=Schueler_Vorname, \ln=Schueler_Name}{\DTLiffirstrow{\\ \hline}{\\  }
\ln & \fn   }  \\ \hline \hline
\end{tabular}

\begin{flushright}
\DTLsavelastrowcount{\n}
\n\ Anmeldungen.
\end{flushright}

}

• You are iterating through the db in a nested manner: foreach entry {build table with each entry} so you will get n tables with n rows. – Peter Grill Apr 25 '14 at 19:50

The example below first uses Obtain all different values of a specific column in a datatool database to extract only the unique ClassIDs. Then the list of unique entries are processed using etoolbox, collecting the table rows that match ClassID before setting it in a tabular.

\documentclass{article}

\usepackage{filecontents}
\begin{filecontents*}{ff14.txt}
ClassID; Schueler_Name; Schueler_Vorname; Subject
5a; Stiner; Markus; Big Band
3c; Meier; Stefan; Latein
5b; Muggli; Elena; Instrument
3b; Strang; Flurin; Band
3b; Nomer; Ulrike; Big Band
\end{filecontents*}

\usepackage{datatool, etoolbox}

\DTLsetseparator{;}

\begin{document}

% Taken from https://tex.stackexchange.com/q/108712/5764
\newcommand*{\uniqueclass}{}
\DTLforeach*{anmeldungen}{\Class=ClassID}{%
\expandafter\DTLifinlist\expandafter{\Class}{\uniqueclass}%
{}% do nothing, already in list
\ifdefempty{\uniqueclass}%
{\let\uniqueclass\Class}% first element of list
{% append to list
\eappto\uniqueclass{,\Class}%
}%
}%
}

\newcommand{\nextrow}{}
\renewcommand{\do}[1]{%
\subsection*{Klasse #1}
\renewcommand{\nextrow}{}
\begin{tabular}{ll}
\hline
\textbf{Name} & \textbf{Vorname} \\ \hline
\DTLforeach{anmeldungen}
{\class=ClassID, \fn=Schueler_Vorname, \ln=Schueler_Name}
{\ifnum\pdfstrcmp{#1}{\class}=0
\eappto\nextrow{\ln \noexpand& \fn \noexpand\\}% Collect rows
\fi}%
\nextrow % Print table rows
\hline \hline
\end{tabular}
}

\expandafter\docsvlist\expandafter{\uniqueclass}

\end{document}