Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 comma separator

Author  Topic 

folumike
Starting Member

24 Posts

Posted - 2013-07-23 : 19:16:28
I have a table in MSSQL 2000 as shown below:
table name: tblinvoice

input
ItemID | InvID | Amount |
_____________________________________________
001 | INV001 | 2000 |
001 | INV002 | 3000 |
001 | INV003 | 1000 |
002 | INV004 | 2000 |
003 | INV005 | 3000 |
003 | INV006 | 1000 |
004 | | 3000 |



output:
ItemID | InvID | Amount |
_________________________________________________
001 | INV001,INV002,INV003 | 6000 |
002 | INV004 | 2000 |
003 | INV005,INV006 | 4000 |
004 | | 3000 |

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-07-24 : 00:38:59
You can write DF to get comma separated values...

CREATE FUNCTION CombineValues
(
@Item_ID varchar(5)-- The foreign key from TableA which is used
-- to fetch corresponding records
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @SomeColumnList VARCHAR(8000);

SELECT @SomeColumnList =
COALESCE(@SomeColumnList + ', ', '') + CAST(InvID AS varchar(20))
FROM tblinvoice C
WHERE C.ItemID = @Item_ID;

RETURN
(
SELECT @SomeColumnList
)
END


SELECT ItemID, dbo.CombineValues(ItemID), SUM(amount) FROM tblinvoice
group by ItemID, dbo.CombineValues(ItemID)


--
Chandu
Go to Top of Page
   

- Advertisement -