Five most recent non blank entries transposed

VINCENT DICIGLIO
VINCENT DICIGLIO used Ask the Experts™
on
Hello all,

I have a database called 'Customer' within it I have a table called 'NextSteps' and within this NextSteps table I have 3 columns called 'ModifiedOn', ' ClientId', and ‘NextSteps’
The problem is there are numerous distinct entries’ under the  ‘NextSteps’ column so the ClientId is repeated numerous times. Also there are blank cells under ‘NextSteps’

The data looks like this:

ModifiedOn  ClientId      NextSteps
12/1/2019      1           call customer
12/2/2019      1           schedule follow up
12/3/2019      1           Meet tomorrow
12/4/2019      5           call in a month
12/5/2019      5           not interested
12/6/2019      5           
12/7/2019      8           upgraded
12/8/2019      8           no answer
12/9/2019      8           had lunch today
12/10/2019      2           not interested
12/11/2019      3           schedule follow up
12/12/2019      4           no answer
12/13/2019      4           downgraded plan
12/14/2019      6           
12/15/2019      7           not interested
12/16/2019      9           not interested
12/17/2019      10           no answer
12/18/2019      10           
12/19/2019      10           Meet with today

What I would like to do is create a group by ClientId then create 5 columns, each column would be the most recent non blank value by the 'ModifiedOn' date
column one: 'Most Current Entry'
column two: 'Entry Two'
column three: 'Entry Three'
column four: 'Entry Four'
column five: 'Entry five'

The result would look like this:

  ClientId      Most Current Entry                 Entry Two                            Entry Three       Entry Four       Entry Five
1                      Meet tomorrow                          schedule follow up            call customer            
2                      not interested                         
3                      schedule follow up                        
4                      downgraded plan                no answer                   
5                      no answer                                 not interested                    call in a month            
6                      no answer                         
7                      not interested                         
8                      had lunch today                        no answer                            upgraded             
9                      not interested                         
10                      Meet with today                         not interested                    no answer             

      


How would I accomplish this?

I am using Microsoft SQL Server Management Studio

Thank you for the help!!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
I'm confused.  Based on your description it looks like Client 1 should be as below, which I think is correct:
1                      Meet tomorrow             schedule follow up      call customer              

But you have it listed as:
1                      call customer              schedule follow up      Meet tomorrow            

Which is actually correct?

Author

Commented:
you are correct, I am sorry I did it backwards
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
SELECT
    ClientId,
    MAX(CASE WHEN row_num = 1 THEN NextSteps ELSE '' END) AS [Most Current Entry],
    MAX(CASE WHEN row_num = 2 THEN NextSteps ELSE '' END) AS [Entry Two],
    MAX(CASE WHEN row_num = 3 THEN NextSteps ELSE '' END) AS [Entry Three],
    MAX(CASE WHEN row_num = 4 THEN NextSteps ELSE '' END) AS [Entry Four],
    MAX(CASE WHEN row_num = 5 THEN NextSteps ELSE '' END) AS [Entry Five]
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ClientId ORDER BY ModifiedOn DESC) AS row_num
    FROM dbo.NextSteps
) AS subquery1
WHERE row_num BETWEEN 1 AND 5
GROUP BY ClientId
ORDER BY ClientId

Author

Commented:
Thank you.

this is almost doing it, what is happening is the most current non blank is not being populated under the "Most Current Entry"

Author

Commented:
Thanks!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial