Posts

SQL Pivot

SELECT Item, imitdesc, 'Price' as Val,[2018-1],[2018-2],[2018-3],[2018-4],[2018-5],[2018-6],[2018-7],[2018-8],[2018-9],[2018-10],[2018-11],[2018-12],[2019-1],[2019-2],[2019-3],[2019-4],[2019-5],[2019-6],[2019-7],[2019-8],[2019-9],[2019-10],[2019-11],[2019-12] FROM ( SELECT Item, imitdesc, isnull(UPrice,0) as UPrice, cast(MYear as varchar) + '-' + cast(Mmonth as varchar) as Mmonth--, --isnull(GRNQty,0)-isnull(IssueQty,0) as Amount FROM #TempCons ) as s PIVOT ( SUM(UPrice) FOR [Mmonth] IN ([2018-1],[2018-2],[2018-3],[2018-4],[2018-5],[2018-6],[2018-7],[2018-8],[2018-9],[2018-10],[2018-11],[2018-12],[2019-1],[2019-2],[2019-3],[2019-4],[2019-5],[2019-6],[2019-7],[2019-8],[2019-9],[2019-10],[2019-11],[2019-12]) )AS pvt

Insert Records to SQl Temp Table

SELECT * INTO # TempTable FROM OriginalTable

Remove Line Breaks SQL

To remove these SQL line breaks, use the SQL Server replace function. Indeed, the CHAR(13) and CHAR(10) specific codes represent the characters of the line breaks,and then replace them with a space or a comma, for example. This time, the copy paste gives us this result. REPLACE( I.DelAddress ,CHAR(13)+CHAR(10),' ')

Find Specific Text on Stored Procedure

Image
Do you remember we had so many requirements to check a text on stored procedures or a view? Think that we are going to change a field name of a table or a data type of a field. Or we are going to replace or delete a data table in your database. Database might be so big and there may be hundreds of stored procedures. We can't open every single stored procedures or a view and check whether they are using the data table we going to delete. For that, We can simply find the stored procedures which are using this particular data table or a field. That may help us to reduce the work load more than 80%. You can use this following query for it. SELECT     * FROM       INFORMATION_SCHEMA . ROUTINES WHERE      ROUTINE_DEFINITION LIKE '%TableName%'

Microsoft SharePoint 2010 Training Tutorial

Image
Microsoft SharePoint 2010 Training Tutorial  [Sites and Templates] Microsoft SharePoint 2010 Training Tutorial [Working with Wikis] Note :credits goes to original uploader

Apply CSS styles to SharePoint Web parts

Image
In SharePoint we are using various types of web parts. List View web part are one of major type which uses to populate data on the web page from a SharePoint list. By default all the web parts of the page use the styles inherits from the site theme. Here I explain a simple workout to apply a CSS style to a web part only using out of the box features of SharePoint 2010. There are only 3 steps for you to follow. 1. Get the ID of web part you wants to apply CSS style 2. Add Content Editor web part 3. Apply CSS style as you required Step 1 ♣ Go to “view source” of the browser you are using and search for the web parts name. In my scenario, it’s “Emergency”.   ♣ Now grab the ID number mentioned there at the end of WebPartTitleWPQ 2 . “2” is the ID of “Emergency” web part in my web page. Step 2 ♣ Now add a Content Editor Web Part by go to edit mode of the page. ♣ Set the Chrome Type as None of the added Content Editor Web Part. Step

SharePoint Number Column Without Commas

Image
Avoiding thousand separator(Comma) in SharePoint Number field SharePoint number fields having thousand separators(Comma) which may not want us to need every time. But there are no possibility to avoid the thousand separator(Comma) in the number fields.  To avoid this comma we can do a small workout as following.. Suppose our Number field named as "Student No." Create another Calculated column and name it as "Student Number" Then you have to type the formula according to the following format     = TEXT( Number_Column_Name, "0") And click Save In our scenario it will show as =TEXT([Student No],"0") Note : If the Student No field will empty, the value will be shown as "0".... Or if you want to keep it blank you have to adjust the formula as   =IF( [Student No] <>"",TEXT( [Student No] ,"0"),"")