October 21, 2009 at 12:12 am
I have a query that successfully concatenates the names of all animals belonging to one customer into a string, which I then use in an application for a variety of tasks.
UPDATE dbo.customerDetails
SET petNames = stuff(( select ', '+ petName from petDetails
WHERE customerID = c.customerID for xml path('')),1,1,'')
FROM customerDetails c
The output currently looks like this for a customer with 3 animals - "Woofy, Smelly, Birdy"
One of the tasks the variable is used for is generating an email - and I would like to present these names 'nicely' or more grammatically correct, like this: "Woofy, Smelly & Birdy" (note the inserted ampasand).
If I was concatenating only two names it would, of course, be easy. But as one customer can (and often does) have 'n' number of pets, I don't know how to do it - or indeed if it is possible at all.
Here's some test data if it will help.
table.customerDetails
custID customerNamepetNames
1Smith
2Jones
3Bertram
4 Betty
table.petDetails
petIDcustIDpetName
11Woofy
21Smelly
32Huffle
43Blue
53Shrek
64Barker
And of course, if anyone can suggest a better/cleaner/more efficent/more professional/simpler method of achieving then objective - then triple virtual Malt Whisky all round!
Many thanks
Nick
October 21, 2009 at 2:30 am
Hi,
This would be my approach...
Create a function that modifies the string to include the desired '&' and then modify the table as below:
USE [YourDBName]
GO
/****** Object: UserDefinedFunction [dbo].[PrettyPetNames] Script Date: 10/21/2009 10:23:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[PrettyPetNames] (@Customer int)
RETURNS varchar(500)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @PetCount TINYINT
SELECT @PetCount = COUNT(PetName) FROM PetDetails WHERE CustID = @Customer
DECLARE @LastName VARCHAR(50)
SELECT TOP(1)@LastName = PetName FROM PetDetails WHERE CustID = @Customer ORDER BY PetID DESC
DECLARE @PrettyString VARCHAR(500)
SELECT @PrettyString =(STUFF((SELECT ', '+ petName
FROM petDetails
WHERE custID = c.custID for xml path('')), 1, 1, '') )
FROM customerDetails c
WHERE CustID = @Customer
DECLARE @Output VARCHAR(500)
SET @Output = ''
IF (@PetCount) > 1
BEGIN
SELECT @Output = LTRIM(STUFF(@PrettyString, CHARINDEX(',', @PrettyString, (LEN(@PrettyString)-(LEN(@LastName)+1))), 1, ' &' ))
END
ELSE
SELECT @Output = PetName FROM PetDetails WHERE CustID = @Customer
Return @Output
END
GO
--And then, I would modify the table definition to look something like this:
USE YourDBName
GO
/****** Object: Table [dbo].[CustomerDetails] Script Date: 10/21/2009 10:25:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CustomerDetails](
[CustID] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] [varchar](25) NULL,
[PetNames] AS ([dbo].[PrettyPetNames]([CustID])),
CONSTRAINT [PK_CustomerDetails] PRIMARY KEY CLUSTERED
(
[CustID] 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
SET ANSI_PADDING OFF
GO
I've used a calculated column to avoid having to run the update.
I'm sure there may be a far more elegant way of doing this and I'd love to see it.
October 21, 2009 at 10:43 pm
Thank you, thank you, thank you!!!!
Works brilliantly.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply