SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 comma separator
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

folumike
Starting Member

24 Posts

Posted - 07/23/2013 :  19:16:28  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 07/24/2013 :  00:38:59  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000