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)
 Looping in a stored proc to create one line...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 10/29/2012 :  13:04:05  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 10/29/2012 :  13:58:44  Show Profile  Reply with Quote
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/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/29/2012 :  14:00:50  Show Profile  Reply with Quote
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/

Go to Top of Page

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 10/29/2012 :  17:21:56  Show Profile  Reply with Quote
Thanks Visakh16, that gets me off to a good start.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/29/2012 :  19:02:01  Show Profile  Reply with Quote
welcome

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

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.06 seconds. Powered By: Snitz Forums 2000