How to turn database table to below format?

[VAer]

Sorry that this is not really VBA question, I just use it inside VBA code.

Just making up a simple database table. The real database table is much more complex with many more fields.

Database: MS S-Q-L (I modified S-Q-L, since the term is blocked by system)

Column A-D: Database table structure (just making up a simple example).

StudentID is unique, which represents an individual.

Unknown: I don't know how many subjects are there.

Yellow area is desired output format, something like pivot table.

Note: It is possible that StudentID 111 took Math test in 2023 but not in 2024, in this case, cell J7 should be blank.
Note: It is also possible that studentID 112 does not take math test in either 2023 or 2024, while other students take math test. In this case, cell I8 and J8 should be blank.

Goal: I would like to identify large score change from previous year for each student's each subject. I want to pull data into excel sheet in yellow area format, so that I can write other part of VBA code.

I already know connection string to database. If I use below code to pull data from database table, the output data will be something like column A:D data. I know how to do this part of task with below VBA code.

Question: How can I modify TestScoreQuery so that output data will be something like yellow area?

Reference: https://www.w3schools.com/sql/sql_groupby.asp

Edit: As mentioned, it is just a simple sample. You can assume that there are many StudentID and many subjects, in real database table, there could be hundreds of subjects associated to each student, and there are multiple years too. if I use my code, excel sheet may not fit, since there are only 1,048,576 rows. I would like to have only one row for each StudentID, as yellow area output format.

Actually, I should add Where Year = 2003 or 2004 in below code TestScoreQuery, I have not figured out how to write it correctly, I should limit the code to two specific years, comparing current year to previous year. But this is not main question, the main question is how to write the code and make output as pivot table format.

Please Login or Register  to view this content.

SQL Groupby.jpg

SQL Output.xlsx

[bebo021999]

Are you OK if, I propose a VBA code solution?
If yes, try to attach a sample worksheet.

[VAer]

Are you OK if, I propose a VBA code solution?
If yes, try to attach a sample worksheet.

I prefer not. As mentioned, it is just a simple sample. You can assume that there are many StudentID and many subjects, in real database table, there could be hundreds of subjects associated to each student, and there are multiple years too. if I use my code, excel sheet may not fit, since there are only 1,048,576 rows. I would like to have only one row for each StudentID.

[bebo021999]

Why not? why 1 milion rows?
The VBA code will results exactly the yellow table, with unique student ID.

[VAer]

Why not? why 1 milion rows?
The VBA code will results exactly the yellow table, with unique student ID.

How? How to result database table into yellow table format? That is my question. I am sure that are less than one million StudentID.

With my sample code, it takes 12 rows for only two students. If using column A:D format (same as the format stored in database table), there could be more than one million of records in database.

[bebo021999]

Just give it a try.
Attach a small sample spreadsheet and let me try

[VAer]

Are you OK if, I propose a VBA code solution?
If yes, try to attach a sample worksheet.

Sample sheet is just attached in original post, which is same as screenshot. Few data, just showing basic idea.

Column A:D is same as database table layout.

My issue is: data source is in MS S-Q-L table, not in excel sheet. I need the code to pull data from database to excel sheet.

Remember: I don't know how many subjects and what each subject is, so math/science/history should not be part of code.

Basically, the question is --- How to use VBA to get pivot table if database table format is something like column A:D, where there are tens of thousands of StudentID, and there are hundreds of Subjects. Anyway, if going with column A:D format, there could be more than one million rows.

[alansidman]

Here is a potential option with Power Query

Please Login or Register  to view this content.

[VAer]

Here is a potential option with Power Query

Please Login or Register  to view this content.

I am not sure if it works or not.

Source is not excel table, source is MS S-Q-L table, which may have more than one million of records.

[bebo021999]

Source is not excel table, source is MS S-Q-L table

Now I understand.
I am not familiar with ADO, but try to split the source into 2 sheets, like this

 Dim maxRows As Long
 maxRows = 500000 ' maximum rows in a sheet

Do While Not TestScoreRs.EOF
    If row > maxRows Then
            sheetNum = sheetNum + 1
            Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
            ws.Name = "Sheet1_" & sheetNum
      ..... then code to work with each sheet1, 2,... 

After importing done, it is so easy to create the yellow table

[alansidman]

Power Query can source its data from a SQL table.

Data-->Get Data--From DataBase-->From SQL Server DataBase

there are other DataBase Formats that it can source from also.

[VAer]

Now I understand.
I am not familiar with ADO, but try to split the source into 2 sheets, like this

 Dim maxRows As Long
 maxRows = 500000 ' maximum rows in a sheet

Do While Not TestScoreRs.EOF
    If row > maxRows Then
            sheetNum = sheetNum + 1
            Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
            ws.Name = "Sheet1_" & sheetNum
      ..... then code to work with each sheet1, 2,... 

After importing done, it is so easy to create the yellow table

Thanks for the information.

[VAer]

Power Query can source its data from a SQL table.

Data-->Get Data--From DataBase-->From SQL Server DataBase

there are other DataBase Formats that it can source from also.

I intended to use VBA, since I plan to build front end application (VBA application). I expect the application to be used by other people, and get the process done automatically, and don't want other people to learn to deal with power query.