How to Pivot daily date to a week Row

Question

How to Pivot daily date to a week Row

Chris Spencer

Valued Member

Points: 62
More actions
September 30, 2023 at 11:08 pm

#4298653

I have data as below which is a per day view, I wish to return it as a per dweek view as
Date, Day,Session.Child, MonProduct,TueProduct,WedProduct, ThuProduct, FriProduct
I have tried various Pivot attempts to no avail.
ID Date ShortDayName Session ParentCode ChildCode ChildSurname ChildFirst Product
----------- ------------------------------ ------------ ------- ---------- --------- ------------------------------ ------------------------------ -------
2499332 2023-09-25 Mon AM 2420 7087 Jack STEFAN
2499333 2023-09-25 Mon PM 2420 7087 Jack STEFAN
2499334 2023-09-26 Tue AM 2420 7087 Jack STEFAN
2499335 2023-09-26 Tue PM 2420 7087 Jack STEFAN
2499336 2023-09-27 Wed AM 2420 7087 Jack STEFAN
2499337 2023-09-27 Wed PM 2420 7087 Jack STEFAN
2499338 2023-09-28 Thu AM 2420 7087 Jack STEFAN
2499339 2023-09-28 Thu PM 2420 7087 Jack STEFAN
2499340 2023-09-29 Fri AM 2420 7087 Jack STEFAN
2499341 2023-09-29 Fri PM 2420 7087 Jack STEFAN
2499342 2023-10-02 Mon AM 2420 7087 Jack STEFAN
2499343 2023-10-02 Mon PM 2420 7087 Jack STEFAN
2499344 2023-10-03 Tue AM 2420 7087 Jack STEFAN
2499345 2023-10-03 Tue PM 2420 7087 Jack STEFAN
2499346 2023-10-04 Wed AM 2420 7087 Jack STEFAN
2499347 2023-10-04 Wed PM 2420 7087 Jack STEFAN
2499348 2023-10-05 Thu AM 2420 7087 Jack STEFAN
2499349 2023-10-05 Thu PM 2420 7087 Jack STEFAN
2499350 2023-10-06 Fri AM 2420 7087 Jack STEFAN
2499351 2023-10-06 Fri PM 2420 7087 Jack STEFAN
2498827 2023-10-09 Mon AM 2420 7087 Jack STEFAN
2498832 2023-10-09 Mon PM 2420 7087 Jack STEFAN PPMLR
2498828 2023-10-10 Tue AM 2420 7087 Jack STEFAN
2498833 2023-10-10 Tue PM 2420 7087 Jack STEFAN
2498829 2023-10-11 Wed AM 2420 7087 Jack STEFAN
2498834 2023-10-11 Wed PM 2420 7087 Jack STEFAN PPMLR
2498830 2023-10-12 Thu AM 2420 7087 Jack STEFAN
2498835 2023-10-12 Thu PM 2420 7087 Jack STEFAN PPMLR
2498831 2023-10-13 Fri AM 2420 7087 Jack STEFAN
2498836 2023-10-13 Fri PM 2420 7087 Jack STEFAN
2499382 2023-09-25 Mon AM 2420 7198 Jack RIC
2499383 2023-09-25 Mon PM 2420 7198 Jack RIC
2499384 2023-09-26 Tue AM 2420 7198 Jack RIC
2499385 2023-09-26 Tue PM 2420 7198 Jack RIC
2499386 2023-09-27 Wed AM 2420 7198 Jack RIC
2499387 2023-09-27 Wed PM 2420 7198 Jack RIC
2499388 2023-09-28 Thu AM 2420 7198 Jack RIC
2499389 2023-09-28 Thu PM 2420 7198 Jack RIC
2499390 2023-09-29 Fri AM 2420 7198 Jack RIC
2499391 2023-09-29 Fri PM 2420 7198 Jack RIC
2499392 2023-10-02 Mon AM 2420 7198 Jack RIC
2499393 2023-10-02 Mon PM 2420 7198 Jack RIC
2499394 2023-10-03 Tue AM 2420 7198 Jack RIC
2499395 2023-10-03 Tue PM 2420 7198 Jack RIC
2499396 2023-10-04 Wed AM 2420 7198 Jack RIC
2499397 2023-10-04 Wed PM 2420 7198 Jack RIC
2499398 2023-10-05 Thu AM 2420 7198 Jack RIC
2499399 2023-10-05 Thu PM 2420 7198 Jack RIC
2499400 2023-10-06 Fri AM 2420 7198 Jack RIC
2499401 2023-10-06 Fri PM 2420 7198 Jack RIC
2499402 2023-10-09 Mon AM 2420 7198 Jack RIC
2499403 2023-10-09 Mon PM 2420 7198 Jack RIC
2499404 2023-10-10 Tue AM 2420 7198 Jack RIC
2499405 2023-10-10 Tue PM 2420 7198 Jack RIC
2499406 2023-10-11 Wed AM 2420 7198 Jack RIC
2499407 2023-10-11 Wed PM 2420 7198 Jack RIC
2499408 2023-10-12 Thu AM 2420 7198 Jack RIC
2499409 2023-10-12 Thu PM 2420 7198 Jack RIC
2499410 2023-10-13 Fri AM 2420 7198 Jack RIC
2499411 2023-10-13 Fri PM 2420 7198 Jack RIC
October 9, 2023 at 9:41 am

This was removed by the editor as SPAM
October 9, 2023 at 9:41 am

This was removed by the editor as SPAM
October 9, 2023 at 9:41 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

~~You appear to be missing "Product" for your sample data.~~

Ah, crud. My apologies. I didn't scroll down enough. You have a few rows with data in the "Product" column. I'm still not sure what you're after for an output, though.

Also, please see the article at the first link in my signature link below for how to post readily consumable data and why it will seriously help you get replies much more quickly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

To add to what Jeff said, provide us some sample code for what you have tried too. Looking at it though I do have a question about the results - since "Date" is a field you require in the result, how are you planning to pivot that data?

What I mean is for the date " 2023-10-13" you will have data for Friday in that row, and everything else will be null as October 13th, 2023 is a Friday.

The columns you want in your result, to me do not make sense. With what you have given (even with valid data), we have no way to really help you without making assumptions and I have learned that making assumptions is a good way to get bad results. My GUESS is that you want the "Date" to be the Monday of that week, but then I have no clue why you need "day" or what that will hold. And I am also assuming that "session.child" is the concatenation of child first name and child surname, but again, this is just a guess.

So, what I recommend is that you provide object creation scripts, script of what you have tried, and expected output.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.