Today I had to accomplish the following task. I had to create a report that selects data from primary table and also selects data from a related table , comma delimited into a column on a report. I keep bumping into this problem periodically, but always have to research the solution. Today I am documenting the solution.
Example
I have the following database on my SQL Server:
USE [master]
GO
/****** Object: Database [RolodexEFCtp] Script Date: 01/17/2011 20:37:17 ******/
CREATE DATABASE [RolodexEFCtp] ON PRIMARY
( NAME = N’RolodexEFCtp’, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATARolodexEFCtp.mdf’ , SIZE = 2304KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’RolodexEFCtp_log’, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATARolodexEFCtp_log.LDF’ , SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [RolodexEFCtp] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled’))
begin
EXEC [RolodexEFCtp].[dbo].[sp_fulltext_database] @action = ‘enable’
end
GO
ALTER DATABASE [RolodexEFCtp] SET ANSI_NULL_DEFAULT OFF
GO
USE [RolodexEFCtp]
GO
/****** Object: Table [dbo].[Companies] Script Date: 01/17/2011 20:37:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Companies](
[CompanyId] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [nvarchar](40) NOT NULL,
[DateAdded] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[CompanyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[CompanyContacts] Script Date: 01/17/2011 20:37:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CompanyContacts](
[CompanyContactId] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](30) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[CompanyId] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[CompanyContactId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: ForeignKey [Company_Contacts] Script Date: 01/17/2011 20:37:19 ******/
ALTER TABLE [dbo].[CompanyContacts] WITH CHECK ADD CONSTRAINT [Company_Contacts] FOREIGN KEY([CompanyId])
REFERENCES [dbo].[Companies] ([CompanyId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CompanyContacts] CHECK CONSTRAINT [Company_Contacts]
GO
In the database above I would like to select company name in one column and semi-colon delimited list of employees. Something like:
I am going to accomplish this task by using XML functionality of SQL Server, specifically FOR XML clause.
Here is what my select looks like that accomplish this task:
SELECT Companies.CompanyName,
STUFF(
(SELECT ‘; ‘ + CompanyContacts.LastName + ‘, ‘ + CompanyContacts.FirstName
FROM CompanyContacts
WHERE CompanyContacts.CompanyId = Companies.CompanyId
FOR XML PATH(”)), 1, 1, ”) As AllContacts
FROM Companies
ORDER BY Companies.CompanyName
You can of course customize contacts query in a number of ways, sorting and filtering that data. Primarily, I want to illustrate that you can accomplish seemingly hard task with a single query.
Thanks.