1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73
| cn.Open();
SqlDataAdapter da = new SqlDataAdapter(@"DECLARE @MATIERE nvarchar(max)
SELECT @MATIERE =
STUFF(
(
select distinct ',[' + LIBELLE_MATIERE + ']'
from MATIERE where CODE_MODULE =11
for xml path('')
),
1,1,'')
DECLARE @SQL nvarchar(max)
SELECT @SQL = N'
select
*
from (
SELECT ETUDIANT.NUM_INSCRIPTION, ETUDIANT.NOM_PRENOM_ETUDIANT_ARABE,EXAMEN.CODE,
EXAMEN.NOTE,
MATIERE.LIBELLE_MATIERE
FROM ETUDIANT INNER JOIN
EXAMEN ON ETUDIANT.NUM_INSCRIPTION = EXAMEN.NUM_INSCRIPTION INNER JOIN
CAPITALISE_MODULE ON
ETUDIANT.NUM_INSCRIPTION = CAPITALISE_MODULE.NUM_INSCRIPTION INNER JOIN
MATIERE ON EXAMEN.CODE_MATIERE = MATIERE.CODE_MATIERE INNER JOIN
MODULE ON CAPITALISE_MODULE.CODE_MODULE = MODULE.CODE_MODULE
AND MATIERE.CODE_MODULE = MODULE.CODE_MODULE
WHERE (CAPITALISE_MODULE.CODE_MODULE = 11) AND
(CAPITALISE_MODULE.CODE_TYPE_RELATION_MODULE = 1)
) Data
PIVOT (
SUM(NOTE)
FOR LIBELLE_MATIERE
IN (
' + @MATIERE + '
)
) PivotTable
'
exec sp_executesql @SQL
", cn);
DataTable dt = new DataTable();
da.Fill(dt);
cn.Close();
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
string sep = "";
foreach (DataColumn dc in dt.Columns)
{
Response.Write(sep + dc.ColumnName);
sep = "\t";
}
Response.Write("\n");
int i;
foreach (DataRow dr in dt.Rows)
{
sep = "";
for (i = 0; i < dt.Columns.Count; i++)
{
Response.Write(sep + dr[i].ToString());
sep = "\t";
}
Response.Write("\n");
}
}
} |
Partager