Turn 2 Rows into 1 1

[RonRepp]

Hi all:

I have a SQL that may bring out as many as 10 rows per customer, but want it to be one line.

I can easily extract the Cust and then join on an ID in a temp table, but how do I get it when it loops through to go to the field? e.g. ProductID, ProductID1, ProductID2...

SELECT     tblAutoShipment.AutoshipmentID, tblAutoShipment.BaseOrderNumber, tblSource.DNIS, tblAdKey.AdKey, tblAutoShipment.TimesAlreadyShipped, 
                      tblAutoShipment.CustomerID, tblAutoShipment.DontShipAgain, CSActivity.ActivityName, CONVERT(nvarchar(25), CSEvent.EventDate, 101) 
                      AS eventdate1, CSActivity.ActivityID, tblAutoShipment.InitialOrderDate, tblSequence.SequenceID, tblAutoshipProducts.AutoItemID, 
                      tblSequence.Description, tblAutoshipProducts.SequenceID AS Expr1, tblAutoshipProducts.SequenceNumber, tblProducts.ProductName, 
                      tblProducts.ProductGroup, Customers.CompanyName, Customers.ContactFirstName, Customers.ContactLastName, Customers.BillingAddress, 
                      Customers.Address2, Customers.City, Customers.StateOrProvince, Customers.PostalCode, Customers.PhoneNumber, Customers.EmailAddress
FROM         tblAutoShipment INNER JOIN
                      tblAutoshipmentCSEvent ON tblAutoShipment.AutoshipmentID = tblAutoshipmentCSEvent.autoshipmentId INNER JOIN
                      CSEvent ON tblAutoshipmentCSEvent.cseventId = CSEvent.id INNER JOIN
                      CSActivity ON CSActivity.ActivityID = CSEvent.ActivityID INNER JOIN
                      Orders ON tblAutoShipment.BaseOrderNumber = Orders.OrderID INNER JOIN
                      tblSource ON Orders.SourceID = tblSource.SourceID LEFT OUTER JOIN
                      tblAdKey ON Orders.AdKey = tblAdKey.AdKeyID INNER JOIN
                      tblCampaign ON tblSource.CampaignID = tblCampaign.CampaignID INNER JOIN
                      tblClient ON tblSource.ClientID = tblClient.ClientID INNER JOIN
                      tblSequence ON tblAutoShipment.SequenceID = tblSequence.SequenceID INNER JOIN
                      tblAutoshipProducts ON tblSequence.SequenceID = tblAutoshipProducts.SequenceID INNER JOIN
                      tblProductOffer ON tblAutoshipProducts.productOfferId = tblProductOffer.ProdOfferID INNER JOIN
                      tblProducts ON tblProductOffer.ProductID = tblProducts.ProductID INNER JOIN
                      Customers ON tblAutoShipment.CustomerID = Customers.CustomerID AND CSEvent.CustomerID = Customers.CustomerID AND 
                      Orders.CustomerID = Customers.CustomerID
WHERE     (tblAutoShipment.CustomerID = '1909034') AND (CSActivity.ActivityID = '91') AND (tblAutoshipProducts.SequenceNumber = 1) AND 
                      (tblClient.ClientID IN (171, 74, 170, 80, 79, 164, 169, 172))

When this is all done, we'll remove the customer ids to get our answer, but if we can roll up this one cust, we can do a million.

Thanks,


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[tran008]

search for Pivot or have a look at this thread

http://www.tek-tips.com/viewthread.cfm?qid=1479412&page=6

 

[RonRepp]

Thanks Tran...I'll give it a shot.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net