STUFF function not working - 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)

STUFF function not working

Author

Topic

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2011-07-05 : 14:25:49

Hello All,

Here is my table structure and code. I need to concatinate Name if the LegType is same for a strategyID. The code is not working as expected. If the LegType = 1 then concatinate courier names as Courier1 and if LegType = 2 then in Courier2 column. My code doesn't work properly. Should I use Row_NUMBER along with STUFF?

Please help...


StrategyID   LegType    Name
12           1           PSI      
14           1           PSI      
14           1           Fed Ex
14           2            DHL      
13           1           Local Courier      

Correct results-
StrategyID   Corier1              Courier2 
12          PSI      
14          PSI/Fed Ex            DHL
13          Local Courier

SQL-
SELECT 
        A1.StrategyID
        ,A1.LegTypeID
        ,STUFF(
				(SELECT '/' + B.Name FROM CourierDetail A WHERE A.StrategyID = A1.StrategyID AND A.LegTypeID = 1
					FOR XML PATH('')
				)
					, 1, 1, '') as Courier1 
        ,STUFF(
				(SELECT '/' + B.Name FROM CourierDetail A WHERE A.StrategyID = A1.StrategyID AND A.LegTypeID = 2
						FOR XML PATH('')
				)
					, 1, 1, '') as Courier2 
FROM 
        CourierDetail A1 
        INNER JOIN CourierHeader B 
          ON A1.CourierID = B.CourierID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-05 : 14:30:59

[code]
SELECT
t.StrategyID
,t.LegTypeID
,STUFF(
(SELECT '/' + B.Name FROM CourierDetail A WHERE A.StrategyID = t.StrategyID AND A.LegTypeID = 1
FOR XML PATH('')
)
, 1, 1, '') as Courier1
,STUFF(
(SELECT '/' + B.Name FROM CourierDetail A WHERE A.StrategyID = t.StrategyID AND A.LegTypeID = 2
FOR XML PATH('')
)
, 1, 1, '') as Courier2
FROM
(SELECT DISTINCT StrategyID
FROM CourierDetail A1
INNER JOIN CourierHeader B
ON A1.CourierID = B.CourierID)t

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2011-07-05 : 14:34:43

Thank you visakh. Will this code show the results I have my question?
I got an Error -
Msg 207, Level 16, State 1, Line 3
Invalid column name 'LegTypeID'.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "B.Name" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "B.Name" could not be bound.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-05 : 14:41:15

[code]SELECT
A1.StrategyID
,A1.LegTypeID
,STUFF(
(SELECT '/' + A.Name FROM CourierDetail A WHERE A.StrategyID = A1.StrategyID AND A.LegTypeID = 1
FOR XML PATH('')
)
, 1, 1, '') as Courier1
,STUFF(
(SELECT '/' + A.Name FROM CourierDetail A WHERE A.StrategyID = A1.StrategyID AND A.LegTypeID = 2
FOR XML PATH('')
)
, 1, 1, '') as Courier2
FROM CourierHeader A1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-05 : 14:46:48

[code]
Declare @CourierDetail table (
StrategyId int,
LegTypeId int,
CourierId int
Primary Key (StrategyId, LegTypeId, CourierId)
)
Declare @CourierHeader table (
CourierId int,
Name varchar(15),
Primary Key (CourierId)
)

Insert Into @CourierHeader Select 1, 'PSI'
Insert Into @CourierHeader Select 2, 'Fed Ex'
Insert Into @CourierHeader Select 3, 'DHL'
Insert Into @CourierHeader Select 4, 'Local Courier'

Insert Into @CourierDetail Select 12, 1, 1
Insert Into @CourierDetail Select 14, 1, 1
Insert Into @CourierDetail Select 14, 1, 2
Insert Into @CourierDetail Select 14, 2, 3
Insert Into @CourierDetail Select 13, 1, 4

/*
Correct results-
StrategyID Corier1 Courier2
12 PSI
14 PSI/Fed Ex DHL
13 Local Courier
*/

SELECT
A1.StrategyID
,A1.LegTypeID
,convert(varchar(30),STUFF(
(SELECT '/' + B.Name FROM @CourierDetail A WHERE A.StrategyID = A1.StrategyID AND A.LegTypeID = 1
FOR XML PATH('')
)
, 1, 1, '')) as Courier1
,convert(varchar(30),STUFF(
(SELECT '/' + B.Name FROM @CourierDetail A WHERE A.StrategyID = A1.StrategyID AND A.LegTypeID = 2
FOR XML PATH('')
)
, 1, 1, '')) as Courier2
FROM
@CourierDetail A1
INNER JOIN @CourierHeader B
ON A1.CourierID = B.CourierID

/*
StrategyID LegTypeID Courier1 Courier2
----------- ----------- ------------------------------ ------------------------------
12 1 PSI NULL
13 1 Local Courier NULL
14 1 PSI/PSI PSI
14 1 Fed Ex/Fed Ex Fed Ex
14 2 DHL/DHL DHL
*/

-- Query Starts here

;with ByLegType As (
Select
StrategyId,
LegTypeId,
Courier = convert(varchar(30),stuff(Courier,1,1,''))
From (Select Distinct StrategyId, LegTypeId From @CourierDetail) O
Cross Apply
(
Select '/'+H.Name
From @CourierDetail I
Inner Join @CourierHeader H
On I.CourierId = H.CourierId
Where I.StrategyId = O.StrategyId
and I.LegTypeId = O.LegTypeId
For Xml Path('')
) A(Courier)
)

Select
A.StrategyId,
A.LegTypeId,
Courier1 = A.Courier,
Courier2 = B.Courier
From ByLegType A
Left Join ByLegType B
On A.StrategyId = B.StrategyId
and 2 = B.LegTypeId
Where A.LegTypeId = 1

/*
StrategyId LegTypeId Courier1 Courier2
----------- ----------- ------------------------------ ------------------------------
12 1 PSI NULL
13 1 Local Courier NULL
14 1 PSI/Fed Ex DHL
*/
[/code]

Corey

I Has Returned!!

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2011-07-05 : 14:52:32

A1 table doesn't have StrategyID and LegTypeID columns. Those are in CourierDetail. Only the Name is in Header. So this SQL gives me bunch of errors.

This is the SQL after correction but the results are not correct.


SELECT 
        A1.StrategyID
        ,A1.LegTypeID
        ,STUFF(
				(SELECT '/' + B.Name FROM CourierDetail A WHERE A.StrategyID = A1.StrategyID AND A.LegTypeID = 1
					FOR XML PATH('')
				)
					, 1, 1, '') as Courier1 
        ,STUFF(
				(SELECT '/' + B.Name FROM CourierDetail A WHERE A.StrategyID = A1.StrategyID AND A.LegTypeID = 2
						FOR XML PATH('')
				)
					, 1, 1, '') as Courier2 
FROM CourierHeader B
 INNER JOIN CourierDetail A1
                        ON A1.CourierID = B.CourierID

Results-
StrategyID  LegType  Courier1       Courier2
12	    1	     PSI	    NULL
14	    1	     PSI/PSI	    PSI
14	    1	     Fed Ex/Fed Ex  Fed Ex
14	    2	     DHL/DHL	    DHL
13	    1	     Local Courier  NULL

Can you help?

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2011-07-05 : 14:59:31

Thank you Seventhnight. Your solution was perfect.

One more thing, I have to return ImportLicenseTypes, Broker Names and LeadTime - for InvoiceApproval, OrderGeneration etc based on LegTypes. And this information is in different tables. How do I put together SQL or change your SQL to implement this?

Can you give me some hints / pseudocode please?

Thanks much for your help,
-P

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-05 : 15:08:35

I would wrap the part I gave you in a CTE and do any further joins past that:


;with ByLegType As (
Select 
	StrategyId,
	LegTypeId,
	Courier = convert(varchar(30),stuff(Courier,1,1,''))
From (Select Distinct StrategyId, LegTypeId From @CourierDetail) O
Cross Apply
	(
	Select '/'+H.Name
	From @CourierDetail I
	Inner Join @CourierHeader H
	On I.CourierId = H.CourierId
	Where I.StrategyId = O.StrategyId
	and I.LegTypeId = O.LegTypeId
	For Xml Path('')
	) A(Courier)
)

, Couriers As (
Select 
	 A.StrategyId,
	 LegTypeId1 = A.LegTypeId,
	 Courier1 = A.Courier,
	 LegTypeId2 = B.LegTypeId,
	 Courier2 = B.Courier
From ByLegType A
Left Join ByLegType B
On A.StrategyId = B.StrategyId
and 2 = B.LegTypeId
Where A.LegTypeId = 1
)

Select *
From Couriers A
Left Join SomeOtherTable B
On A.LegTypeId1 = B.LegTypeId
...

Corey

I Has Returned!!

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2011-07-05 : 15:17:26

Should I create separate CTE's for each shipping criteria (Brokers, LicenseTypes, LeadTime as mentioned above etc.) since most of these have LegType (1 and 2) and need similar format of result. And then write a final select joining all these CTEs.

Would that work?

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2011-07-05 : 15:19:20

OR should I include other criteria after Courier field in the first CTE?

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2011-07-05 : 16:08:49

Thanks Seventhnight.

How do I change the query if I want Courier in different row if there are more than 1 for a StrategyID and same legType?

Should look like this-


StrategyId  LegTypeId   Courier1                       Courier2
----------- ----------- ------------------------------ ------------------------------
12          1           PSI                            NULL
13          1           Local Courier                  NULL
14          1           PSI                            DHL
14          1           Fed Ex                         NULL

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-05 : 16:09:35

Sounds like it might be simplest to put it in the first CTE with Courier...
But... if you do alot of Cross Applying, the query is going to get nasty. At some point, it will probably be best to break it up into individual CTEs, table variables, or temp tables... It just depends on the type and quantity of data.

I'd start with the 1st CTE though, and review the performance.

Corey

I Has Returned!!

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2011-07-05 : 16:12:10

Thanks Seventhnight.

What if I do want Courier in different row if there are more than 1 couriers for a StrategyID and same legType?

Should look like this-


StrategyId  LegTypeId   Courier1                       Courier2
----------- ----------- ------------------------------ ------------------------------
12          1           PSI                            NULL
13          1           Local Courier                  NULL
14          1           PSI                            DHL
14          1           Fed Ex                         NULL

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-05 : 16:15:21

quote:
Originally posted by sqlpal2007

Thanks Seventhnight.

How do I change the query if I want Courier in different row if there are more than 1 for a StrategyID and same legType?

Should look like this-

StrategyId LegTypeId Courier1 Courier2
----------- ----------- ------------------------------ ------------------------------
12 1 PSI NULL
13 1 Local Courier NULL
14 1 PSI DHL
14 1 Fed Ex NULL


;with CTE1 As (
Select 
	A.StrategyId,
	A.LegTypeId,
	Courier = B.Name,
	RowId = ROW_NUMBER() Over(Partition By A.StrategyId, A.LegTypeId Order By A.CourierId)
From @CourierDetail A
Left Join @CourierHeader B
On A.CourierId = B.CourierId
)

Select 
	A.StrategyId,
	A.RowId,
	Courier1 = A.Courier,
	Courier2 = B.Courier
From CTE1 A
Left Join CTE1 B
On A.StrategyId = B.StrategyId
and A.RowId = B.RowId
and 2 = B.LegTypeId
Where A.LegTypeId = 1

Corey

I Has Returned!!

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2011-07-05 : 17:15:57

Hi Seventhnight - This is how I changed the query to add Courier and Broker together. But due to the code in RED the output is not correct.

How would I change it avoiding writting multiple CTEs?


;with CTE1 As (
Select 
	CD.StrategyId,
	CD.LegTypeId as CourierLegType,
	Courier = CH.Name,
	RowIdCourier = ROW_NUMBER() Over(Partition By CD.StrategyId, CD.LegTypeId Order By CD.Choice),
	BD.LegTypeId as BrokerLegType,
	Brokers = BH.Name,
	RowIdBroker = ROW_NUMBER() Over(Partition By BD.StrategyId, BD.LegTypeId Order By BD.Choice)
From #Strategy S
LEFT JOIN CourierDetail CD ON S.StrategyID = CD.StrategyID
INNER JOIN CourierHeader CH ON CD.CourierID = CH.CourierID
LEFT JOIN BrokerDetail BD ON S.StrategyID = BD.StrategyID
INNER JOIN BrokerHeader BH ON BD.BrokerIORID = BH.BrokerIORID
	AND BH.BrokerIORType = 'B'
)

Select 
	A.StrategyId,
	A.RowIdCourier,
	CourierLegType1 = A.Courier,
	CourierLegType2 = B.Courier,
	A.CourierLegType,
	A.RowIdBroker,
	BrokerLegType1 = A.Brokers,
	BrokerLegType2 = B.Brokers,
	A.BrokerLegType
From CTE1 A
Left Join CTE1 B
On A.StrategyId = B.StrategyId
and A.RowIdCourier = B.RowIdCourier
 and A.RowIdBroker = B.RowIdBroker 
and 2 = B.CourierLegType
 and 2 = B.BrokerLegType 
Where A.CourierLegType = 1
 and A.BrokerLegType = 1

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2011-07-05 : 17:18:49

I think I got it by writting LEFT JOIN to CTE1 for Broker.

Is that correct?


;with CTE1 As (
Select 
	CD.StrategyId,
	CD.LegTypeId as CourierLegType,
	Courier = CH.Name,
	RowIdCourier = ROW_NUMBER() Over(Partition By CD.StrategyId, CD.LegTypeId Order By CD.Choice),
	BD.LegTypeId as BrokerLegType,
	Brokers = BH.Name,
	RowIdBroker = ROW_NUMBER() Over(Partition By BD.StrategyId, BD.LegTypeId Order By BD.Choice)
From #Strategy S
LEFT JOIN CourierDetail CD ON S.StrategyID = CD.StrategyID
INNER JOIN CourierHeader CH ON CD.CourierID = CH.CourierID
LEFT JOIN BrokerDetail BD ON S.StrategyID = BD.StrategyID
INNER JOIN BrokerHeader BH ON BD.BrokerIORID = BH.BrokerIORID
	AND BH.BrokerIORType = 'B'
)

Select 
	A.StrategyId,
	A.RowIdCourier,
	CourierLegType1 = A.Courier,
	CourierLegType2 = B.Courier,
	A.CourierLegType,
	A.RowIdBroker,
	BrokerLegType1 = A.Brokers,
	BrokerLegType2 = B.Brokers,
	A.BrokerLegType
From CTE1 A
Left Join CTE1 B
On A.StrategyId = B.StrategyId
and A.RowIdCourier = B.RowIdCourier
and 2 = B.CourierLegType
Left Join CTE1 C
ON A.RowIdBroker = C.RowIdBroker
and 2 = C.BrokerLegType
Where A.CourierLegType = 1
and A.BrokerLegType = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources