Help with STUFF function 1

[johnhugh]

Hi,

I'm using SQL server 2005.
I have below query and table.
I'm trying to get all rows into just one row by excluding everything that is NULL and showing the rig numbers (the numbers in the last column) one after another seperated with a "|".
I know it can be done as i got it before but in the below query something is wrong and I cannot figure it out.

Would anyone be able to advise?
Much appreciated.

SELECT i.FMTITEMNO,i.ITEMDESC,  STUFF
		((SELECT ' | ' + rtrim(g.ACSEGVAL03) 
				FROM   GLAMF AS g 
				WHERE ISNUMERIC(g.ACSEGVAL03) =1 AND i.GLACCT=g.ACCTFMTTD FOR XML PATH(''), TYPE ).value('.', 'varchar(max)'), 1, 3, '') AS RigNumber 
FROM  IFSHIPD AS i 
WHERE  i.FMTITEMNO='2-07-24-43-02748' --AND ISNUMERIC(g.ACSEGVAL03) =1

2-07-24-43-02748 | Fittings Poly 1 1/4" 50mm | NULL
2-07-24-43-02748 | Fittings Poly 1 1/4" 50mm | NULL
2-07-24-43-02748 | Fittings Poly 1 1/4" 50mm | NULL
2-07-24-43-02748 | Fittings Poly 1 1/4" 50mm | NULL
2-07-24-43-02748 | Fittings Poly 1 1/4" 50mm | 26
2-07-24-43-02748 | Fittings Poly 1 1/4" 50mm | 12
2-07-24-43-02748 | Fittings Poly 1 1/4" 50mm | 3
2-07-24-43-02748 | Fittings Poly 1 1/4" 50mm | NULL
2-07-24-43-02748 | Fittings Poly 1 1/4" 50mm | NULL
2-07-24-43-02748 | Fittings Poly 1 1/4" 50mm | 31
2-07-24-43-02748 | Fittings Poly 1 1/4" 50mm | 58
2-07-24-43-02748 | Fittings Poly 1 1/4" 50mm | 26
2-07-24-43-02748 | Fittings Poly 1 1/4" 50mm | 34
2-07-24-43-02748 | Fittings Poly 1 1/4" 50mm | 33
2-07-24-43-02748 | Fittings Poly 1 1/4" 50mm | NULL
2-07-24-43-02748 | Fittings Poly 1 1/4" 50mm | 3
2-07-24-43-02748 | Fittings Poly 1 1/4" 50mm | 10

 

[simian336]

post a data sample and structure from GLAMF and IFSHIPD

Simi

 

[johnhugh]

Hi simian,

Sample date below

CREATE TABLE GLAMF (ACCTFMTTD char(45), ACSEGVAL03 CHAR(15));

INSERT INTO GLAMF VALUES ('30-5210-098','098');
INSERT INTO GLAMF VALUES ('30-5210-CAI','CAI');
INSERT INTO GLAMF VALUES ('30-5210-SHA','SHA');
INSERT INTO GLAMF VALUES ('30-5210-SUE','SUE');
INSERT INTO GLAMF VALUES ('30-5210-SUG','SUG');
INSERT INTO GLAMF VALUES ('30-5210-SUK','SUK');
INSERT INTO GLAMF VALUES ('30-5210','');
INSERT INTO GLAMF VALUES ('30-5210-006','006');
INSERT INTO GLAMF VALUES ('30-5210-007','007');
INSERT INTO GLAMF VALUES ('30-5210-008','008');
INSERT INTO GLAMF VALUES ('30-5210-009','009');
INSERT INTO GLAMF VALUES ('30-5210-017','017');

CREATE TABLE IFSHIPD (FMTITEMNO CHAR(24),ITEMDESC CHAR(60), GLACCT CHAR(45));
INSERT INTO IFSHIPD VALUES ('2-07-21-35-02175','NQ Impreg Bit KS++5','30-5210-098');
INSERT INTO IFSHIPD VALUES ('2-07-21-35-02175','NQ Impreg Bit KS++5','30-5210-CAI');
INSERT INTO IFSHIPD VALUES ('2-07-21-35-02175','NQ Impreg Bit KS++5','30-5210-007');
INSERT INTO IFSHIPD VALUES ('2-07-21-35-02180','NQ2 Core Lifter NQ3h','30-5210-SUE');
INSERT INTO IFSHIPD VALUES ('2-07-21-35-02180','NQ2 Core Lifter NQ3h','30-5210-006');
INSERT INTO IFSHIPD VALUES ('2-07-21-35-02180','NQ2 Core Lifter NQ3h','30-5210-017');
INSERT INTO IFSHIPD VALUES ('2-07-21-35-02190','SAFETY GLASSES dark','30-5210-SUK');
INSERT INTO IFSHIPD VALUES ('2-07-21-35-02190','SAFETY GLASSES dark','38-5210-009');

 

[simian336]

This will produce your result from the top.

SELECT rtrim(i.FMTITEMNO) + ' | ' + rtrim(i.ITEMDESC) + ' | ' + g.ACSEGVAL03
FROM GLAMF g
join IFSHIPD i
on i.GLACCT=g.ACCTFMTTD

but add your where if you only want the numeric ones.

WHERE ISNUMERIC(g.ACSEGVAL03) =1

If that is not the result you want. Post what you want it to look like.

Simi

 

[johnhugh]

Sorry Simi. I might have been explaining myself wrong.

The result I'm after would look like this.
List each item number only ones with the the rig numbers at the end concatenating.
Rig numbers are either in GLAMF.ACSEGVAL03 or the last 3 characters of IFSHIPD.GLACCT.

"2-07-21-35-02175" "NQ Impreg Bit KS++5" "098 | 007"
"2-07-21-35-02180" "NQ2 Core Lifter NQ3h" "006 | 017"
"2-07-21-35-02190" "SAFETY GLASSES dark" "009

 

[johnhugh]

OK. I got somehting now but not sure whether this is the most efficient SQL statement.

CREATE TABLE IFSHIPD (FMTITEMNO VARCHAR(32),GLACCT VARCHAR(32));
INSERT INTO IFSHIPD VALUES ('2-07-21-35-02175','30-5210-098');
INSERT INTO IFSHIPD VALUES ('2-07-21-35-02175','30-5210-CAI');
INSERT INTO IFSHIPD VALUES ('2-07-21-35-02175','30-5210-007');
INSERT INTO IFSHIPD VALUES ('2-07-21-35-02180','30-5210-SUE');
INSERT INTO IFSHIPD VALUES ('2-07-21-35-02180','30-5210-006');
INSERT INTO IFSHIPD VALUES ('2-07-21-35-02180','30-5210-017');
INSERT INTO IFSHIPD VALUES ('2-07-21-35-02190','30-5210-SUK');
INSERT INTO IFSHIPD VALUES ('2-07-21-35-02190','38-5210-009');

SELECT  i.FMTITEMNO, STUFF
		((SELECT ' | ' + right(g.glacct,3)
		    FROM   ifshipd AS g 
		    WHERE ISNUMERIC(right(g.glacct,3)) =1 AND i.fmtitemno = g.fmtitemno FOR XML PATH(''), TYPE ).value('.', 'varchar(max)'), 1, 3, '') AS RigNumber 
FROM  IFSHIPD AS i 
order by rignumber

I'm only using one table IFSHIPD and getting these results.
Obviously still duplicate entries in there I need to get rid of.

"2-07-21-35-02180" "006 | 017"
"2-07-21-35-02180" "006 | 017"
"2-07-21-35-02190" "009"
"2-07-21-35-02190" "009"
"2-07-21-35-02175" "098 | 007"
"2-07-21-35-02175" "098 | 007"
"2-07-21-35-02175" "098 | 007"

What do you think?

 

[simian336]

You don't even need the second table.

SELECT DISTINCT i.FMTITEMNO, i.ITEMDESC,
STUFF((SELECT ' | ' + rtrim(substring(i2.glacct,9,3))
FROM IFSHIPD AS i2
WHERE i2.FMTITEMNO = i.FMTITEMNO
and isnumeric(substring(glacct,9,3))=1
FOR XML PATH('')), 1, 1, '') AS CODES
FROM IFSHIPD i
ORDER BY i.FMTITEMNO

Simi

 

[johnhugh]

Thanks simi. Got it now.