CROSSTAB Structure

[SerializableAttribute]
public struct CROSSTAB

;WITH sample_data AS (SELECT sqx.BernoulliD_PPF(a.Value, 0.6) [Truth]
, sqx.BernoulliD_PPF((CASE WHEN b.ID = 3 THEN NULL ELSE b.Value END), 0.6) [Predicted]
FROM sqx.UTable(1000,0) a, sqx.UTable(1000,3) b
WHERE a.ID = b.ID)
SELECT ct.*
FROM (
    SELECT sqx.CROSSTAB([Truth], [Predicted]) CRT
    FROM sample_data
    ) a
CROSS APPLY sqx.ContingencyTable(a.CRT) ct
/*
TP                   FP                   FN                   TN                   NL
-------------------- -------------------- -------------------- -------------------- --------------------
344                  241                  253                  161                  1*/

;WITH sample_data AS (SELECT sqx.BernoulliD_PPF(a.Value, 0.6) Observed
, sqx.BernoulliD_PPF(b.Value, 0.6) Predicted
FROM sqx.UTable(1000,0) a, sqx.UTable(1000,2) b
WHERE a.ID = b.ID)
SELECT ct.*
FROM sqx.ContingencyTable((SELECT sqx.CROSSTAB(Observed, Predicted) FROM sample_data)) ct
/*
TP                   FP                   FN                   TN                   NL
-------------------- -------------------- -------------------- -------------------- --------------------
381                  234                  217                  168                  0*/

;WITH sample_data AS (SELECT sqx.BernoulliD_PPF(a.Value, 0.6) Observed
, sqx.BernoulliD_PPF(b.Value, 0.6) Predicted
FROM sqx.UTable(1000,1) a, sqx.UTable(1000,3) b
WHERE a.ID = b.ID)
SELECT cmt.*
FROM sqx.ConfusionMatrixTable((SELECT sqx.CROSSTAB(Observed, Predicted) FROM sample_data)) cmt

;WITH sample_data AS (SELECT a.Value [MyGroup], sqx.BernoulliD_PPF(b.Value, 0.6) [Truth]
, sqx.UniformD_PPF(c.Value, 1, 10) [SomeRank]
FROM sqx.NTable(1,5) a OUTER APPLY sqx.UTable((CASE WHEN a.Value = 3 THEN 0 ELSE a.Value * 10 END),0) b
LEFT JOIN sqx.UTable(50,2) c ON b.ID = c.ID)
SELECT [MyGroup], ct.*
FROM (
    SELECT [MyGroup], sqx.CROSSTAB([Truth], (CASE WHEN [SomeRank] <= 3 THEN 1 ELSE 0 END)) CRT
    FROM sample_data
    GROUP BY [MyGroup]
    ) a
OUTER APPLY sqx.ContingencyTable(a.CRT) ct
ORDER BY [MyGroup]
GO
/*
MyGroup     TP                   FP                   FN                   TN                   NL
----------- -------------------- -------------------- -------------------- -------------------- --------------------
1           2                    2                    6                    0                    0
2           5                    3                    10                   2                    0
3           NULL                 NULL                 NULL                 NULL                 NULL
4           9                    4                    19                   8                    0
5           10                   5                    23                   12                   0*/

;WITH sample_data AS (SELECT a.Value [MyGroup], sqx.BernoulliD_PPF(b.Value, 0.6) [Truth]
, sqx.UniformD_PPF(c.Value, 1, 10) [SomeRank]
FROM sqx.NTable(1,5) a OUTER APPLY sqx.UTable((CASE WHEN a.Value = 3 THEN 0 ELSE a.Value * 10 END),0) b
LEFT JOIN sqx.UTable(50,2) c ON b.ID = c.ID)
SELECT [MyGroup], ct.*
FROM (
    SELECT [MyGroup], sqx.CROSSTAB([Truth], (CASE WHEN [SomeRank] <= 3 THEN 1 ELSE 0 END)) CRT
    FROM sample_data
    GROUP BY [MyGroup]
    ) a
CROSS APPLY sqx.ContingencyTable(a.CRT) ct
ORDER BY [MyGroup]
GO
/*
MyGroup     TP                   FP                   FN                   TN                   NL
----------- -------------------- -------------------- -------------------- -------------------- --------------------
1           2                    2                    6                    0                    0
2           5                    3                    10                   2                    0
4           9                    4                    19                   8                    0
5           10                   5                    23                   12                   0*/