Making 1 row out of 2

[RonRepp]

Hi all:

My boss gave me this undaunting task of trying to reduce this query to one row. This can be done by eliminating two of the columns and doing a group, but he needs to see that data, too. He just wants it in one row.

I've highlighted the two that need to be removed to return one row and then will have to be brought back in. I just haven't the foggiest idea how.

SELECT 
OrderID, 
DNIS, 
AdKey, 
PhoneNumber, 
[b][COLOR=red]--ProductGroup, [/color][/b]
ContactFirstName, 
ContactLastName, 
BillingAddress, 
Address2, 
City, 
StateOrProvince, 
PostalCode,
Code, 
CreditCardNumber, 
expdate--,
[b][COLOR=red]--itemtype [/color][/b]

from ##tempj1

group by
	OrderID, 
	DNIS, 
	AdKey, 
	PhoneNumber, 
	[b][COLOR=red]--ProductGroup, [/color][/b]
	ContactFirstName, 
	ContactLastName, 
	BillingAddress, 
	Address2, 
	City, 
	StateOrProvince, 
	PostalCode,
	Code, 
	CreditCardNumber, 
	expdate--,
	[b][COLOR=red]--itemtype [/color][/b]

Can this be done?


Ron Repp

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

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[jfdabiri]

you need an aggregate function to use group by.

 

[RonRepp]

JF:

Can you explain further? I'm messing with @@RowCount right now.

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

 

[ESquared]

If you have two rows worth of data, how will you make them one row? Do you mean to pivot the data?

Please give simplified (but representative) sample data and desired results.

 

[RonRepp]

ESquared:

OrderID ProductGroup
12345 Product1
12345 Product2

Into 1

OrderID ProductGroup1 ProductGroup2
12345 Product1 Product2

Kind of like that, but the total rows is always unknown.


Ron Repp

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

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[RonRepp]

Never mind.

We went a different route.

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

 

[ESquared]

If you want to know how to do that search for PIVOT. It's easier in SQL Server 2005 than SQL Server 2000.