CAN we concatinate all records of field

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 2005 Forums

Transact-SQL (2005)

CAN we concatinate all records of field

Author

Topic

SHIVPREET2K1
Starting Member

32 Posts

Posted - 2009-05-09 : 00:53:00

Dear friends

I have a query for my software. I want to concatinate all records for a particular criteria in one row. see the example


Item_Order_Table Has follwing records in buyer_code and item_code field

Buyer_Code               Item_Code
    A                       X
    B                       X
    C                       Y
    A                       Y 
    B                       Z
_____________________________________

now the output required is
Buyer_Code              Item_Code
 A,B                       X
 C,A                       Y
 B                         Z           
______________________________

I just wanted to know that how can i concatinate buyer_Code field with one query only. I can do this with my .net programming too but it will reduce the performance. so please guide me.

Thanks in advance

Shiv Preet

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-09 : 00:56:37

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-09 : 04:50:17

[code]
SELECT t.Item_Code,LEFT(bl.buylist,LEN(bl.buylist)-1)
FROM (SELECT DISTINCT Item_Code FROM Item_Order_Table) t
CROSS APPLY (SELECT Buyer_Code + ','
FROM Item_Order_table
WHERE Item_Code=t.Item_Code
FOR XML PATH(''))bl(buylist)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources