Sql combine multiple rows into one string

In this article I am going to explain how to show multi row column value into single row in sql.

Output

Calculate hours minutes between two dates in SQL

In this article, I am going to create logic how to calculate hours and minute between two dates in SQL.

DECLARE @date1 DATETIME = '2016-06-23 12:18:03.133'; DECLARE @date2 DATETIME = getdate(); SELECT CAST((DATEDIFF(Minute, @date1, @date2)) / 60 AS VARCHAR(5)) + ' Hrs' + ' ' + RIGHT('0' + CAST((DATEDIFF(Minute, @date1, @date2)) % 60 AS VARCHAR(2)), 2) + ' Min' AS 'TotalTime'

Output

Group Functions in SQL

These functions take a group of rows into consideration and returns a single value as an output.

Count (*)

It returns the no. of rows that can be retrieved by the given 'select' statement.

Example

select Count (*) from Emp where depno = 30

select Count (*) from Emp where Job = 'Clerk'

Count (colname)

It returns the no. of Not Null values that are present in the specified column.

Example

select Count (comm) from Emp

select Count (M62) from Emp

NOTE

All the Group functions will not take Null values into consideration.

Sum (colname)

It returns the sum of the specified column.

Example

select Sum (sal) from Emp

select Sum (comm) from Emp

Avg (colname)

It returns the average of the specified column i.e., Sum(colname) / Count(colname)

Example

select Avg (sal) from Emp

select Avg (comm) from Emp

Max (colname)

It returns the highest value of the specified column.

Example

select Max (sal) from Emp

Min (colname)

It returns the least value of the specified column. 

Example

select Min (sal) from Emp

Stdev (colname)

It returns the standard deviation of the specified column.

Example

select Stdev (sal) from Emp

Var (colname)

It returns the variance of the specified column.

Example

select Var (sal) from Emp

Distinct function

It returns the values of the specified column by eliminating duplicates.

Example

select Distinct job from Emp

how to read xml file in sql server stored procedure

In this article, I am going to explain how to read or select xml data in SQL.

Firstly I show you xml which one is ready in SQL procedure. The given below xml have Empcode,name,DOB and age fileds, which one shown by sql in a table

<EmpDetails>     <NewDataSet>         <EmpCode>EMP014</EmpCode>         <name>Sharad</name>         <DOB>12/24/1978 12:00:00 AM</DOB>         <age>35</age>         <location>x</location>         <gender>Male</gender>         <StateID>1</StateID>         <CityID>1</CityID>         <designation>1 </designation>     </NewDataSet>     <NewDataSet>         <EmpCode>EMP015</EmpCode>         <name>Devesh </name>         <DOB>12/24/1983 12:00:00 AM</DOB>         <age>31</age>         <location>Delhi</location>         <gender>Male</gender>         <StateID>1</StateID>         <CityID>1</CityID>         <designation>1</designation>     </NewDataSet> </EmpDetails>

Now i write a Procedure that read xml and return result in table format.

Create PROCEDURE usp_ReadXml (                 @xmlString NVARCHAR(MAX) = '<EmpDetails>     <NewDataSet>         <EmpCode>EMP014</EmpCode>         <name>Sharad</name>         <DOB>12/24/1978 12:00:00 AM</DOB>          <age>35</age>         <location>x</location>         <gender>Male</gender>         <StateID>1</StateID>         <CityID>1</CityID>         <designation>1 </designation>     </NewDataSet>     <NewDataSet>         <EmpCode>EMP015</EmpCode>         <name>Devesh </name>         <DOB>12/24/1983 12:00:00 AM</DOB>         <age>31</age>         <location>Delhi</location>         <gender>Male</gender>         <StateID>1</StateID>         <CityID>1</CityID>         <designation>1</designation>     </NewDataSet> </EmpDetails> '                 ) AS BEGIN                 DECLARE @xmlHandle INT                   EXEC sp_xml_preparedocument @xmlHandle OUTPUT,  @xmlString                   SELECT NAME,                                 Age,                                 Gender,                                 Location,                                 designation,                                 StateID,                                 CityID,                                 EmpCode,                                 DOB                 FROM OPENXML(@xmlHandle, '/EmpDetails/NewDataSet', 2) WITH (                                                 name NVARCHAR(100),                                                 age INT,                                                 gender VARCHAR(10),                                                 location VARCHAR(50),                                                 designation INT,                                                 StateID INT,                                                 CityID INT,                                                 EmpCode VARCHAR(50),                                                 DOB DATETIME                                                 )                   EXEC sp_xml_removedocument @xmlHandle END

Output is

Run this command "Exec usp_ReadXml" and now output is

Data Type in SQL

SQL have following data types

1-Integer value

Integer value have following data types

Type	Storage
TINY	One(1) byte
SMALL INT	Two(2) byte
INT	Four(4) byte
BIG INT	Eight (8) byte

2-Float/Decimal values

Syntax

decimal(p,s)

where p=(Permission)

The permission means total number of digits can be present in the value (maximum value is 38 digits).

s=(scale)

It means your number of digits that can present after decimal.

3 - Numeric (p,s)

Syntax

Numeric (p,s)

It is equal to decimal data type.

4 - Boolean Values

Syntax

bit

It is bit type and take 1 byte space. It holds either 0 or 1 where 0 means "false" and 1 means true.

5 - Character Values

Syntax

char(n)

The maximum value of n is 8000. It is called as fixed data type. once you specified n value, the sql uses all the size.

6 - Varchar values

Syntax

varchar(n)

The maximum value of n is 8000. It is variable length character data type. In this case it does not fill any blank space.

7 - Text

Syntax

Text

It is in 2 GB size. It is variable length character data type. In this case it does not fill any blank space.

8 - NChar

Syntax

nchar(n)

It is similar to the char data type but can store multi language character. The maximum capacity is 4000 characters.

9 - Currency Values

It can only use for US currency format, The currency values are two type

a- Small Money - 4 bytes

b- Money - 8 bytes

10 - For Storing Binary Values

If you want to store images or video files or audio files in the database, you can use binary data type

a - Binary (n) - The maximum value is 8000  bytes, it is fixed length data type similar to char and nchar data type.

b- VarBinary(n) - The maximum value is 8000  bytes.

c - Image - The maximum Size is 2 GB.

11 - For Storing Date Values

a - Small Date Time

Syntax

smalldatetime

It is 4 bytes, It can hold data ranging between  Jan 1, 1900 to Jun 6,2079.

b - Date Time

Syntax

datetime

Size of Date Time is 8 bytes.It can hold data ranging between January 1,1753 to Dec 31, 9999

c- Time Stamp

Syntax

timestamp

If this data type is used in column wherever a modification is made to a record SQL Server will enter the date & time into the column. The size is 8 bytes

12 - For Storing XML Values


It is size in 2 GB.