Arka Guptta Blog

Saturday, July 22, 2017

XML Path in SQL Server

Now we see the XML Path (Xpath) query in SQL Server.

Here using Xpath query we can get the column values into a single row.

SELECT name FROM sysusers

Result: It will display all user name in a column.

SELECT name FROM sysusers FOR XML PATH ('')

Result: It will display all user name in a single row with name tag.

SELECT ',' + name FROM sysusers FOR XML PATH ('')

Result: It will display all user name in a single row by removing tag and added comma(,) before each user name.

SELECT STUFF((SELECT ',' + name FROM sysusers FOR XML PATH ('')),1,1,'')

Result: It will display all user name in a single row and first comma(,) is not showing for using STUFF() function.

This is how we can get all column values into a single row value.

Now we see the Reverse case. Split the Row values (which is separated by comma) into a Column i.e. row values are divided into a column.

SELECT T.Name AS [TableName], C.name AS [ColumnList]

FROM sys.tables T

INNER JOIN sys.columns C ON T.object_id = C.object_id

WHERE T.name = 'OUSR'

Result: It will display the table name and its all column name. Here I use OUSR table. You can try with other table.

Now I use a Temporary table to save this Column names into a single Row (separated by comma) by using XML path and STUFF() function.

SELECT V1.* INTO #TEMP

FROM ( SELECT 'OUSR' AS [TableName], STUFF((SELECT ',' + C.name

FROM sys.tables T

INNER JOIN sys.columns C ON T.object_id = C.object_id

WHERE T.name = 'OUSR'

FOR XML PATH ('')),1,1,'') AS [ColumnList]

) V1

SELECT * FROM #TEMP

Result: All column names are in a single row (separated by comma)

Now see the row values into a column list.

SELECT A.TableName,

Split.a.value('.', 'VARCHAR(MAX)') AS ColumnList

FROM (SELECT TableName ,

CAST ('<tag>' + REPLACE(ColumnList , ',', '</tag><tag>') + '</tag>' AS XML) AS String

FROM #TEMP ) AS A

CROSS APPLY String.nodes ('/tag') AS Split(a)

Result: Row value are now separated by Column.

If there is any other better option,

Please share it here or mail to me arkaa4@gmail.com

Thank You,

Arka Gupta.

http://www.howtoexcel.org/shortcuts/keyboard-shortcuts/?utm_source=newsletter&utm_medium=email&utm_campaign=the_complete_list_of_over_280_excel_keyboard_shortcuts&utm_term=2017-07-19

Thursday, July 20, 2017

The Complete List Of Excel Keyboard Shortcuts

Thursday, June 22, 2017

GST Calculation Formula (India) using SQL Server

Hi, friends GST is now start in INDIA. Now I’m describing how to calculate Product Amount and GST price from MRP Rate through our programing languages.

The basic calculation is Amount Excluding GST = (100*MRP (With GST))/ (100+GST (%))

For Example MRP Rate = 105 (N.B. MRP is include with GST)

That price has included GST 5%.

Amount Excluding GST = (100 * 105) / (100 + 5)

That will give Amount Excluding GST = 100 Rs.

GST Amount = MRP RATE – Amount Excluding GST

GST Amount = (105 – 100)

GST Amount = 5 Rs.

Now I’ve written a simple SQL Query to fetch these amount. This code is help you to use in other languages like .Net, Java, etc.

DECLARE @MRP AS NUMERIC(19,2) = 100

DECLARE @GST AS NUMERIC(19,2) = 5

SELECT Amount = (100*@MRP)/(100+@GST)

SELECT GST = @MRP - (100*@MRP)/(100+@GST)

-------------------------------------------------------------------------------------------------------------------

Result : Amount = 95.238095238095238

GST = 4.761904761904762

-------------------------------------------------------------------------------------------------------------------

If there is any other better option,

Please share it here or mail to me arkaa4@gmail.com

Thank You,

Arka Gupta.

Wednesday, May 31, 2017

Offline install .Net Framework 3.5 (includes .NET 2.0 and 3.0) in Windows 8

1st step - Insert the Windows 8 installation disc into your DVD Drive

and Click Windows key + Q then type cmd on the Search.

2nd step - When the Command Prompt App appears Right Click it

and then Click Run as administrator

3rd step – Now Administrator: Command Prompt Start, then type

dism /online /enable-feature /featurename:NetFx3 /all /source:\sources\sxs

4th step – Then Press Enter Key.

Now Install .Net Framework 3.5(include 2.0 and 3.0),

after the operation completed successfully you can see another Window.

Source : http://whotrust.blogspot.in/2013/02/offline-installation-of-net-framework.html

If there is any other better option,

Please share it here or mail to me arkaa4@gmail.com

Thank You,

Arka Gupta.

Thursday, May 18, 2017

Export Data from SQL to Excel with column headers in VB.Net

First we must declare these in top of the class file.
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel

Now, just copy this code in Button Click event

Private Sub BtnSql2Excel_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click

ProgressBar1.Value = 0

ProgressBar1.Maximum = DataGridView1.RowCount

Dim xlApp As Excel.Application

Dim xlWorkBook As Excel.Workbook

Dim xlWorkSheet As Excel.Worksheet

Dim misValue As Object = System.Reflection.Missing.Value

Dim i As Int16, j As Int16

xlApp = New Excel.ApplicationClass

xlWorkBook = xlApp.Workbooks.Add(misValue)

xlWorkSheet = xlWorkBook.Sheets("Sheet1")

'Excel Header Row

Dim columnsCount As Integer = DataGridView1.Columns.Count

For Each column In DataGridView1.Columns

xlWorkSheet.Cells(1, column.Index + 1).Value = column.Name

'Excel Header Row End

'Excel Details Row

For i = 0 To DataGridView1.RowCount - 1

For j = 0 To DataGridView1.ColumnCount - 1

xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString()

ProgressBar1.Value = ProgressBar1.Value + 1

'Excel Details Row End

xlWorkBook.Activate()

FolderBrowserDialog1.ShowDialog()

Dim path As String = FolderBrowserDialog1.SelectedPath

xlWorkBook.SaveAs(path & "\SqltoExcel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, _

Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue)

xlWorkBook.Close(True, misValue, misValue)

xlApp.Quit()

releaseObject(xlWorkSheet)

releaseObject(xlWorkBook)

releaseObject(xlApp)

MsgBox("Find Excel File at " & path & "\SqltoExcel.xls", MsgBoxStyle.Information, "Convert Excel Successfully")

End Sub

Then just create a function named ReleaseObject

Private Sub releaseObject(ByVal obj As Object)

Try

System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)

obj = Nothing

Catch ex As Exception

obj = Nothing

MessageBox.Show("Exception Occured while releasing object " + ex.ToString())

Finally

GC.Collect()

End Try

End Sub

Now, execute the program...

If there is any other better option,

Please share it here or mail to me arkaa4@gmail.com

Thank You,

Arka Gupta.

Wednesday, March 15, 2017

We can fetch a Null value column using ISNULL() function.
If the value is empty then we'll use IsNull or Empty function...

We can use it two types:-

The simple code is : SELECT ISNULL(NULLIF(@parameter1, ''), @parameter2)

And the another way is to create a function :

/****** Function ::: IsNullOrEmpty Function ******/

IF EXISTS(SELECT * FROM SysObjects WHERE NAME = 'IsNullOrEmpty' AND XTYPE = 'FN')

BEGIN

DROP FUNCTION IsNullOrEmpty

END

CREATE FUNCTION [dbo].[IsNullOrEmpty]

(@value NVARCHAR(max),

@return NVARCHAR(max)

)

RETURNS NVARCHAR(MAX)

BEGIN

IF (@value IS NULL)

BEGIN

RETURN @return

END

ELSE

BEGIN

IF (LEN(LTRIM(@value)) = 0)

BEGIN

RETURN @return

END

RETURN @value;

END

Then we use this simple code : SELECT dbo.IsNullOrEmpty(@parameter1,@parameter2)

If there is any other better option,

Please share it here or mail to me arkaa4@gmail.com

Thank You,

Arka Gupta.