SQL Server : Displaying child records in a single column as CSV format in SQL Server

Yesterday, I was asked by one of my team member, how to display the child records as comma separated values in a single column.

I would like to share the query which I have suggested him as it is often required to display data as a single column,especially data from child tables.

Let us create some sample records for this example:

CREATE TABLE [DBO].[Country]
(
[CountryID] INT IDENTITY(1,1),
[CountryName] VARCHAR(100)
)
GO

CREATE TABLE [DBO].[State]
(
[StateID] INT IDENTITY(1,1),
[StateName] VARCHAR (100),
[CountryID] INT
)
GO

Also, will insert few records into these tables.

INSERT INTO [DBO].[Country] ([CountryName]) VALUES ('India'),('United States'),('Australia')
GO

INSERT INTO [DBO].[State] ([StateName],[CountryID]) VALUES ('Andhra Pradesh',1),('Tamil Nadu',1),('Maharashtra',1),('Kerala',1),('Karnataka',1)
GO

INSERT INTO [DBO].[State] ([StateName],[CountryID]) VALUES ('California',2),('New York',2),('Texas',2),('Washington',2)
GO

INSERT INTO [DBO].[State] ([StateName],[CountryID]) VALUES ('New South Wales',3),('Tasmania',3),('Victoria',3)
GO

First, let us execute the below queries and check the records :

SELECT [CountryID],[CountryName] FROM [DBO].[Country]
GO

SELECT [StateID],[StateName],[CountryID] FROM [DBO].[State]
GO

Hope, the above statements were executed successfully. J

Now, let's get to the real situation, suppose we need to display state names in CSV format for “India”.

The following query can be used to display the state names in a single column.

SELECT
ISNULL(STUFF(
(

SELECT

', ' + [StateName]
FROM
(
SELECT
[StateName]
FROM
[DBO].[State]
WHERE
[State].[CountryID] = 1
)
AS [State] FOR XML PATH('')
)
,1,1,''),'') AS [StateName]

GO

The output would be :

The above query satisfying only for single country right?
So, let us write a script to display all the state names for all the countries using correlated sub-query.

SELECT
      [Country].[CountryID],[Country].[CountryName],
      ISNULL(STUFF(
(
SELECT

', ' + [StateName]
FROM
(
SELECT
[StateName]
FROM
[DBO].[State]
WHERE
[State].[CountryID] = [Country].[CountryID]
)
AS [State] FOR XML PATH('')
)
,1,1,''),'') AS [StateName]
FROM
      [DBO].[Country]
GO

The output would be :

I hope, this article may have helped you to understand, how to display the records in CSV format and also you can find more.

Note:

The above queries will work only in SQL Server 2005 and above versions as 'FOR XML PATH' was introduced in SQL Server 2005.

SQL Server : Keyboard Shortcuts for SQL Server Management Studio (SSMS)

When I was surfing the net yesterday, I found the SSMS query window keyboard shortcuts from www.simple-talk.com

I just felt the need to share with you how easily we can navigate the options in SSMS (SQL Server Management Studio) which also save our time.

Note :

You can also refer http://msdn.microsoft.com/en-us/library/ms174205.aspx

SQL Server : Capturing multiple IDENTITY values after inserting into SQL Server table

Usually we will have situation where we need to capture IDENTITY values after inserting into table, normally this can be achieved by using SCOPE_IDENTITY(), @@IDENTITY and IDENT_CURRENT() functions.

IDENT_CURRENT: returns the last identity value generated for a specific table in any session and any scope.
@@IDENTITY: returns the last identity value generated for any table in the current session, across all scopes.
SCOPE_IDENTITY : returns the last identity value generated for any table in the current session and the current scope.

The above functions are used to retrieve IDENTITY value for single record inserts.J

But, do you think these functions will satisfy all our requirements? My answer is NO, since they will not support for multiple inserts.

Nothing to worry, we have OUTPUT clause to capture the new IDENTITY values for a batch of inserted records which is explained below:

First, let us create sample table with few records.

CREATE TABLE [DBO].[Employee]
(
[EmployeeID] INT IDENTITY(1,1),
[EmployeeName] VARCHAR(100)
)
GO

INSERT INTO [DBO].[Employee] ([EmployeeName]) VALUES ('Vamsi Priya');
INSERT INTO [DBO].[Employee] ([EmployeeName]) VALUES ('Praveen');
INSERT INTO [DBO].[Employee] ([EmployeeName]) VALUES ('Sushma');
INSERT INTO [DBO].[Employee] ([EmployeeName]) VALUES ('Radhika');
INSERT INTO [DBO].[Employee] ([EmployeeName]) VALUES ('Harini');
INSERT INTO [DBO].[Employee] ([EmployeeName]) VALUES ('Chaitanya');

The same insertion can be done in SQL Server 2008 as below :

INSERT INTO [DBO].[Employee] ([EmployeeName])
VALUES
('Vamsi Priya'),('Praveen'),('Sushma'),('Radhika'),('Harini'),('Chaitanya')
GO

After inserting the records, let us execute and check what is the last [EmployeeID] using the below query.

SELECT @@IDENTITY
GO

The output would be 6. Still we are not satisfied with the result as we got only last inserted employee id, but not all the IDs which were inserted in the transaction.

This can be achieved using the below query :

Before this let us remove all the records from [Employee] table.

TRUNCATE TABLE [DBO].[Employee]
GO

As we know, TRUNCATE TABLE removes all rows from a table, but the table structure and columns remains. If the table contains an identity column, the counter for that column is reset to the seed value defined for the column.

DECLARE @EMPTABLE TABLE ([EmployeeID] INT,[EmployeeName] VARCHAR(100));

INSERT INTO [DBO].[Employee]
(
[EmployeeName]
)
OUTPUT INSERTED.[EmployeeID],INSERTED.[EmployeeName]
INTO @EMPTABLE ([EmployeeID],[EmployeeName])
SELECT 'Vamsi Priya' UNION ALL
SELECT 'Praveen'     UNION ALL
SELECT 'Sushma'      UNION ALL
SELECT 'Radhika'     UNION ALL
SELECT 'Harini'      UNION ALL
SELECT 'Chaitanya'
GO

Now, let us see the output :

SELECT [EmployeeID],[EmployeeName] FROM @EMPTABLE
GO

From the above example, you could understand how we can capture the multiple identity values. J

Note :

1. I have used TABLE variable to capture the records, even TEMP TABLE also be used in place of TABLE variables.
2. You can use this statement inside your stored procedure and also the result can be used for any other calculations within SP.
3. This feature is available in SQL Server 2005 and above versions.

SQL Server : Using CASE expression in an ORDER BY clause

Many times, I used to hear the question from my folks that “How can I use CASE in an ORDER BY clause?”

Let me show you, how CASE expression can be used in an ORDER BY clause to determine the sort order of the rows based on a given column value.

Example 1 :

Let us assume that we need to sort [CityName] based on @ORDER variable when we set to 1, the result set should sort by [CityName] in ascending order and @ORDER set to 2 should be in descending order.

Normal result without order by clause would be :

SELECT [CityID],[CityName] FROM [DBO].[City]

I have modified the above statement slightly to make ORDER BY based on the condition which is shown below:

DECLARE @ORDER TINYINT
SET @ORDER = 1
SELECT [CityID],[CityName]
FROM [DBO].[City]
ORDER BY
CASE WHEN @ORDER = 1 THEN [CityName] END ASC,
CASE WHEN @ORDER = 2 THEN [CityName] END DESC

Let us execute the above statement and see the output.

From the above result set, we could identify that the records were sorted by [CityName] in ascending order as @ORDER value is 1.

Now, let us change @ORDER value to 2 (descending order) and see the result.

DECLARE @ORDER TINYINT
SET @ORDER = 2
SELECT [CityID],[CityName]
FROM [DBO].[City]
ORDER BY
CASE WHEN @ORDER = 1 THEN [CityName] END ASC
CASE WHEN @ORDER = 2 THEN [CityName] END DESC
GO

As expected, the result set has been sorted by [CityName] in descending order.

Example 2 :

The same CASE expression can also be used to sort columns based on conditions. See the below example.

DECLARE @ColumnOrder VARCHAR
SET @ColumnOrder = 'CityName'

SELECT [CityID],[CityName]
FROM [DBO].[City]
ORDER BY
CASE WHEN @ColumnOrder = 'CityName' THEN[CityName] END ASC,
CASE WHEN @ColumnOrder = 'CityID' THEN [CityID]END ASC
GO

The same statement can be used in Stored Procedure as given below :

CREATE PROCEDURE [DBO].[STP_City]
(
@ColumnOrder VARCHAR(10)
)
AS
SELECT [CityID],[CityName]
FROM [DBO].[City]
ORDER BY
CASE WHEN @ColumnOrder = 'CityName' THEN[CityName] END ASC,
CASE WHEN @ColumnOrder = 'CityID' THEN [CityID]END ASC
GO

EXECUTE [DBO].[STP_City] 'CityName'
GO

Note : For this example, I have used only variables, even column names also be used in place of variables.