Hi, I'm working on SQL Server 20 1

johnhugh · Jul 2, 2012

Hi,

I'm working on SQL Server 2005.
I have the 2 tables below, 1 containing item numbers and description, the other one item numbers and supplier numbers.
Some item numbers can have more than 1 supplier number.
How would I be able to bring all suppliers on 1 row, as shown in my results table?

With a join I get a lot of duplicate item numbers and a subquery doesn't work because it cannot have more than 1 return value.

SELECT ICITEM.ITEMNO, ICITEM.[DESC],
(SELECT MANITEMNO FROM ICIOTH WHERE (ITEMNO = ICITEM.ITEMNO)) AS MANUFACTURERS_NUMBER
FROM ICITEM INNER JOIN ICITMV ON ICITEM.ITEMNO = ICITMV.ITEMNO

<html><head><title>Excel Jeanie HTML</title></head><body><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:145px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; ">ItemNo</td><td style="font-weight:bold; ">ItemDesc</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">1</td><td >Item 1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">2</td><td >Item 2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">3</td><td >Item 3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">4</td><td >Item 4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-weight:bold; ">ItemNo</td><td style="font-weight:bold; ">SupplierNo</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">1</td><td >Supplier 1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">2</td><td >Supplier 1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">2</td><td >Supplier 2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">3</td><td >Supplier 4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">4</td><td >Supplier 3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">4</td><td >Supplier 4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-weight:bold; ">Result</td><td style="font-weight:bold; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-weight:bold; ">ItemNo</td><td style="font-weight:bold; ">SupplierNo</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">1</td><td >Supplier 1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">2</td><td >Supplier 1 / Supplier 2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">3</td><td >Supplier 4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:right; ">4</td><td >Supplier 3 / Supplier 4</td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="

http://www.excel-jeanie-html.de/index.php?f=1"

target="_blank"> Excel Jeanie HTML 4 </a>

</body></html>

Savil · Jul 2, 2012

Oooh Yuk!

The only way to do this would be with a function where the itemNumber is passed to the function and a concatenated string of values is returned.

SELECT DISTINCT ICITEM.ITEMNO, yourfuncionname(ICITEM.ITEMNO)
FROM ICITEM

This would call the function within the select statement

The function would recieve the itemno and pull all distinct suppliers against the itemno and concatenate using a loop.

r937 · Jul 2, 2012

faq183-2146
faq183-6466
faq183-6485

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

imex · Jul 2, 2012

Hi,

Try something like that:

Code:

select i.ItemNo, STUFF( (SELECT ' / ' + s.SupplierNo
                         FROM ItemSupplier as s
                         WHERE s.ItemNo = i.ItemNo
                         FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
                        ,1, 3, '')
from Item as i

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]

johnhugh · Jul 2, 2012

Thanks all.

Imex - your statement works wonderful!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Hi, I'm working on SQL Server 20 1

johnhugh

Technical User

Savil

Programmer

r937

Technical User

imex

Programmer

johnhugh

Technical User

Similar threads

Part and Inventory Search

Sponsor