I have a table (Name: Sales) about when and what customers purchase:

Date      C_Name      Thing 
02/21       A         Fruit
02/26       A         Doll
02/27       A         Drink
02/28       B         Book
06/03       B         Fruit
06/13       B         Shoes
06/10       C         Shoes
07/07       A         Tablet
07/11       A         Chair
07/20       A         Sofa
07/21       A         Coat

And I need to return the first four items purchased by every customer each month, if the items were purchased in a month were less than four, the return should still see the records. Desired Output:

Date(Month) C_Name Thing1 Thing2 Thing3 Thing4
2            A     Fruit   Doll   Drink  
2            B     Book
6            B     Fruit   Shoes 
             .  
             . 
             .
7            A     Tablet  Chair  Sofa Coat    

My current code is as follow, which only can show if the customer purchased equal or more than four objects:

SELECT MONTH(s1.Date) AS Date(Month), s1.C_Name, s1.Thing, s2.Thing, s3.Thing, s4.Thing
FROM Sales as s1, Sales as s2, Sales as s3, Sales as s4
ON s1.C_Name = s2.C_Name = s3.C_Name = s4.C_Name
      AND s1.Date<s2.Date 
      AND s2.Date<s3.Date
      AND s3.Date<s4.Date
      AND MONTH(s1.Date) = MONTH(s2.Date)= MONTH(s3.Date)= MONTH(s4.Date);

Is there any solution for me to change my code? Thanks!

CodePudding user response：

row_number pivot

with 
t1 as
(
    select   *, format(Date,'yyyy-MM') as month
    from     Sales
),
t2 as
(
    select  'Thing' 
            cast(row_number() over 
                  (
                      partition by C_Name, month
                      order by Date
                  ) as varchar(10)
            ) as thing_id
           ,month
           ,C_Name
           ,Thing
    
    from    t1
)
select *
from   t2 pivot (min(Thing) for thing_id in ([Thing1],[Thing2],[Thing3],[Thing4])) as p

Fiddle