Looping in a stored proc to create one line...

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)

Looping in a stored proc to create one line...

Author

Topic

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2012-10-29 : 13:04:05

I am looking to build a stored procedure to do the following:

We have a table of products (tblProducts). Each product can have up to 10 items associated to it (tblItem). Each item may have insurance (tblinsurance) and if it does have insurance it may also have premium insurance (tblpremium).

I am looking to build a flat file using a stored procedure and just building a backbone for the loop right now. I need to print a line item for each product and the vendor we are sending this to wants to have 10 items even if null and the associated insurance and premium info.

For ex:

Product Item1 Insurance1 PremiumIns1 Item2 Inusurance2 PremiumIns2 Item3 Insurance3 PremiumIns3....

The looping is what I am trying to do. So I have to loop 10 times per Product for this file but I need it to all be on the same line.

Anyone have an example of something like this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-29 : 13:58:44

no need of looping. this is called crosstabing and can be done efficiently using set based techniques

Something like below should work (this is just a stub to start with as I dont have any info on your table columns, sample data etc)


SELECT p.ProductID,p.ProductName,
MAX(CASE WHEN Seq=1 THEN ins.InsuranceDescription END) AS Insurance1,
MAX(CASE WHEN Seq=1 THEN prm.PremiumInsuranceDescription END) AS PremiumIns1,
MAX(CASE WHEN Seq=2 THEN ins.InsuranceDescription END) AS Insurance2,
MAX(CASE WHEN Seq=2 THEN prm.PremiumInsuranceDescription END) AS PremiumIns2,
...
FROM(SELECT *,ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ItemID) AS Seq,
     FROM tblProducts p
     INNER JOIN tblProductItems pi
     ON pi.ProductID = p.ProductID
     INNER JOIN tblItem i
     ON i.IetmID = pi.ItemID
     LEFT JOIN tblInsurance ins
     ON ins.InsuranceID = i.InsuranceID
     LEFT JOIN tblpremium prm
     ON prm.PremiumInsuranceID = i.PremiumInsuranceID
    WHERE p.Vendor= @YourVendor
)t
GROUP BY p.ProductID,p.ProductName

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-29 : 14:00:50

if you want make it dynamic to handle any number of insurances you can extend it like below using dynamic sql

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

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

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2012-10-29 : 17:21:56

Thanks Visakh16, that gets me off to a good start.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-29 : 19:02:01

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources