Help with STUFF function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with STUFF function

Author

Topic

wibni
Starting Member

31 Posts

Posted - 2012-09-26 : 09:05:32

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-26 : 10:27:26

Is the data that you posted the output from the current query, or is it the raw data in the table? You refer to two tables in the query, can you post some sample data from each table?

wibni
Starting Member

31 Posts

Posted - 2012-09-26 : 21:58:58

Hi Sunitabeck.
The table is the output of my query.

A sample of the 2 original tables is below.
Appreciate your help!

GLAMF
ACCTFMTTD | ACSEGVAL03
30-5120-098 | 098
30-5120-CAI | CAI
30-5120-SHA | SHA
30-5120-SUE | SUE
30-5120-SUG | SUG
30-5120-SUK | SUK
30-5130 |
30-5130-006 | 006
30-5130-007 | 007
30-5130-008 | 008
30-5130-009 | 009
30-5130-017 | 017

IFSHIPD
FMTITEMNO | ITEMDESC | GLACCT
2-07-21-35-02175 | NQ Impreg Bit KS++5 | 30-5210-018
2-07-21-35-02175 | NQ Impreg Bit KS++5 | 30-5210-017
2-07-21-35-02175 | NQ Impreg Bit KS++5 | 30-5210-007
2-07-21-35-02184 | NQ2 9H Bit HQ678Atd | 60-5210-023
2-07-21-03-01889 | NQ2 Core Lifter NQ3h | 60-5210-023
2-07-21-03-01723 | CORE LIFTER, SLOT g | 20-5210-003
3-10-31-56-20056 | Rain Coats and Pants | 20-5342-KAN
3-10-31-46-02492 | SAFETY GLASSES dark | 38-5342-075

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-27 : 07:05:48

See if the following query gives you the results you are looking for?

SELECT
	i.FMTITEMNO,
	i.ITEMDESC,
	STUFF(b.RigNumber , 1, 1, '') AS RigNumber 
FROM
	IFSHIPD i
	OUTER APPLY
	(
		SELECT 
		(
		   SELECT '|' AS [text()],
				  b.ACSEGVAL03  AS [text()]
		   FROM   GLAMF            b
		   WHERE  b.ACCTFMTTD = a.GLACCT
				  -- AND ISNUMERIC(b.ACSEGVAL03) = 1
				  FOR XML PATH(''),TYPE
		 ).value('.', 'varchar(max)')
	) b(RigNumber);

wibni
Starting Member

31 Posts

Posted - 2012-09-27 : 07:55:29

Sadly not.
Same results as with my query.

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-27 : 08:08:13

In your sample data at least, there are no ACCTFMTTD values in the GLAMF table that is in the GLACCT values in the IFSHIPD column, or vice versa. I thought I had done something wrong in my query, but when I created the test data from your posting and tested, I see no commonality. See the two select queries below:

CREATE TABLE #GLAMF (ACCTFMTTD varchar(32), ACSEGVAL03 VARCHAR(32));

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

CREATE TABLE #IFSHIPD (FMTITEMNO VARCHAR(32),ITEMDESC VARCHAR(32), GLACCT VARCHAR(32));
INSERT INTO #IFSHIPD VALUES ('2-07-21-35-02175','NQ Impreg Bit KS++5','30-5210-018');
INSERT INTO #IFSHIPD VALUES ('2-07-21-35-02175','NQ Impreg Bit KS++5','30-5210-017');
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-02184','NQ2 9H Bit HQ678Atd','60-5210-023');
INSERT INTO #IFSHIPD VALUES ('2-07-21-03-01889','NQ2 Core Lifter NQ3h','60-5210-023');
INSERT INTO #IFSHIPD VALUES ('2-07-21-03-01723','CORE LIFTER, SLOT g','20-5210-003');
INSERT INTO #IFSHIPD VALUES ('3-10-31-56-20056','Rain Coats and Pants','20-5342-KAN');
INSERT INTO #IFSHIPD VALUES ('3-10-31-46-02492','SAFETY GLASSES dark','38-5342-075');

SELECT * FROM #IFSHIPD WHERE GLACCT IN (SELECT ACCTFMTTD FROM #GLAMF);
SELECT * FROM #GLAMF WHERE ACCTFMTTD IN (SELECT GLACCT FROM #IFSHIPD);

wibni
Starting Member

31 Posts

Posted - 2012-09-27 : 08:57:28

True. I see what you're saying.
My sample data was no good.

Can't get your SQL statement to work with updated sample data either though.

CREATE TABLE #GLAMF (ACCTFMTTD varchar(32), ACSEGVAL03 VARCHAR(32));

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 VARCHAR(32),ITEMDESC VARCHAR(32), GLACCT VARCHAR(32));
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-02175','NQ2 9H Bit HQ678Atd','30-5210-SUE');
INSERT INTO #IFSHIPD VALUES ('2-07-21-35-02175','NQ2 Core Lifter NQ3h','30-5210-006');
INSERT INTO #IFSHIPD VALUES ('2-07-21-35-02175','CORE LIFTER, SLOT g','30-5210-017');
INSERT INTO #IFSHIPD VALUES ('2-07-21-35-02175','Rain Coats and Pants','30-5210-SUK');
INSERT INTO #IFSHIPD VALUES ('2-07-21-35-02175','SAFETY GLASSES dark','38-5210-009');

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-27 : 09:11:26

Run this code and you will see that it works. However, you will notice that there is only one rig number for each row. That is because the data is such that there is only one rig number per FMTITEMNO. If you insert more data into the #GLAMF table (see my second query below) and run the select again, you will see that they are concatenating the rig numbers.


-- QUERY 1IF OBJECT_ID('tempdb..#GLAMF') IS NOT NULL DROP TABLE #GLAMF;
CREATE TABLE #GLAMF (ACCTFMTTD varchar(32), ACSEGVAL03 VARCHAR(32));

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');

IF OBJECT_ID('tempdb..#IFSHIPD') IS NOT NULL DROP TABLE #IFSHIPD;
CREATE TABLE #IFSHIPD (FMTITEMNO VARCHAR(32),ITEMDESC VARCHAR(32), GLACCT VARCHAR(32));
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-02175','NQ2 9H Bit HQ678Atd','30-5210-SUE');
INSERT INTO #IFSHIPD VALUES ('2-07-21-35-02175','NQ2 Core Lifter NQ3h','30-5210-006');
INSERT INTO #IFSHIPD VALUES ('2-07-21-35-02175','CORE LIFTER, SLOT g','30-5210-017');
INSERT INTO #IFSHIPD VALUES ('2-07-21-35-02175','Rain Coats and Pants','30-5210-SUK');
INSERT INTO #IFSHIPD VALUES ('2-07-21-35-02175','SAFETY GLASSES dark','38-5210-009');

SELECT
	i.FMTITEMNO,
	i.ITEMDESC,
	STUFF(b.RigNumber , 1, 1, '') AS RigNumber 
FROM
	#IFSHIPD i
	OUTER APPLY
	(
		SELECT 
		(
		   SELECT '|' AS [text()],
				  b.ACSEGVAL03  AS [text()]
		   FROM   #GLAMF            b
		   WHERE  b.ACCTFMTTD = i.GLACCT
				  -- AND ISNUMERIC(b.ACSEGVAL03) = 1
				  FOR XML PATH(''),TYPE
		 ).value('.', 'varchar(max)')
	) b(RigNumber);

-- QUERY 2
IF OBJECT_ID('tempdb..#GLAMF') IS NOT NULL DROP TABLE #GLAMF;
CREATE TABLE #GLAMF (ACCTFMTTD varchar(32), ACSEGVAL03 VARCHAR(32));

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');


INSERT INTO #GLAMF VALUES ('30-5210-098','TEST1');
INSERT INTO #GLAMF VALUES ('30-5210-CAI','TEST2');
INSERT INTO #GLAMF VALUES ('30-5210-SHA','TEST3');

IF OBJECT_ID('tempdb..#IFSHIPD') IS NOT NULL DROP TABLE #IFSHIPD;
CREATE TABLE #IFSHIPD (FMTITEMNO VARCHAR(32),ITEMDESC VARCHAR(32), GLACCT VARCHAR(32));
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-02175','NQ2 9H Bit HQ678Atd','30-5210-SUE');
INSERT INTO #IFSHIPD VALUES ('2-07-21-35-02175','NQ2 Core Lifter NQ3h','30-5210-006');
INSERT INTO #IFSHIPD VALUES ('2-07-21-35-02175','CORE LIFTER, SLOT g','30-5210-017');
INSERT INTO #IFSHIPD VALUES ('2-07-21-35-02175','Rain Coats and Pants','30-5210-SUK');
INSERT INTO #IFSHIPD VALUES ('2-07-21-35-02175','SAFETY GLASSES dark','38-5210-009');

SELECT
	i.FMTITEMNO,
	i.ITEMDESC,
	STUFF(b.RigNumber , 1, 1, '') AS RigNumber 
FROM
	#IFSHIPD i
	OUTER APPLY
	(
		SELECT 
		(
		   SELECT '|' AS [text()],
				  b.ACSEGVAL03  AS [text()]
		   FROM   #GLAMF            b
		   WHERE  b.ACCTFMTTD = i.GLACCT
				  -- AND ISNUMERIC(b.ACSEGVAL03) = 1
				  FOR XML PATH(''),TYPE
		 ).value('.', 'varchar(max)')
	) b(RigNumber);

wibni
Starting Member

31 Posts

Posted - 2012-09-27 : 10:20:39

Thanks a lot for your help!
I think I'm explaining myself wrong.

Starting to think I might not even need both tables and can remove GLAMF.

Lets make it a bit simpler.

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');

With above data, how can I get the below result?

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

wibni
Starting Member

31 Posts

Posted - 2012-09-27 : 11:01:29

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?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-27 : 11:51:01

That looks fine. You can get rid of the duplicates by joining with distinct FMTITEMNO from IFSHIPD rather than IFSHIPD itself as shown below:

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  ( SELECT DISTINCT FMTITEMNO FROM IFSHIPD ) AS i 
order by rignumber

wibni
Starting Member

31 Posts

Posted - 2012-09-27 : 21:36:35

Thank you.
Got it now.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-28 : 06:59:25

Glad you got it figured out :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources