Grouping on different columns based on each scenario

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
New Member

Grouping on different columns based on each scenario

Hi,

 

Can you please help me on how to achieve the below-expected result from the dataset given.

 

SKUSerial #DateStatus
S1x11-JanAvailable 
S1X11-JanUsed
S1X22-JanAvailable 
S1X72-JanUsed
S1X32-JanUsed
S2X55-JanAvailable 
S3X65-JanAvailable 

 

Expected Result

 

SKUAvailable dateUsed DateAvailble CountUsed Count
S11-Jan1-Jan11
S12-Jan2-Jan12
S25-Jan 1 
S35-Jan 1 
1 REPLY
Senior Apprentice

Re: Grouping on different columns based on each scenario

Is available & used date always the same day?

Then it's a simple conditional aggregation:

SELECT
   SKU, 
   Max(CASE WHEN Status = 'Available' THEN date END),
   Max(CASE WHEN Status = 'Used' THEN date end),
   Count(CASE WHEN Status = 'Available' THEN date END),
   Count(CASE WHEN Status = 'Used' THEN date END)
FROM tab
GROUP BY SKU, DATE

 

Otherwise which rules exits to assigned those dates to a row?