Loop the PID match hireachy ID sum qty

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 2012 Forums

Transact-SQL (2012)

Loop the PID match hireachy ID sum qty

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2013-12-27 : 20:10:57

Hi All,

I got table:
ID Qty PID
131 1 121
132 1 120
133 1 131
134 1 133
135 1 132
136 1 134

Output
______

If I set
PID=121
Sum Qty => 4 (131,133,134,136)

PID=133
Sum Qty => 2 (134,136)

PID=120
Sum Qty => 2 (132,135)

How can I do the loop SP refer to output above?

Please advise.

Thank you.

Regards,
Micheale

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-28 : 01:38:27

you need to use recursive CTE as below


CREATE PROC GetTotalQty
@PID int
AS

;With CTE
AS
(
SELECT ID,Qty,PID,CAST(ID AS varchar(max)) AS [Path]
FROM Table
WHERE PID = @PID
UNION ALL
SELECT t.ID,t.Qty,t.PID,CAST(c.[Path] + ',' + CAST(t.ID AS varchar(50)) AS varchar(max))
FROM CTE c
INNER JOIN Table t
ON t.PID = c.ID
)

SELECT @PID AS PID,SUM(Qty) AS Total,
MAX([path]) AS MaxPath
FROM CTE
OPTION (MAXRECURSION 0)

GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources