SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Help with STUFF function		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wibni
Starting Member

25 Posts
Posted - 09/26/2012 :  09:05:32  Show Profile  Reply with Quote
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

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 09/26/2012 :  10:27:26  Show Profile  Reply with Quote
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?
Go to Top of Page

wibni
Starting Member

25 Posts
Posted - 09/26/2012 :  21:58:58  Show Profile  Reply with Quote
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


       
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 09/27/2012 :  07:05:48  Show Profile  Reply with Quote
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);
Go to Top of Page

wibni
Starting Member

25 Posts
Posted - 09/27/2012 :  07:55:29  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 09/27/2012 :  08:08:13  Show Profile  Reply with Quote
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);
Go to Top of Page

wibni
Starting Member

25 Posts
Posted - 09/27/2012 :  08:57:28  Show Profile  Reply with Quote
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');
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 09/27/2012 :  09:11:26  Show Profile  Reply with Quote
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);
Go to Top of Page

wibni
Starting Member

25 Posts
Posted - 09/27/2012 :  10:20:39  Show Profile  Reply with Quote
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"
Go to Top of Page

wibni
Starting Member

25 Posts
Posted - 09/27/2012 :  11:01:29  Show Profile  Reply with Quote
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?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 09/27/2012 :  11:51:01  Show Profile  Reply with Quote
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
Go to Top of Page

wibni
Starting Member

25 Posts
Posted - 09/27/2012 :  21:36:35  Show Profile  Reply with Quote
Thank you.
Got it now.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 09/28/2012 :  06:59:25  Show Profile  Reply with Quote
Glad you got it figured out :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000