August 24, 2010 at 11:49 pm
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
August 25, 2010 at 12:16 am
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
August 25, 2010 at 4:54 am
Many thanks,
August 25, 2010 at 5:39 am
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
August 25, 2010 at 6:56 am
The Stuff() formula is used to get rid of the extra comma.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 20, 2010 at 2:29 am
Hi Dirk Wegener,
It is nice solution....
Many -Many Thanks!!!
July 11, 2014 at 11:28 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply