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: Monday, October 20, 2014 8:30 AM
Points: 280, Visits: 1,218
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: Tuesday, September 9, 2014 4:21 AM
Points: 145, Visits: 28,764
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: Monday, October 20, 2014 8:30 AM
Points: 280, Visits: 1,218
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: 2 days ago @ 5:47 AM
Points: 594, Visits: 3,495
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 3, 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: Tuesday, September 30, 2014 6:44 AM
Points: 38, Visits: 60
Hi Dirk Wegener,

It is nice solution....

Many -Many Thanks!!!
Post #1007521
Posted Friday, July 11, 2014 11:28 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 4:44 PM
Points: 267, Visits: 351
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

Post #1591744
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse