SOLVED - How to concatenate column values of type text during SELECT...FROM...GROUP BY? [modified]
-
I'd like to create a SQL statement which would do such thing: Having a table/query of these columns: number of type integer text of type text (string) I'd like execute such a SQL Statement: SELECT number,concatenate(text) FROM table GROUP BY number Resulting rows should contain concatenation of every value in column "text" for each number value. Is it possible at all?
modified on Thursday, December 23, 2010 5:55 AM
-
I'd like to create a SQL statement which would do such thing: Having a table/query of these columns: number of type integer text of type text (string) I'd like execute such a SQL Statement: SELECT number,concatenate(text) FROM table GROUP BY number Resulting rows should contain concatenation of every value in column "text" for each number value. Is it possible at all?
modified on Thursday, December 23, 2010 5:55 AM
liquid_ wrote:
Is it possible at all?
Yes, That is possible. Suppose Consider AdventureWorks DB First create UDF fn_Join as Below,
USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[fn_Join](@managerID int)
returns varchar(max)
AS
BEGIN
DEclare @ret varchar(max)
SELECT @ret = SUBSTRING(
(SELECT ',' + s.Title
FROM HumanResources.Employee s
WHERE ManagerID = @managerID
FOR XML PATH('')),2,200000)
RETURN(@ret)
ENDThat will give you a concatenated string of all the Title by giving ManagerID as a INPUT. Now write the query to fetch all the distinct ManagerID and it's corresponding concatenated Title
select distinct ManagerID,dbo.fn_Join(ManagerID)
from HumanResources.Employee
WHERE ManagerID is not nullThat's will give you a desired result.
Regards, Hiren.
My Recent Article: - Way to know which control have raised a postback
My Recent Tip/Trick: - Remove HTML Tag, get plain Text -
liquid_ wrote:
Is it possible at all?
Yes, That is possible. Suppose Consider AdventureWorks DB First create UDF fn_Join as Below,
USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[fn_Join](@managerID int)
returns varchar(max)
AS
BEGIN
DEclare @ret varchar(max)
SELECT @ret = SUBSTRING(
(SELECT ',' + s.Title
FROM HumanResources.Employee s
WHERE ManagerID = @managerID
FOR XML PATH('')),2,200000)
RETURN(@ret)
ENDThat will give you a concatenated string of all the Title by giving ManagerID as a INPUT. Now write the query to fetch all the distinct ManagerID and it's corresponding concatenated Title
select distinct ManagerID,dbo.fn_Join(ManagerID)
from HumanResources.Employee
WHERE ManagerID is not nullThat's will give you a desired result.
Regards, Hiren.
My Recent Article: - Way to know which control have raised a postback
My Recent Tip/Trick: - Remove HTML Tag, get plain Text -
Thanks but I'm afraid this does not work with MS Access. There is no straight way to create function or procedure or I don't know about it.
You could've mentioned MS ACCESS at a database.
liquid_ wrote:
There is no straight way to create function or procedure or I don't know about it.
I also don't know as I've not worked much on ACCESS. :)
Regards, Hiren.
My Recent Article: - Way to know which control have raised a postback
My Recent Tip/Trick: - Remove HTML Tag, get plain Textmodified on Thursday, December 23, 2010 2:52 AM
-
liquid_ wrote:
Is it possible at all?
Yes, That is possible. Suppose Consider AdventureWorks DB First create UDF fn_Join as Below,
USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[fn_Join](@managerID int)
returns varchar(max)
AS
BEGIN
DEclare @ret varchar(max)
SELECT @ret = SUBSTRING(
(SELECT ',' + s.Title
FROM HumanResources.Employee s
WHERE ManagerID = @managerID
FOR XML PATH('')),2,200000)
RETURN(@ret)
ENDThat will give you a concatenated string of all the Title by giving ManagerID as a INPUT. Now write the query to fetch all the distinct ManagerID and it's corresponding concatenated Title
select distinct ManagerID,dbo.fn_Join(ManagerID)
from HumanResources.Employee
WHERE ManagerID is not nullThat's will give you a desired result.
Regards, Hiren.
My Recent Article: - Way to know which control have raised a postback
My Recent Tip/Trick: - Remove HTML Tag, get plain Text