Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Concatinate the String with in single row


Concatinate the String with in single row

Author
Message
Saravanan_tvr
Saravanan_tvr
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 1347
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”
Dirk Wegener
Dirk Wegener
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 28774
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
Saravanan_tvr
Saravanan_tvr
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 1347
Many thanks,

Many Thanks!
S.saravanan
“I am a slow walker, but I never walk backwards-
Abraham Lincoln”
davidandrews13
davidandrews13
SSC Eights!
SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)

Group: General Forum Members
Points: 824 Visits: 4450
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
drew.allen
drew.allen
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2658 Visits: 9932
The Stuff() formula is used to get rid of the extra comma.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
meet2aftab
meet2aftab
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 70
Hi Dirk Wegener,

It is nice solution....

Many -Many Thanks!!!
Ed Pollack
Ed Pollack
SSC-Addicted
SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)

Group: General Forum Members
Points: 474 Visits: 513
So I love a good challenge, and my first thought here was...how can we do this without loops or XML? Here is a solution that appears to work...let me know what you think! I give myself an A+ for creativity at least : )

This SQL uses a recursive CTE to build a comma-delimited list and ensure we only get back the final results of the work. It is not as efficient at the moment as the XML solution (I didn't take time to optimize it), but certainly we could do more to make it speedier:



;WITH CTE_ITEM_NUMBERS AS (
SELECT
ROW_NUMBER() OVER (ORDER BY #t2.Item_no) AS row_num,
#t2.Item_no,
#t2.format,
1 AS level
FROM #t2 ),
CTE_RECURSIVE_ITEMS AS (
SELECT
CTE_ITEM_NUMBERS.row_num,
CTE_ITEM_NUMBERS.Item_no,
CAST(CTE_ITEM_NUMBERS.format AS VARCHAR(MAX)) AS all_formats,
CTE_ITEM_NUMBERS.level
FROM CTE_ITEM_NUMBERS
UNION ALL
SELECT
CTE_ITEM_NUMBERS.row_num,
CTE_ITEM_NUMBERS.Item_no,
CAST(CTE_ITEM_NUMBERS.format AS VARCHAR(MAX)) + ', ' + CAST(CTE_RECURSIVE_ITEMS.all_formats AS VARCHAR(MAX)) AS all_formats,
CTE_RECURSIVE_ITEMS.level + 1
FROM CTE_ITEM_NUMBERS
INNER JOIN CTE_RECURSIVE_ITEMS
ON CTE_RECURSIVE_ITEMS.Item_no = CTE_ITEM_NUMBERS.Item_no
WHERE CTE_ITEM_NUMBERS.row_num <> CTE_RECURSIVE_ITEMS.row_num
AND CTE_RECURSIVE_ITEMS.row_num > CTE_ITEM_NUMBERS.row_num
)
SELECT
*
FROM CTE_RECURSIVE_ITEMS
WHERE level = (SELECT MAX(level) FROM CTE_RECURSIVE_ITEMS REC2 WHERE REC2.Item_no = CTE_RECURSIVE_ITEMS.Item_no)
ORDER BY CTE_RECURSIVE_ITEMS.Item_no, CTE_RECURSIVE_ITEMS.row_num


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search