使用sql server 2012创建2X2 Matrix结果表（WITH ROLLUP） [英] Create 2X2 Matrix result table using sql server 2012 (WITH ROLLUP)
问题描述
在此之前我使用了以下代码在sql语句中使用WITH ROLLUP创建一个子总数和总计的表。 />
SELECT isnull（ convert （ VARCHAR ，MC_TYPE_T），' ' ） AS MC_TYPE_T，count（MC_TYPE_T） AS CT
FROM Q_MCLOC WHERE MC_TYPE_T 不 NULL GROUP BY MC_TYPE_T WITH ROLLUP
如何使用相同的方法生成2X2矩阵表，结果如下（小计显示在每行和每列。总表显示在桌子的最右下方是17）：
Line | A | B | C | D |总计
---------------------------
1A | 1 | 3 | 0 | 2 | 6
2A | 2 | 2 | 1 | 2 | 7
3A | 0 | 1 | 1 | 2 | 4
总计| 3 | 6 | 2 | 6 | 17
更新：
我尝试过使用vb.net生成动态数据表，如上例所示。我使用for循环，结果如下：
area - YM L-10YM YH SS4 HSST L-10YH NAL
1A 21
1A 5
1B 17
2A 15
2A 1
2A 2
2A 10
2B 16
3A 3
3A 21
3A 6
3A 14
3B 17
3B 3
3B 4
4A 2
4A 19
4A 22
4B 1
4B 15
4B 11
5A 11
5A 33
5B 27
6A 2
6A 14
6A 27
6B 27
6B 1
7A 11
7A 32
7B 2
7B 3
7B 22
8A 11
8A 14
8A 6
8B 10
8B 13
如何根据特定列将值更新为现有行？
例如对于区域1A，我想在第一个ro处插入值5但是在YM列中。
教程：创建基本矩阵报告 [ ^ ]
制作2x2矩阵 [ ^ ]
我通过代码隐藏而不是使用SQL语句。这是我的代码：
函数GetTable（）As DataTable
''创建新的DataTable实例。
Dim table As New DataTable
table.Columns.Add（AREA）
Dim sqlHS As String =SELECT DISTINCT HS_TYPE_T FROM Q_MCLOC WHERE HS_TYPE_T IS NOT NULL
Dim dtHS As DataTable = DBLayer.Util.Data.MsSql.DBFunction.BuildDataTable（sqlHS，connEPJSetting）
For each hs As DataRow in dtHS.Rows
If table.Columns（hs （HS_TYPE_T））Nothing然后
table.Columns.Add（hs（HS_TYPE_T），GetType（String））
结束如果
下一个
table.Columns。添加（TOTAL）
返回表
结束函数
私有子测试（）
Dim dt As DataTable = GetTable（）
Dim sqlBlock As String =SELECT DISTINCT LOCAREA_T from Q_MCLOC WHERE LOCAREA_T IS NOT NULL
Dim dtBlock As DataTable = DBLayer.Util.Data.MsSql.DBFunction.BuildDataTable（sqlBlock，connEPJSetting）
For Each areas作为DataRow在dtBlock.Rows
Dim a As DataRow = dt.NewRow
Dim count，total As Integer
total = 0
a（AREA）= area（LOCAREA_T）
For each s As DataColumn在dt.Columns中
如果s.ColumnName（）<> AREA然后
Dim sqlCount As String =SELECT COUNT（HS_TYPE_T）FROM Q_MCLOC WHERE LOCAREA_T =''&区域（LOCAREA_T）& '和'HS_TYPE_T =''& s.ColumnName（）& msgstr$'$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ b''count + = count
total = total + count
End if
Next
a（TOTAL）=总
dt.Rows。添加（a）
下一个
Dim c As DataRow = dt.NewRow
For each col As DataColumn in dt.Columns
Dim rowtotal As Integer = 0
如果col.ColumnName（）<> AREA然后
For Each row As DataRow In dt.Rows
Dim b As String = row（col.ColumnName（））
rowtotal + = CType（row（col） .ColumnName（）），Integer）
下一个
c（col.ColumnName）= rowtotal
结束如果
下一个
dt.Rows.Add（c）
GridView1.DataSource = dt
GridView1.DataBind（）
，这是Gridview的结果。
AREA - HSST L-10YH L-10YM NAL SS4 YH YM TOTAL
1A 21 0 0 0 0 0 0 5 26
1B 17 0 0 0 0 0 0 0 17
2A 15 0 0 1 0 0 2 10 28
2B 16 0 0 0 0 0 0 0 16
3A 3 0 0 0 0 21 6 14 44
3B 0 17 0 0 0 0 3 4 24
4A 0 0 2 0 0 0 19 22 43
4B 0 0 0 1 0 0 15 11 27
5A 0 0 0 0 0 0 11 33 44
5B 0 0 0 0 0 0 27 0 27
6A 0 0 0 2 0 0 14 27 43
6B 0 0 0 0 0 0 27 1 28
7A 0 0 0 0 0 0 11 32 43
7B 0 0 0 2 0 0 3 22 27
8A 0 0 0 0 11 0 14 6 31
8B 0 0 0 0 0 0 10 13 23
72 17 2 6 11 21 162 200 491
要使用代码生成此类结果，可能会消耗更多时间，因为需要使用for循环。如果有人知道更好的解决方案（最好使用SQL语句），请告诉我。谢谢
Hye all.
Before this I''ve used code below to create a table with sub total and total using WITH ROLLUP in the sql statement.
SELECT isnull(convert(VARCHAR,MC_TYPE_T),'') AS MC_TYPE_T, count(MC_TYPE_T) AS CT
FROM Q_MCLOC WHERE MC_TYPE_T Is Not NULL GROUP BY MC_TYPE_T WITH ROLLUP
How can I use the same method to produce 2X2 matrix table which results like below (Subtotal shown at each row and column. Overall total shown at the most down-right side of the table which is 17):
Line| A | B | C | D | Total
---------------------------
1A | 1 | 3 | 0 | 2 | 6
2A | 2 | 2 | 1 | 2 | 7
3A | 0 | 1 | 1 | 2 | 4
Total| 3 | 6 | 2 | 6 | 17
Updated:
I have tried using vb.net to generate dynamic datatable like example above. I use for loop and the result that I get was like below:
area - YM L-10YM YH SS4 HSST L-10YH NAL 1A 21 1A 5 1B 17 2A 15 2A 1 2A 2 2A 10 2B 16 3A 3 3A 21 3A 6 3A 14 3B 17 3B 3 3B 4 4A 2 4A 19 4A 22 4B 1 4B 15 4B 11 5A 11 5A 33 5B 27 6A 2 6A 14 6A 27 6B 27 6B 1 7A 11 7A 32 7B 2 7B 3 7B 22 8A 11 8A 14 8A 6 8B 10 8B 13
How can I update values into existing row according particular column?
For example for area 1A, i want to insert values 5 at first row but in column YM.
Tutorial: Creating a Basic Matrix Report[^]
Making a 2x2 matrix[^]
Instead of using SQL statement, I did it via code behind. This is my code:
Function GetTable() As DataTable '' Create new DataTable instance. Dim table As New DataTable table.Columns.Add("AREA") Dim sqlHS As String = "SELECT DISTINCT HS_TYPE_T FROM Q_MCLOC WHERE HS_TYPE_T IS NOT NULL" Dim dtHS As DataTable = DBLayer.Util.Data.MsSql.DBFunction.BuildDataTable(sqlHS, connEPJSetting) For Each hs As DataRow In dtHS.Rows If table.Columns(hs("HS_TYPE_T")) Is Nothing Then table.Columns.Add(hs("HS_TYPE_T"), GetType(String)) End If Next table.Columns.Add("TOTAL") Return table End Function Private Sub test() Dim dt As DataTable = GetTable() Dim sqlBlock As String = "SELECT DISTINCT LOCAREA_T FROM Q_MCLOC WHERE LOCAREA_T IS NOT NULL" Dim dtBlock As DataTable = DBLayer.Util.Data.MsSql.DBFunction.BuildDataTable(sqlBlock, connEPJSetting) For Each area As DataRow In dtBlock.Rows Dim a As DataRow = dt.NewRow Dim count, total As Integer total = 0 a("AREA") = area("LOCAREA_T") For Each s As DataColumn In dt.Columns If s.ColumnName() <> "AREA" Then Dim sqlCount As String = "SELECT COUNT(HS_TYPE_T) FROM Q_MCLOC WHERE LOCAREA_T = ''" & area("LOCAREA_T") & "'' AND HS_TYPE_T = ''" & s.ColumnName() & "''" count = DBLayer.Util.Data.MsSql.DBFunction.executeScalar(sqlCount, connEPJSetting) a(s.ColumnName()) = count ''count += count total = total + count End If Next a("TOTAL") = total dt.Rows.Add(a) Next Dim c As DataRow = dt.NewRow For Each col As DataColumn In dt.Columns Dim rowtotal As Integer = 0 If col.ColumnName() <> "AREA" Then For Each row As DataRow In dt.Rows Dim b As String = row(col.ColumnName()) rowtotal += CType(row(col.ColumnName()), Integer) Next c(col.ColumnName) = rowtotal End If Next dt.Rows.Add(c) GridView1.DataSource = dt GridView1.DataBind()
and here is the result in Gridview.
AREA - HSST L-10YH L-10YM NAL SS4 YH YM TOTAL 1A 21 0 0 0 0 0 0 5 26 1B 17 0 0 0 0 0 0 0 17 2A 15 0 0 1 0 0 2 10 28 2B 16 0 0 0 0 0 0 0 16 3A 3 0 0 0 0 21 6 14 44 3B 0 17 0 0 0 0 3 4 24 4A 0 0 2 0 0 0 19 22 43 4B 0 0 0 1 0 0 15 11 27 5A 0 0 0 0 0 0 11 33 44 5B 0 0 0 0 0 0 27 0 27 6A 0 0 0 2 0 0 14 27 43 6B 0 0 0 0 0 0 27 1 28 7A 0 0 0 0 0 0 11 32 43 7B 0 0 0 2 0 0 3 22 27 8A 0 0 0 0 11 0 14 6 31 8B 0 0 0 0 0 0 10 13 23 72 17 2 6 11 21 162 200 491
To produce such result using code behind, it might consume more time because need to use for loop. If anybody knows better solution (using SQL statement preferable), please let me know. Thank you
这篇关于使用sql server 2012创建2X2 Matrix结果表（WITH ROLLUP）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
