Simple GROUPing "?" 2

[kermitforney]

How do I summarize this data to display like this:

1 Toy Balls A B C 1.09 2.09 3.09

???

Thanks!

lngItemID strDescription Date1RANK Date2RANK Date3RANK Date1PPP Date2PPP Date3PPP
----------- -------------- --------- --------- --------- --------------------------------------- --------------------------------------- ---------------------------------------
1 Toy Balls NULL B NULL NULL 2.09 NULL
1 Toy Balls NULL NULL C NULL NULL 3.09
1 Toy Balls A NULL NULL 1.09 NULL NULL

 

[markros]

You're looking for PIVOT operation here, but first you need to massage data a little

If you always have just 3 records per Item, then

select A.lngItemID, A.strDescription, A.Date1Rank, B.Date2Rank, C.Date3Rank, A.Date1PP, B.Date2PP, C.Date3PP
from myTable A inner join myTable B on A.lngItemID = B.lngItemID and A.Date1Rank IS NOT NULL and B.Date2Rank IS NOT NULL inner join myTable C on A.lngItemID = C.lngItemID and C.Date3Rank IS NOT NULL

 

[gmmastros]

Or....

Select lngItemId,
       Min(strDescription) As strDescription,
       Min(Date1Rank) As Date1Rank,
       Min(Date2Rank) As Date2Rank, 
       Min(Date3Rank) As Date3Rank, 
       Min(Date1PP) As Date1PP, 
       Min(Date2PP) As Date2PP, 
       Min(Date3PP) As Date3PP
From   YourTableNameHere
Group By lngItemId

The way this works...

For each row(s) that have the same lngItemId, this query will return the minimum value for each column without regard to which row the data is found. Null values are ignored by the Min aggregate so the only time you will get a null in any column is if all rows (for a particular lngItemId) are null.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kermitforney]

YEEEHAH!!!

I ended up going with GM's suggestion, I have twelve dated sets to work with and an endless self join wouldn't be too efficient.

Thanks, to the both of you and double thanks to GM for the solution, plus explanation!

 

[markros]

Yes, this would be much better solution - not sure why I haven't thought about it - may be because I first started to write a different query, then changed my mind in the middle...