-- Create and populate tables used in this demo
IF EXISTS(SELECT name FROM sys.tables WHERE name='customer')
DROP TABLE customer
GO
IF EXISTS(SELECT name FROM sys.tables WHERE name='customer_type')
DROP TABLE customer_type
GO
-- Create the tables
CREATE TABLE customer_type(
id int PRIMARY KEY NOT NULL,
type varchar(50))
CREATE TABLE customer (
id int identity(1,1) PRIMARY KEY NOT NULL,
ct_id int REFERENCES customer_type(id),
name varchar(50))
ALTER TABLE customer
ADD CONSTRAINT fk_customer_customer_type
FOREIGN KEY (ct_id)
REFERENCES customer_type(id)
INSERT INTO customer_type (id, type)
VALUES (1, 'Movie Characters'), (2, 'Rock Stars'), (3, 'Cartoon Characters')
INSERT INTO customer (ct_id, name )
VALUES
(1, 'Johnny B. Goode'), (1,'Buckaroo Banzai'), (1,'Marty McFly'),
(2, 'Rick Springfield'), (2,'Bono'), (2,'Sammy Hagar'),
(3, 'Wile E. Coyote'), (3,'Officer Barbrady'), (3,'Sweet Polly Purebred'), (3,'Bart Simpson')
-- Show the tables
SELECT * FROM customer
SELECT * FROM customer_type
-- XML PATH returns a single row with all values, separated by comma space
SELECT ', ' + convert(varchar(50), name)
FROM customer
FOR XML PATH('')
-- STUFF removes the first comma space and formats this with ,
SELECT DISTINCT STUFF((
SELECT ', ' + convert(varchar(50), c2.name)
FROM customer c2
FOR XML PATH('')), 1, 1, '')
FROM customer c1
-- THIS WORKS
SELECT DISTINCT ct.type,
STUFF(
(SELECT ', ' + convert(varchar(50), c2.name)
FROM customer c2
WHERE c1.ct_id = c2.ct_id
FOR XML PATH('') ), 1, 2, '') as names
FROM customer c1
JOIN customer_type ct ON c1.ct_id = ct.id
ORDER BY ct.type
-- Throw the above results into a temp table
IF OBJECT_ID('tempdb..#c') IS NOT NULL
DROP TABLE #c
GO
CREATE TABLE #c (type varchar(100), names varchar(1000))
INSERT INTO #c (type, names)
SELECT DISTINCT ct.type,
STUFF(
(SELECT ', ' + convert(varchar(50), c2.name)
FROM customer c2
WHERE c1.ct_id = c2.ct_id
FOR XML PATH('') -- SELECT it as XML
), 1, 2, '') -- This is done to remove the first comma-space (,) FROM the result
as names
FROM customer c1
JOIN customer_type ct ON c1.ct_id = ct.id
ORDER BY ct.type
-- Add a few more just for kicks and giggles
INSERT INTO #c (type, Names)
VALUES
('Actors', 'Kevin Costner, Meg Ryan, Peter Weller'),
('Athletes', 'Michael Jordan, Bo Jackson'),
('Fictional Characters', 'Santa Clause, The Easter Bunny, The Tooth Fairy')
-- Show the results
SELECT * FROM #c
So here's the new starting point
-- Create and populate tables used in this demo
IF EXISTS(SELECT name FROM sys.tables WHERE name='customer')
DROP TABLE customer
GO
IF EXISTS(SELECT name FROM sys.tables WHERE name='customer_type')
DROP TABLE customer_type
GO
-- Create the tables
CREATE TABLE customer_type(
id int PRIMARY KEY NOT NULL,
type varchar(50))
CREATE TABLE customer (
id int identity(1,1) PRIMARY KEY NOT NULL,
ct_id int REFERENCES customer_type(id),
name varchar(50))
ALTER TABLE customer
ADD CONSTRAINT fk_customer_customer_type
FOREIGN KEY (ct_id)
REFERENCES customer_type(id)
INSERT INTO customer_type (id, type)
VALUES (1, 'Movie Characters'), (2, 'Rock Stars'), (3, 'Cartoon Characters')
INSERT INTO customer (ct_id, name )
VALUES
(1, 'Johnny B. Goode'), (1,'Buckaroo Banzai'), (1,'Marty McFly'),
(2, 'Rick Springfield'), (2,'Bono'), (2,'Sammy Hagar'),
(3, 'Wile E. Coyote'), (3,'Officer Barbrady'), (3,'Sweet Polly Purebred'), (3,'Bart Simpson')
-- Show the tables
SELECT * FROM customer
SELECT * FROM customer_type
-- XML PATH returns a single row with all values, separated by comma space
SELECT ', ' + convert(varchar(50), name)
FROM customer
FOR XML PATH('')
-- STUFF removes the first comma space and formats this with ,
SELECT DISTINCT STUFF((
SELECT ', ' + convert(varchar(50), c2.name)
FROM customer c2
FOR XML PATH('')), 1, 1, '')
FROM customer c1
-- THIS WORKS
SELECT DISTINCT ct.type,
STUFF(
(SELECT ', ' + convert(varchar(50), c2.name)
FROM customer c2
WHERE c1.ct_id = c2.ct_id
FOR XML PATH('') ), 1, 2, '') as names
FROM customer c1
JOIN customer_type ct ON c1.ct_id = ct.id
ORDER BY ct.type
-- Throw the above results into a temp table
IF OBJECT_ID('tempdb..#c') IS NOT NULL
DROP TABLE #c
GO
CREATE TABLE #c (type varchar(100), names varchar(1000))
INSERT INTO #c (type, names)
SELECT DISTINCT ct.type,
STUFF(
(SELECT ', ' + convert(varchar(50), c2.name)
FROM customer c2
WHERE c1.ct_id = c2.ct_id
FOR XML PATH('') -- SELECT it as XML
), 1, 2, '') -- This is done to remove the first comma-space (,) FROM the result
as names
FROM customer c1
JOIN customer_type ct ON c1.ct_id = ct.id
ORDER BY ct.type
-- Add a few more just for kicks and giggles
INSERT INTO #c (type, Names)
VALUES
('Actors', 'Kevin Costner, Meg Ryan, Peter Weller'),
('Athletes', 'Michael Jordan, Bo Jackson'),
('Fictional Characters', 'Santa Clause, The Easter Bunny, The Tooth Fairy')
-- Show the results
SELECT * FROM #c
-- THIS WORKS convert the set back to normalized
;with tmp(type, names1, names2) as (
SELECT
type,
CAST(LTRIM(LEFT(Names, CHARINDEX(',', Names + ',') -1 )) as varchar(100)),
CAST(STUFF(Names, 1, CHARINDEX(',', Names + ','), '') as varchar(1000))
FROM #c
UNION ALL
SELECT
type,
CAST(LTRIM(LEFT(names2, CHARINDEX(',', names2 + ',') -1)) as varchar(100)),
CAST(STUFF(names2, 1, CHARINDEX(',', names2 + ','), '') as varchar(1000))
FROM tmp
where names2 > ''
)
SELECT type, names1 as names
FROM tmp
ORDER BY type
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (1)
Author
Commented: