September 29, 2009 at 8:36 am
I have six different locations(location1,location2,location3,location4,location5,location6) for the same business.
All these six locations are stored on six diff columns. I need to concatinate these six columns to and need to have a comma betweeen each location.
I can have null for any of the locations.
Say for bussiness1, location1 and location5 could be null and the other four locations can have some valid address and for business2. location1 ,location2 and location 6 can be null.
How can I do this?
Thanks in advance.
September 29, 2009 at 8:46 am
This code will comma-delimit all locations and take NULLS into account (changing them into zero-length strings). The outer query takes the concatenated string, and trims off the trailing ', ' from the string as when any value appears a comma is consistently added to the end.
SELECT businessid, LEFT(location_string, LEN(location_string) - 1) AS location_string
FROM(
SELECT
businessid,
CASE WHEN bl.location1 IS NOT NULL THEN bl.location1 + ', ' ELSE '' END +
CASE WHEN bl.location2 IS NOT NULL THEN bl.location2 + ', ' ELSE '' END +
CASE WHEN bl.location3 IS NOT NULL THEN bl.location3 + ', ' ELSE '' END +
CASE WHEN bl.location4 IS NOT NULL THEN bl.location4 + ', ' ELSE '' END +
CASE WHEN bl.location5 IS NOT NULL THEN bl.location5 + ', ' ELSE '' END +
CASE WHEN bl.location6 IS NOT NULL THEN bl.location6 + ', ' ELSE '' END AS location_string
FROM dbo.business_locations bl) t1
MJM
September 29, 2009 at 8:51 am
The other way to do this is to use isnull function.
select isnull(col1,'') + isnull(col2,'') + isnull(col3,'')+ isnull(col4,'')+ isnull(col5,'')+ isnull(col6,'')
from MyTable
September 29, 2009 at 9:01 am
Hi Pradeep,
I need to have a comma between each location. Mark's idea was very close to my requirement .
Thank you very much for your help.
September 29, 2009 at 10:23 am
sql_novice_2007 (9/29/2009)
Hi Pradeep,I need to have a comma between each location. Mark's idea was very close to my requirement .
Thank you very much for your help.
Pradeep's method can also use a comma between each with only a slight addition.
select isnull(col1 + ',','') + isnull(col2 + ',','') + isnull(col3 + ',','')+ isnull(col4 + ',','')+ isnull(col5 + ',','')+ isnull(col6 + ',','')
from MyTable
August 30, 2011 at 10:55 pm
Here we go...
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TEST1](
[a1] [varchar](10) NULL,
[a2] [varchar](10) NULL,
[a3] [varchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[TEST1] ([a1], [a2], [a3]) VALUES (N'A', N'B', N'C')
INSERT [dbo].[TEST1] ([a1], [a2], [a3]) VALUES (N'A', N'B', NULL)
INSERT [dbo].[TEST1] ([a1], [a2], [a3]) VALUES (N'A', NULL, N'C')
INSERT [dbo].[TEST1] ([a1], [a2], [a3]) VALUES (NULL, N'C', N'D')
INSERT [dbo].[TEST1] ([a1], [a2], [a3]) VALUES (NULL, NULL, N'D')
INSERT [dbo].[TEST1] ([a1], [a2], [a3]) VALUES (N'A', N'B', NULL)
INSERT [dbo].[TEST1] ([a1], [a2], [a3]) VALUES (N'A', NULL, NULL)
INSERT [dbo].[TEST1] ([a1], [a2], [a3]) VALUES (NULL, NULL, NULL)
INSERT [dbo].[TEST1] ([a1], [a2], [a3]) VALUES (N'ABS', N'', N'D')
INSERT [dbo].[TEST1] ([a1], [a2], [a3]) VALUES (N' ', N'', N'D')
INSERT [dbo].[TEST1] ([a1], [a2], [a3]) VALUES (N' AA ', N'AAA ', N'')
INSERT [dbo].[TEST1] ([a1], [a2], [a3]) VALUES (N' TRT ', N'', NULL)
GO
//Code
WITH c
AS
(
SELECT COALESCE(NULLIF(LTRIM(RTRIM(a1)),''),'') +
COALESCE(ISNULL(',' + NULLIF(LTRIM(RTRIM(a2)),'') ,''),'')+
COALESCE(ISNULL(','+ NULLIF(LTRIM(RTRIM(a3)),''),''),'') as comma
from TEST1
)
select comma=
CASE
WHEN CHARINDEX(',',comma)=1 THEN STUFF(comma,1,1,'')
ELSE
comma
END
FROM c
Prabhu
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply