SQL Query with months dynamically generated with the result counts.

If you can share more details or sample data on how your original records are, we can let you know whether it is feasible to get the values in the format you have provided..
Ideally, we should be able to get the values in the dynamically generate month values with PIVOT feature but would need to see the source data format to confirm..

Yep, can do it with SQL

As Raja said above, would need more information / table structures / sample data / SQL Version and so on.

One of the challenges with PIVOT in SQL Server is you need to 'Know' the column names and refer to them explicitly - they must be premeditated.

If needed, you could resort to dynamic SQL to build the list of columns.... If Month, then dont really have to - just a bit of typing....

Your Excel spreadsheet is pretty good, but lacks some information - such as how the month columns are determined. I would assume that some of that data is aggregated and some of the data is more detailed than shown.

Still, using that spreadsheet as a base, and adding a column to reflect an individual [Exam Date] we can generate some test data :

create table #exam_results (id int identity, [name] varchar(20), DOB datetime, [Exam Score] int, [exam completed] datetime, [Exam Date] datetime)

insert #exam_results values ('James','1973-03-12',56,'1990-05-08','1990-01-01')
insert #exam_results values ('James','1973-03-12',56,'1990-05-08','1990-03-01')
insert #exam_results values ('James','1973-03-12',56,'1990-05-08','1990-05-08')

insert #exam_results values ('Mike','2004-05-21',78,'2010-03-02','2010-01-01')
insert #exam_results values ('Mike','2004-05-21',78,'2010-03-02','2010-02-01')

insert #exam_results values ('Lisa','2009-05-07',90,'2015-05-05','2015-05-05')

Select allOpen in new window

And now we have some test data, we can set about a couple of examples....

First the generic PIVOT to show the reliance on predetermined column names

select * 
from
( select [name],[dob],[exam score], [exam completed]
         ,case when [exam score] > 0 then 1 else 0 end as [Exam Result]
         ,left(datename(Month,[Exam date]),3) as [Exam Month]
  from #exam_results ) src
PIVOT
( sum([Exam Result]) for [Exam Month] in ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]))pvt

Select allOpen in new window

Now while that kind of fulfills the requirement (loosely), we note that a lot of columns are NULL, and your example shows zeros. That can be rectified by replacing that (awful) SELECT * with actual column names checking for NULL, and in doing so we can simplify the PIVOT to use month numbers....

select [name],[dob],[exam score], [exam completed],isnull([1],0) as [Jan],isnull([2],0) as [Feb],isnull([3],0) as [Mar],isnull([4],0) as [Apr],isnull([5],0) as [May],isnull([6],0) as [Jun],isnull([7],0) as [Jul],isnull([8],0) as [Aug],isnull([9],0) as [Sep],isnull([10],0) as [Oct],isnull([11],0) as [Nov],isnull([12],0) as [Dec]
from
( select [name],[dob],[exam score], [exam completed]
         ,case when [exam score] > 0 then 1 else 0 end as [Exam Result]
         ,Month([Exam date]) as [Exam Month]
  from #exam_results ) src
PIVOT
( sum([Exam Result]) for [Exam Month] in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))pvt

Select allOpen in new window

Anyway, that is a starting point.

I have written Articles that may help explain the PIVOT in more detail. Have a read of : https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html

Look forward to seeing some of those table structures and sample data.

I see this is your first question, so, a whole-hearted welcome to 'Asking' questions, and if anything is unclear or confusing, please ask your experts to explain. We are here to help you....

Thanks for all your responses. Sorry to respond late. I got the idea but the scenerio for me is , we are using DevExpress Exporter to Excel. We are exporting data with identical row information but each row has unique id. Now for example if two rows have same content with different ID's so before it exported to excel i need to find the equal rows and delete the identical ones. But with that i need to update the month column to the row which is not deleted. So for example there is count 1 task occured in January month for one row and the other row task comes as count 1 in Feb. So as the Excel attachment which i shared before , i need to remove the row with same content and move the count 1 to the  upper row.
If u see the sample attached spread sheet.
I need to Delete the row in red color and move the cell value from Green to Purple color.

When i try doing this using Worksheet object of Devexpress so it worked fine with few records but with large number of records the performance was really really slow due to For loop. So i thought to move such feature totally on the database side. Create some kind of table like this at Database level and then process records and pass result set to C# object as Datatable and then export this information.

If any more ideas on this please , like how can i approach this problem ,can share. Thanks for your time again.
Book1.xlsx

Well, give us some table structures and some sample data for those structures.

I am pretty sure we can come up with a solution for you, but that spreadsheet doesnt really help with table structures.

>> If any more ideas on this please , like how can i approach this problem ,can share

As requested by Mark above, we would require the Source or raw data ie., before you identify the duplicates..
Book1.xlsx you have provided shows us the expected output and if you can provide us sample source data it would be great to assist you further..

Thanks for all the responses. Really helpful for me , sure i will provide more close data structure in next response. But really thanks for all the solutions. I will give some more sample data.

We will be here.... Look forward to seeing the sample data.