SQl Server: Group Adjacent Rows if Certain Column Value is Same.

Below image to the left is original data as in Status change Tracker table. And, what we are expecting is the one to the right. i.e. we want to group the adjacent rows if they are in same stage and need the time stamp when the Stage added for the first time.

To achieve this, below is the simple query needed.

select t.*
from #StatusLog t
left join #StatusLog t1 on t.id = t1.id + 1 and t1.Stage = t.Stage 
where t1.id is null

If we don't have a Column as ID with sequential numbers, use Row_Number () to get the sequential number for rows and execute above script by replacing Id column with it.

Also, if multiple columns should be equal, we can add them along with stage condition in above script.

Access Sharepoint Lists in SSRS Reports (SQL Server 2012)

There are many cases where we need to display SharePoint List records in SSRS reports. To achieve this, earlier we used to follow different hard paths like trying to access SharePoint Database, using some 3rd party tools to integrate SharePoint lists, etc.


But, in SQL Server 2012 reporting / Report Builder 3.0, this was made straight forward be adding an option 'Microsoft SharePoint List' in Data source type selection. follow below simple steps to get some basic idea on creating a Dataset for a SharePoint List.


1. Click on Create New Data Source and select 'Microsoft SharePoint List' from Type Selection Dropdown

2. Now, prove the SharePoint Portal URL in Connection String. Also, provide the Credentials as per your need and Click on Ok.

3. Now we have a dataset pointing to the SharePoint Portal. We are left with creating a Dataset. Click on Create Dataset and select SharePoint Portal as Data source.

4. Now, click on Query Designer and you will be able to see the SharePoint Lists / Libraries available in provided SharePoint portal.

5. Select the List you needed and also the required fields from it. In section 3 in above image, you can filter the SharePoint List data. Once set up, click on OK.


Now we have a dataset which fetches data from SharePoint list and you can build any type of report using this data set.


Below are few important notes to be considered in this aspect.

• Default view data is fetched  from the SharePoint list when we connect it as a Data Source
• If we need to join multiple lists, easy way is to create a list in SharePoint using Lookup relations and then directly creating a dataset for the new list.