combining rows in table

[vlitim]

I have a table that has two columns

tagName value
tag2 50
tag2 100
testdescription Here is some info!!

and i need to get the data out like:

tagName value
tag2 50,100
testdescription Here is some info!!

is this possible??

Cheers

Tim

 

[rdandrade]

Yes...here is a function to try. You can adapt it for your use. The purpose of this function is to show a list of Benefits that a bank offers in a database for one of my customers. Call it with ', ' as the parameter for it to return a comma seperated list.

First it fills the working table with a unique list of BanksIDs and a text benefit strings. The Update statement changes the working table into a comma delimted list. It then does a group by to return a unique Bank and Benefits lists.

While this is an example with my data, it should be easy to change this to work with your data.

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE  FUNCTION dbo.fn_ImplementationBenefitsList
	(
	@SpacingChar nvarchar(100)=NULL
	)
RETURNS @ReturnTable TABLE(BankFK int NOT NULL, BenefitList nvarchar(500) NULL)
AS
	BEGIN

DECLARE @WorkingTable TABLE(
BankFK 		int	NOT NULL,
Benefit		nvarchar(50) NULL,
BenefitsList	nvarchar(500) NULL
)

IF @SpacingChar IS NULL
	Select @SpacingChar = CHAR(13) + CHAR(10)

INSERT INTO @WorkingTable (BankFK, Benefit)
SELECT BankFK, Value
FROM Benefits INNER JOIN StringMap
	ON Benefits.BenefitsFK=StringMap.AttributeID
WHERE AttributeName=N'BenePlans'
ORDER BY BankFK, DisplayOrder

declare
    @list nvarchar(500),
    @lasti int

select
    @list = '',
    @lasti = -1


update
    @workingtable
set
    @list = BenefitsList = case
                       when @lasti <> BankFK then Benefit
                       else @list + @SpacingChar + Benefit
                   end,
    @lasti = BankFK

INSERT INTO @ReturnTable
select
    BankFK, Max(BenefitsList) AS BenefitsList
from
    @WorkingTable
group by
    BankFK


	RETURN
	END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Ray D'Andrade

http://www.raydandrade.com

 

[vlitim]

fantastic thanks for that I would never have gone in that direction!!

much appreciated