Concatenate values within each group defined by GROUP BY clause - SQL Server

In this article, you can learn how to do group concatenation in SQL Server. Group concatenation means concatenating values within each group defined by GROUP BY clause. Here a list of strings to be combined and places a separator between them.
Consider the following table:

CREATE TABLE EMPLOYEE(ENAME VARCHAR(45),DEPT VARCHAR(45))
INSERT INTO EMPLOYEE VALUES ('Mahesh','IT')
INSERT INTO EMPLOYEE VALUES ('Suresh','ACCOUNTS')
INSERT INTO EMPLOYEE VALUES ('Prakesh','ACCOUNTS')
INSERT INTO EMPLOYEE VALUES ('Rajesh','IT')
INSERT INTO EMPLOYEE VALUES ('Kamesh','ACCOUNTS')
INSERT INTO EMPLOYEE VALUES ('Prajnesh','IT')
INSERT INTO EMPLOYEE VALUES ('Sateesh','IT')
INSERT INTO EMPLOYEE VALUES ('Venkatesh','SALES')
INSERT INTO EMPLOYEE VALUES ('Rupesh','ACCOUNTS')
INSERT INTO EMPLOYEE VALUES ('Yagnesh','SALES')

Using STRING_AGG() Function (SQL Server 2017 or later)

SELECT DEPT, STRING_AGG(ENAME, ',') AS ENAME_LIST
FROM EMPLOYEE
GROUP BY DEPT

Using STUFF AND FOR XML PATH

SELECT T1.DEPT, STUFF((SELECT ','+ENAME
    FROM EMPLOYEE T2
    WHERE T2.DEPT=T1.DEPT
    FOR XML PATH('')),1,1,'') AS ENAME_LIST
FROM EMPLOYEE T1
GROUP BY DEPT

Using REPLACE AND FOR JSON PATH, WITHOUT_ARRAY_WRAPPER (SQL Server 2016 or later)

SELECT T3.DEPT, REPLACE(REPLACE(T3.ENAME,'{"ENAME":"',''),'"}','') AS ENAME_LIST
FROM (
 SELECT DEPT, (SELECT ENAME AS [ENAME]
        FROM EMPLOYEE T2
        WHERE T2.DEPT=T1.DEPT
        FOR JSON PATH,WITHOUT_ARRAY_WRAPPER) ENAME
    FROM EMPLOYEE T1
    GROUP BY DEPT) T3

Using CTE and ROW_NUMBER Function

;WITH
    ELIST1(DEPT, ENAME, RID)
    AS
    (
        SELECT DEPT, ENAME, ROW_NUMBER() OVER (PARTITION BY DEPT ORDER BY ENAME)
        FROM EMPLOYEE
    )
,
    ELIST2(DEPT, ENAME_LIST, RID)
    AS
    (
                    SELECT DEPT, TRY_CONVERT(VARCHAR(MAX),ENAME), 1
            FROM ELIST1
            WHERE RID=1
        UNION ALL
            SELECT ELIST2.DEPT, TRY_CONVERT(VARCHAR(MAX),ELIST2.ENAME_LIST+','+ELIST1.ENAME), ELIST2.RID+1
            FROM ELIST2
                INNER JOIN
                ELIST1 ON ELIST1.DEPT=ELIST2.DEPT AND ELIST1.RID=ELIST2.RID+1
    )
SELECT DEPT, MAX(ENAME_LIST) AS ENAME_LIST
FROM ELIST2
GROUP BY DEPT

Output (Output is same for all above examples)

DEPT	ENAME_LIST
ACCOUNTS	Kamesh,Prakesh,Rupesh,Suresh
IT	Mahesh,Prajnesh,Rajesh,Sateesh
SALES	Venkatesh,Yagnesh

That's all about group concatenation in SQL Server.
Let me know if you have any questions, suggestions or comments.

Read More

DATEPART Function - SQL Server Date Time Functions

DATEPART function returns an integer that represents the specified datepart of the specified date. This is very similar to DATENAME. This function returns an integer value. The return value depends on the language environment set by using "SET LANGUAGE".  DATEPART function can be used in the SELECT list, WHERE, GROUP BY, HAVING, ORDER BY.
Syntax

DATEPART ( datepart, date )

datepart	Specifies the part of the date that DATEPART will return
date	an expression that returns a date or datetime or datetimeoffset or datetime2 or smalldatetime or time or string in a valid date format value

The table below lists all valid datepart arguments

datepart	Abbreviations	Return Value
year	yy, yyyy	1840-9999
quarter	qq, q	1-4
month	mm, m	1-12
dayofyear	dy, y	1-366
day	dd, d	1-31
week	wk, ww	1-53
weekday	dw, w	1-7
hour	hh	0-23
minute	mi, n	0-59
second	ss, s	0-59
millisecond	ms	0-99(with precision of 2)
microsecond	mcs
nanosecond	ns
TZoffset	tz
ISO_WEEK	ISOWK, ISOWW	1-53

Examples

DECLARE @date DATETIME 
SET @date= '2019-08-27 17:26:27.640' 

SELECT @date AS CurrentDate;------------------------------2013-04-07 23:28:42.013

SELECT DATEPART (year,@date) AS 'Year';--------------------2019 
SELECT DATEPART (yy,@date) AS 'Year';----------------------2019 
SELECT DATEPART (yyyy,@date) AS 'Year';--------------------2019 

SELECT DATEPART (quarter,@date) AS 'Quarter';--------------3 
SELECT DATEPART (qq,@date) AS 'Quarter';-------------------3 
SELECT DATEPART (q,@date) AS 'Quarter';--------------------3 

SELECT DATEPART (month,@date) AS 'Month';------------------8 
SELECT DATEPART (mm,@date) AS 'Month';---------------------8
SELECT DATEPART (m,@date) AS 'Month';----------------------8 

SELECT DATEPART (dayofyear,@date) AS 'Dayofyear';----------239 
SELECT DATEPART (dy,@date) AS 'Dayofyear';-----------------239 
SELECT DATEPART (y,@date) AS 'Dayofyear';------------------239 

SELECT DATEPART (day,@date) AS 'Day';----------------------27 
SELECT DATEPART (dd,@date) AS 'Day';-----------------------27 
SELECT DATEPART (d,@date) AS 'Day';------------------------27 

SELECT DATEPART (week,@date) AS 'Week';--------------------35 
SELECT DATEPART (wk,@date) AS 'Week';----------------------35 
SELECT DATEPART (ww,@date) AS 'Week';----------------------35 

SELECT DATEPART (weekday,@date) AS 'Weekday';--------------3 
SELECT DATEPART (dw,@date) AS 'Weekday';-------------------3  
SELECT DATEPART (w,@date) AS 'Weekday';--------------------3  

SELECT DATEPART (hour,@date) AS 'Hour';--------------------17 
SELECT DATEPART (hh,@date) AS 'Hour';----------------------17 

SELECT DATEPART (minute,@date) AS 'Minute';----------------26 
SELECT DATEPART (mi,@date) AS 'Minute';--------------------26 
SELECT DATEPART (n,@date) AS 'Minute';---------------------26 

SELECT DATEPART (second,@date) AS 'Second';----------------27 
SELECT DATEPART (ss,@date) AS 'Second';--------------------27 
SELECT DATEPART (s,@date) AS 'Second';---------------------27 

SELECT DATEPART (millisecond,@date) AS 'Milli Second';-----640 
SELECT DATEPART (ms,@date) AS 'Milli Second';--------------640 

SELECT DATEPART (microsecond,@date) AS 'Micro Second';-----640000 
SELECT DATEPART (mcs,@date) AS 'Micro Second';-------------640000 

SELECT DATEPART (nanosecond,@date) AS 'Nano Second';-------640000000 
SELECT DATEPART (ns,@date) AS 'Nano Second';---------------640000000 

SELECT DATEPART (TZoffset,SYSDATETIMEOFFSET()) AS 'TZoffset';-- 330 ( Returns minutes 330 -> +05:30 ) 
SELECT DATEPART (tz,SYSDATETIMEOFFSET()) AS 'TZoffset';-------- 330 

SELECT DATEPART (ISO_WEEK,@date) AS 'ISO_WEEK';-------------35
SELECT DATEPART (ISOWK,@date) AS 'ISO_WEEK';----------------35
SELECT DATEPART (ISOWW,@date) AS 'ISO_WEEK';----------------35

Read More

DATENAME Function - SQL Server Date Time Functions

DATENAME function returns a character string representing the specified datepart of the specified date. This function returns a nvarchar value. The return value depends on the language environment set by using "SET LANGUAGE".  DATENAME function can be used in the SELECT list, WHERE, GROUP BY, HAVING, ORDER BY.
Syntax

DATENAME ( datepart, date )

datepart	Specifies the part of the date that DATENAME will return
date	an expression that returns a date or datetime or datetimeoffset or datetime2 or smalldatetime or time or string in a valid date format value

The table below lists all valid datepart arguments

datepart	Abbreviations	Return Value
year	yy, yyyy	1840-9999
quarter	qq, q	1-4
month	mm, m	January,...December
dayofyear	dy, y	1-366
day	dd, d	1-31
week	wk, ww	1-53
weekday	dw, w	Sunday,..Saturday
hour	hh	0-23
minute	mi, n	0-59
second	ss, s	0-59
millisecond	ms	0-99(with precision of 2)
microsecond	mcs
nanosecond	ns
TZoffset	tz
ISO_WEEK	ISOWK, ISOWW	1-53

Examples

DECLARE @date DATETIME 
SET @date= '2019-08-27 16:28:42.013' 
 
SELECT @date AS CurrentDate;------------------------------2019-08-27 16:28:42.013

SELECT DATENAME(year,@date) AS 'Year';--------------------2019 
SELECT DATENAME(yy,@date) AS 'Year';----------------------2019 
SELECT DATENAME(yyyy,@date) AS 'Year';--------------------2019 

SELECT DATENAME(quarter,@date) AS 'Quarter';--------------3 
SELECT DATENAME(qq,@date) AS 'Quarter';-------------------3 
SELECT DATENAME(q,@date) AS 'Quarter';--------------------3 

SELECT DATENAME(month,@date) AS 'Month';------------------August 
SELECT DATENAME(mm,@date) AS 'Month';---------------------August
SELECT DATENAME(m,@date) AS 'Month';----------------------August 

SELECT DATENAME(dayofyear,@date) AS 'Dayofyear';----------239 
SELECT DATENAME(dy,@date) AS 'Dayofyear';-----------------239 
SELECT DATENAME(y,@date) AS 'Dayofyear';------------------239 

SELECT DATENAME(day,@date) AS 'Day';----------------------27 
SELECT DATENAME(dd,@date) AS 'Day';-----------------------27 
SELECT DATENAME(d,@date) AS 'Day';------------------------27 

SELECT DATENAME(week,@date) AS 'Week';--------------------35 
SELECT DATENAME(wk,@date) AS 'Week';----------------------35 
SELECT DATENAME(ww,@date) AS 'Week';----------------------35 

SELECT DATENAME(weekday,@date) AS 'Weekday';--------------Tuesday 
SELECT DATENAME(dw,@date) AS 'Weekday';-------------------Tuesday  
SELECT DATENAME(w,@date) AS 'Weekday';--------------------Tuesday  

SELECT DATENAME(hour,@date) AS 'Hour';--------------------16 
SELECT DATENAME(hh,@date) AS 'Hour';----------------------16 

SELECT DATENAME(minute,@date) AS 'Minute';----------------28 
SELECT DATENAME(mi,@date) AS 'Minute';--------------------28 
SELECT DATENAME(n,@date) AS 'Minute';---------------------28 

SELECT DATENAME(second,@date) AS 'Second';----------------42 
SELECT DATENAME(ss,@date) AS 'Second';--------------------42 
SELECT DATENAME(s,@date) AS 'Second';---------------------42 

SELECT DATENAME(millisecond,@date) AS 'Milli Second';-----13 
SELECT DATENAME(ms,@date) AS 'Milli Second';--------------13 

SELECT DATENAME(microsecond,@date) AS 'Micro Second';-----13333 
SELECT DATENAME(mcs,@date) AS 'Micro Second';-------------13333 

SELECT DATENAME(nanosecond,@date) AS 'Nano Second';-------13333333 
SELECT DATENAME(ns,@date) AS 'Nano Second';---------------13333333 

SELECT DATENAME(TZoffset,SYSDATETIMEOFFSET()) AS 'TZoffset';-- +05:30 
SELECT DATENAME(tz,SYSDATETIMEOFFSET()) AS 'TZoffset';-------- +05:30 

SELECT DATENAME(ISO_WEEK,@date) AS 'ISO_WEEK';-------------35
SELECT DATENAME(ISOWK,@date) AS 'ISO_WEEK';----------------35
SELECT DATENAME(ISOWW,@date) AS 'ISO_WEEK';----------------35

Read More

Get Column or parameter properties with COLUMNPROPERTY function - SQL Server

Overview
    COLUMNPROPERTY function returns the property information about the column or parameter. This function returns an integer value. It returns 1 for true, 0 for false, NULL for invalid input/value,  and -1 for XML or large value types for the precision property.
    Catalog Views and Information_schema objects are used to get a lot of information about the column or parameter info in a useful format. But some additional column level properties information is missing which you can get using the COLUMNPROPERTY function.
Catalog Views: sys.columns, sys.procedures, sys.parameters, sys.objects
INFORMATION_SCHEMA: information_schema.columns, information_schema.parameters

Syntax

COLUMNPROPERTY ( id , column , property )

ID	Identifier represents the table object ID or Procedure ID
Column	Column represents the name of the column or parameter
Property	Property represents pre-defined values for the property information

The following are available pre-defined values for property argument.

Value	Description
AllowsNull	Allows null values.
ColumnId	Column ID value corresponding to sys.columns.column_id.
FullTextTypeColumn	The TYPE COLUMN in the table holding the document type information of the column.
GeneratedAlwaysType	Is column value system-generated. Corresponds to sys.columns.generated_always_type
IsColumnSet	Column is a column set. For more information, see Use Column Sets.
IsComputed	Column is a computed column.
IsCursorType	Procedure parameter is of type CURSOR.
IsDeterministic	Column is deterministic. This property applies only to computed columns and view columns.
IsFulltextIndexed	Column is registered for full-text indexing.
IsHidden	Is column value system-generated. Corresponds to sys.columns.is_hidden
IsIdentity	Column uses the IDENTITY property.
IsIdNotForRepl	Column checks for the IDENTITY_INSERT setting.
IsIndexable	Column can be indexed.
IsOutParam	Procedure parameter is an output parameter.
IsPrecise	Column is precise. This property applies only to deterministic columns.
IsRowGuidCol	Column has the uniqueidentifier data type, and is defined with the ROWGUIDCOL property.
IsSparse	Column is a sparse column. For more information, see Use Sparse Columns.
IsSystemVerified	The Database Engine can verify the determinism and precision properties of the column. This property applies only to computed columns and columns of views.
IsXmlIndexable	The XML column can be used in an XML index.
Precision	Data type length of the column or parameter.
Scale	Scale for the column or parameter data type.
StatisticalSemantics	Column is enabled for semantic indexing.
SystemDataAccess	Column is derived from a function that accesses data in the system catalogs or virtual system tables of SQL Server. This property applies only to computed columns and columns of views.
UserDataAccess	Column is derived from a function that accesses data in user tables, including views and temporary tables, stored in the local instance of SQL Server. This property applies only to computed columns and columns of views.
UsesAnsiTrim	ANSI_PADDING was set ON at time of table creation. This property applies only to columns or parameters of type char or varchar.

Examples of COLUMNPROPERTY function

-- Get table and identity column names in adventureworks database
SELECT top 5
    OBJECT_NAME(id) as ObjectName, Name as Identity_Column
FROM syscolumns
WHERE COLUMNPROPERTY( id ,name, 'IsIdentity') = 1
ORDER BY 1,2

-- Returns the length of the column (Person.Person.Title)
SELECT COLUMNPROPERTY(OBJECT_ID('Person.Person'), 'Title','PRECISION') AS COL_LEN

Reference :  https://docs.microsoft.com/en-us/sql/t-sql/functions/columnproperty-transact-sql

Read More

Split comma separated string in a column / string to rows and convert into columns in SQL Server

This article is about how to split comma separated string value in a column or string variable value into multiple columns in SQL Server. Here delimiter can be any special character such as comma (,), hash (#), dollar ($), etc.,.

1. Split comma separated string value and convert into columns
2. Split comma separated string in a column and convert into columns

Split comma separated string value and convert into columns
For example, if you have the following string and if you want each of the value comma separated string into a separate column.

-- Input variable (comma seperated)
declare @strval varchar(45)='mahesh,satish,krishna,lakshmi,rakesh'; 
-- Drop temp table if exists
IF OBJECT_ID('tempdb..##strspilt') IS NOT NULL DROP TABLE ##strspilt;
-- Identifying the start and end positions of comma separated values (CTE, CHARINDEX)
-- spilt the string into rows using start and end position
-- final resultset will be copied into temp table (##strspilt)
;with str_spilt_col AS ( 
     SELECT 1 AS start_pos,
        CHARINDEX(',',@strval) AS end_pos,1 AS id
    UNION all
    SELECT CHARINDEX(',',@strval,end_pos)+1 AS start_pos,
        CASE
        WHEN CHARINDEX(',',@strval,end_pos+2)=0 THEN
        DATALENGTH(@strval)+1
        ELSE CHARINDEX(',',@strval,end_pos+2)
        END AS end_pos, id+1 AS id
    FROM str_spilt_col
    WHERE end_pos<=DATALENGTH(@strval)-charindex(',',REVERSE(@strval))+1 )
SELECT SUBSTRING(@strval,
        start_pos,
        end_pos-start_pos) AS msg,
        id into ##strspilt
FROM str_spilt_col 
-- convert rows to columns (DYNAMIC SQL)
-- Prepare dynamic query  
declare @sql nvarchar(500)='',@i int=1 
while (@i<= (SELECT max(id) FROM ##strspilt)) 
begin 
    if (@sql='') 
        SET @sql+='(select msg from ##strspilt where id='+cast(@i AS nvarchar)+') as col'+cast(@i AS nvarchar)
    ELSE 
        SET @sql+=',(select msg as col'+cast(@i AS nvarchar)+' from ##strspilt where id='+cast(@i AS nvarchar)+') as col'+cast(@i AS nvarchar) 
    SET @i+=1 
end; 
SET @sql='select distinct '+@sql 
execute (@sql)

Split comma separated string in a column and convert into columns
For example, if you have the following column (Name_age_gender_height_weight) and if you want each of the value comma separated string from that column into a separate column.

declare @strspilt table
(
  Name_age_gender_height_weight varchar(25)
)
insert into @strspilt values
('mahesh,31,M,178,75'),
('suresh,35,M,172,85'),
('sita,25,F,168,58')

select p1.Pos,p2.Pos,p3.Pos,p4.Pos,
substring(Name_age_gender_height_weight,1, P1.Pos - 1) as Name,
substring(Name_age_gender_height_weight, P1.Pos + 1, P2.Pos - P1.Pos - 1) as Age,
substring(Name_age_gender_height_weight, P2.Pos + 1, P3.Pos - P2.Pos - 1) as Gender,
substring(Name_age_gender_height_weight, P3.Pos + 1, P4.Pos - P3.Pos - 1) as Height,
substring(Name_age_gender_height_weight, P4.Pos + 1, 10) as [weight]
from @strspilt
  cross apply (select (charindex(',', Name_age_gender_height_weight))) as P1(Pos)
  cross apply (select (charindex(',', Name_age_gender_height_weight, P1.Pos+1))) as P2(Pos)
  cross apply (select (charindex(',', Name_age_gender_height_weight, P2.Pos+1))) as P3(Pos)
  cross apply (select (charindex(',', Name_age_gender_height_weight, P3.Pos+1))) as P4(Pos)

That’s it.Let me know if you have any questions, suggestions or comments.

Read More

Default ports used by SQL Server Services

Firewall systems help prevent unauthorized access to computer resources. If a firewall is turned on but not correctly configured, connections to SQL Server instances and related services will be blocked. So database administrators need to ensure that firewalls are properly configured to allow inbound and outbound traffic to the SQL Server instance.
Below table list the default ports used by SQL Server services.

Service	Port	Description
SQL Server Database Services	1433 1434 1433 or Dynamic Port	Default Instance Dedicated Admin Connection Named Instance (If named instance is the only instance of the Database Engine installed, it will probably use TCP port 1433 otherwise it uses different port.UDP port 1434 might be required for the SQL Server Browser Service when you are using named instances.)
SQL Server Browser Service	UDP 1434 TCP 2382	Default
SQL Server Service Broker	TCP 4022	Default
SQL Server Analysis Services	TCP 2383	Default Instance. When using Named Instances, TCP port 2382 is needed.
SQL Server Integration Services	TCP 135	Default
SQL Server Reporting Services	TCP 80 TCP 443	Non-SSL (http://) SSL (https://)

Port 135

TCP port 135 is used for many services.

• Transact-SQL debugger
• Microsoft Distributed Transaction Coordinator (MS DTC)
• Windows Management Instrumentation (WMI) - SQL Server Configuration Manager uses WMI to list and manage services.
• Microsoft remote procedure calls (MS RPC) - used to start, stop, and control SQL Server Integration Services and is required only if you connect to a remote instance of the service from SSMS

View sys.tcp_endpoints helps to list ports used by various services such as Service Broker, Mirroring, etc.,

SELECT name, protocol_desc, port, state_desc FROM sys.tcp_endpoints WHERE type_desc in ('SERVICE_BROKER','DATABASE_MIRRORING')

For detailed information about ports used by SQL Server, refer Microsoft article
"Configure the Windows Firewall to Allow SQL Server Access"

Read More

SQL Server 2017 Graph Databases ( Overview, Uses, Example )

Overview
    Graph database is introduced in SQL Server 2017 version and it is a collection of Nodes and Edges. Both the relational and graph databases are data management system software. In Graph database building blocks are Nodes and Edges whereas in relational database building blocks are tables.

Read More

SQL Server Window Function - ROW_NUMBER() usage and examples

Overview

• ROW_NUMBER function returns a unique integer value starting with one and incremented by one for every row.
• The ROW_NUMBER function enumerates the rows in the sort order defined in the OVER clause.
• An ORDER BY expression inside the OVER clause is mandatory.
• Optional PARTITION BY can divide data into smaller groups based on the column.
• All the window functions that depend on ORDER BY in the OVER clause are non-deterministic. Refer Microsoft article on Deterministic and Nondeterministic Functions.
• ROW_NUMBER is only for output reference.

Syntax : 

ROW_NUMBER() 
OVER([PARTITION BY <col1>[,<col2>,...<n>]] ORDER BY <col3>[,<col4])

OVER: How the ranking should order or split the data

PARTITION BY: To split the data into groups/partition based on column

ORDER BY: Ordering of the data

Consider the below sample data:

CREATE TABLE EMPLOYEE(ENAME VARCHAR(45),EDEPART VARCHAR(45))
INSERT INTO EMPLOYEE(ENAME,EDEPART) 
VALUES 
('KRISHNA','IT'),('RAMKRISHNA','CIVIL'),('PRASAD','IT'),
('RAMESH','ACCOUNTS'),('MAHESH','MANAGEMENT'),('KALYAN','IT'),
('SATISH','ACCOUNTS'),('ASHOK','CIVIL'),('GANESH','MANAGEMENT')

ROW_NUMBER() Function without Partition By clause
Without Partition By clause SQL Server considered all records in the result-set as single partition or group and then ranking functions are applied.
Example

SELECT ROW_NUMBER()
    OVER (ORDER BY EDEPART,ENAME) AS ID,EDEPART,ENAME
FROM EMPLOYEE

Output:

ROW_NUMBER() Function with Partition By clause
With the PARTITION BY clause, a column or set of columns specified will divide the result set into record partitions. Then, finally ranking functions are applied to each record partition separately.
Example

SELECT ROW_NUMBER()
    OVER (PARTITION BY EDEPART
ORDER BY  EDEPART,ENAME) AS ID,EDEPART,ENAME
FROM EMPLOYEE

Output

Refer : https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql
Let me know if you have any questions, suggestions or comments.

Read More

Different ways to find the Nth max salary in SQL Server

It is the most common interview question to be asked for SQL developers. Finding Nth highest value/salary in a table, where N could be 2, 3, 4 or anything.

Read More