Create 2X2 Matrix result table using sql server 2012 (WITH ROLLUP)

Question

0.00/5 (No votes)

See more:

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.

SQL

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.

Posted 11-Dec-12 17:37pm

snamyna

Updated 12-Dec-12 16:33pm

v3

Add a Solution

2 solutions

Solution 1

Tutorial: Creating a Basic Matrix Report[^]
Making a 2x2 matrix[^]

Posted 11-Dec-12 17:43pm

Krunal Rohit

Updated 11-Dec-12 19:21pm

v2

Comments

snamyna 12-Dec-12 3:25am

I dont understand :( If possible I want to do the query statement inside sql server that directly produce the result, not in vb.. What method should I use instead of With Rollup?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

snamyna · Accepted Answer · 2012-12-12T20:04:00

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