Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Concatinate the String with in single row Expand / Collapse
Author
Message
Posted Tuesday, August 24, 2010 11:49 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, March 30, 2014 9:34 PM
Points: 277, Visits: 1,179
HI all

I have a master table with item_no as one of column

Ex:
table name:Table1
ITem_no
1
2
3

Table name: Table 2
Item_no , format
1 Soap
1 pen
1 pencil
1 eraser
2 soft toys
2 adult toys
2 kids items

Now i want to merge the table with below format ,with single row as output

1 soap pen pencil eraser
2 soft toys adult toys kids items

or
1 soap, pen, pencil, eraser
2 soft toys,adult toys, kids items


Thnaks
in advance












Relationship table






Many Thanks!
S.saravanan
“I am a slow walker, but I never walk backwards-
Abraham Lincoln”
Post #974641
Posted Wednesday, August 25, 2010 12:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 9:33 AM
Points: 145, Visits: 28,753
create table #t1 (ITem_no int)
insert into #t1 (Item_no) values (1)
insert into #t1 (Item_no) values (2)
insert into #t1 (Item_no) values (3)

create table #t2 (ITem_no int, format varchar(16))
insert into #t2 (Item_no, format) values (1, 'Soap')
insert into #t2 (Item_no, format) values (1, 'Pen')
insert into #t2 (Item_no, format) values (1, 'Pencil')
insert into #t2 (Item_no, format) values (1, 'Eraser')
insert into #t2 (Item_no, format) values (2, 'Soft Toys')
insert into #t2 (Item_no, format) values (2, 'Soft Toys')
insert into #t2 (Item_no, format) values (2, 'Adult Toys')
insert into #t2 (Item_no, format) values (2, 'Kids Items')


select
t1.*,
(
select t2.format + ', '
from #t2 t2
where t2.Item_no = t1.Item_no
order by t2.format
for xml path('')
)
from #t1 t1



http://www.dwexplorer.com
@dtwegener
Post #974647
Posted Wednesday, August 25, 2010 4:54 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, March 30, 2014 9:34 PM
Points: 277, Visits: 1,179
Many thanks,



Many Thanks!
S.saravanan
“I am a slow walker, but I never walk backwards-
Abraham Lincoln”
Post #974734
Posted Wednesday, August 25, 2010 5:39 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:10 AM
Points: 551, Visits: 3,165
hi, i notice in the following link, for what i see as the same problem, they give a similar answer but wrap it with the STUFF function.
I've tested this on my database and there doesn't seem to be any difference in output.
I'm i just not seeing something or are they the same?

http://www.sqlservercentral.com/Forums/Topic973776-392-1.aspx
Post #974755
Posted Wednesday, August 25, 2010 6:56 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 03, 2013 8:24 AM
Points: 1,240, Visits: 5,421
The Stuff() formula is used to get rid of the extra comma.

Drew


J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Post #974812
Posted Wednesday, October 20, 2010 2:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:54 AM
Points: 34, Visits: 56
Hi Dirk Wegener,

It is nice solution....

Many -Many Thanks!!!
Post #1007521
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse