SQL Concatenation: Concatenate Phone Numbers of a Customer
In this t-sql tutorial, I want to show how t-sql concatenation can be used to create customer phone numbers list as home phone number, office phone number and the possible available rest phones in the name of others.
If you want to concatenate phone numbers of customers for a number of more than 1, then it is better to manage this task in a single select statement instead of using user defined functions.
To accomplish with the difficult task, I code in t-sql using the new enhancements of Microsoft SQL Server 2005 and SQL Server 2008.
For example if you look for the given t-sql solution of this sql problem, you will see that SQL CTE (Common Table Expression), ROW_NUMBER Over Partition By tsql structure with t-sql concatenation using FOR XML PATH and t-sql aggregation functions MAX() using Group By and CASE statements are used.
But it's better to start with creating sample data for sql concatenation than dealing with the tsql solution first.
Create demo sql table and populate sql table with sample data using MS SQL Server 2008 new Insert syntax.
The first part of the sql script includes a CREATE TABLE t-sql command.
And the second part of the below tsql script includes the INSERT INTO table t-sql command.
And the last select is for displaying all data that we insert into sample sql table.
CREATE TABLE CustomerPhones (
Id Int Identity(1,1),
Customer Nvarchar(100),
Phone Nvarchar(25)
)
GO
INSERT INTO CustomerPhones
VALUES
('A','9848403211'),
('A','9812675432'),
('A','9112356788'),
('A','9876503276'),
('B','9567890765'),
('B','9876098567')
GO
SELECT * FROM CustomerPhones
Below t-sql developers can see how is the sample data we have created for t-sql concatenation of phone numbers as home phone, office phone and others.
The sql question for the above sql data is displaying the first two phone numbers in two separate columns for the customer A.
And displaying the other two phone numbers of Customer A in a third column.
T-SQL developers and SQL Server administrations can use the below sql statement as the t-sql solution of this sql problem.
This tsql script uses T-SQL CTE (Common Table Expressions) for storing data temporarily and referencing this CTE data for more than once.
Again there exists a t-sql ranking function that is called SQL Window Functions - ROW NUMBER().
Of course ROW_NUMBER() function is used with Partition By clause to have the solution or ranking per customer based.
There is also transact-sql concatenation statement which uses the FOR XML PATH('') t-sql concatenation method which I had tried to explain in my previous sql tutorial : SQL Concatenation - Get column values as comma separated list using XML PATH() instead of UDF's using SQL COALESCE
I have also used the CASE WHEN THEN END t-sql case statement for distributing the selected phone numbers into approtiate column values.
SQL developers can realize that this kind of a usage of CASE WHEN statement is just like using it like a SQL PIVOT Select statement.
You know Pivot tables can be described as changing rows into columns in short.
And here is the sample sql select statement which I offer for the solution of this sql problem.
WITH CTE AS
(
SELECT
Customer,
rn = ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Id),
Phone
FROM CustomerPhones
)
SELECT
Customer,
Home,
Office,
STUFF(
(
SELECT
',' + Phone
FROM CTE CTE2
WHERE rn > 2
AND CTE2.Customer = Phones.Customer
FOR XML PATH('')
), 1, 1, '') Other
FROM (
SELECT
Customer,
MAX(Home) Home,
MAX(Office) Office
FROM (
SELECT
Customer,
Home = CASE WHEN rn = 1 THEN Phone ELSE NULL END,
Office = CASE WHEN rn = 2 THEN Phone ELSE NULL END
FROM CTE
WHERE rn <= 2
) List
GROUP BY Customer
) Phones
And the output of this sql select statement where the first two phone numbers of each customer is considered as home and office telephone numbers.
And the remaining phone numbers if there exist any are concatenated using tsql concatenation methods and considered as other phone numbers.
I hope sql developers and Microsoft SQL Server Administrators find this sql batch useful for their different kinds of sql requirements ad sql problems.