**URGENT** Grouping Problems - 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 2000 Forums

SQL Server Development (2000)

**URGENT** Grouping Problems

Author

Topic

AlexP
Starting Member

34 Posts

Posted - 2005-08-10 : 10:40:36

This produces a result set where the OrderProperties are repeated and not all in one line. How can I group the Order Properties to read on one line per unique record?

SELECT     TOP 100 PERCENT dbo.tblOrder.dtOrderDate, dbo.tblCustomer.sFirstName, dbo.tblCustomer.sLastName, 
                      dbo.tblOrderProperty.sKey + '=' + dbo.tblOrderProperty.sValue AS Expr1, dbo.tblCustomer.sEmail
FROM         dbo.tblOrder INNER JOIN
                      dbo.tblOrderProperty ON dbo.tblOrder.iOrderID = dbo.tblOrderProperty.iOrderID INNER JOIN
                      dbo.tblCustomer ON dbo.tblOrder.iCustomerID = dbo.tblCustomer.iCustomerID INNER JOIN
                      dbo.tblAddress ON dbo.tblCustomer.iCustomerID = dbo.tblAddress.iCustomerID
WHERE     (dbo.tblOrder.iCampaignID = 286)
GROUP BY dbo.tblOrder.dtOrderDate, dbo.tblCustomer.sFirstName, dbo.tblCustomer.sLastName, dbo.tblOrderProperty.sKey + '=' + dbo.tblOrderProperty.sValue, 
                      dbo.tblCustomer.sEmail
ORDER BY dbo.tblOrder.dtOrderDate

RESULTS:
57:56.3 ALEX STRADA DATASOURCE_NAME=source apola@test.com
57:56.3 ALEX STRADA DOB= apola@test.com
57:56.3 ALEX STRADA GENDER=m apola@test.com
57:56.3 ALEX STRADA INTEREST=3 apola@test.com
57:56.3 ALEX STRADA KEY1=3 apola@test.com
57:56.3 ALEX STRADA KEY2=3 apola@test.com
57:56.3 ALEX STRADA KEY3=1 apola@test.com
57:56.3 ALEX STRADA SITE_LANGUAGE=en US apola@test.com
57:56.3 ALEX STRADA TIME=2005.07.13.18.17.00 apola@test.com
THIS WHAT I NEED THE QUERY ABOVE TO LOOK LIKE BELOW
57:56.3 ALEX STRADA DATASOURCE_NAME=source DOB= GENDER=m INTEREST=3 KEY1=3 KEY2=3 KEY3=1 SITE_LANGUAGE=en US TIME=2005.07.13.18.17.00

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-10 : 10:42:02

this is probably what you need:
http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

Go with the flow & have fun! Else fight the flow

AlexP
Starting Member

34 Posts

Posted - 2005-08-10 : 11:16:45

I don't think so becuase all I want to do is just break out the OrderProperties and not do any summaries.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-10 : 11:31:30

then try this:
http://weblogs.sqlteam.com/mladenp/archive/2005/03/15/4260.aspx

Go with the flow & have fun! Else fight the flow

AlexP
Starting Member

34 Posts

Posted - 2005-08-10 : 11:42:11

sorry I don't get that at all

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-10 : 11:54:24

well then... the best thing you can do is give us
1. sample data in the form of insert into statements
2. create table statements
3. desired results based on the sample data

that way we can just copy-paste the stuff into our QA and play with it.

Go with the flow & have fun! Else fight the flow

AlexP
Starting Member

34 Posts

Posted - 2005-08-10 : 12:00:46

shall I script out the tables etc.. first?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-10 : 12:03:58

script the tables and only columns that are needed.
sample data should be max 20 insert statements

Go with the flow & have fun! Else fight the flow

AlexP
Starting Member

34 Posts

Posted - 2005-08-10 : 14:34:25

Well basically what I did to get the job done, was to create multiple inner joins like this.

LEFT JOIN tblOrderProperty op8 (nolock) ON op8.iOrderId = tblOrder.iOrderId and op8.sKey= 'MYSTRING'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources