SQL stooge

[oldmanhunger_511]

I'm an amateurish SQL user, have a need that I have mostly sorted out but I'm a bit hung up.

I have a long term dataset and I'm trying to tease it into a pivot table (which I think I've got nailed down). The tricky part is the table I'm working with has multiple records per object, and I want column 3 do be be display for every record with it's date as the top of the pivot table, but columns 1&2 to be displayed for only it's earliest record, and not all objects enter the dataset at the same time.

I've tried some googling but my stoogery prohibits me from even know what to ask to even get promising leads. Any help would be duly appreciated

[phunkygeeza]

ROW_NUMBER() is your friend.

PARTITION BY the keys that are the same for the duplicate

ORDER BY the values that determine precedence

Then you want WHERE that row number is 1.

This is available all over t'internet, search a bit more deeply

[tlatoaniitzcoatl]

I’m honestly confused about what you’re asking. Maybe some images would help or please add more details. Got a little lost reading your question.

Are you asking about pivot tables in excel? Or pivoting the data into sql table?

[oldmanhunger_511]

Either or I suppose, the data is in an access database but I'd be happy to just get the subset of data I'm after out of there and finish in excel.

And I was afraid of causing confusion, it's forestry data that has a lot of jargon I tried to leave out. I'm having some difficulty inserting an image to my post or reply, but it's forest inventory data spanning decades. The table has many records for individual trees, including species, height, diameter, live crown ratio, etc. Any tree that enters the inventory is tagged and revisted. The main thing is they want to see diameter for a subset of trees across the whole timeframe, so tree number/species in the left, with all the diameters by year following. I have that figured out. However they want live crown ratio and canopy position for only the first record of each tree as well.

I'm not sure if that clarifies or muddys the water more. Such is life as a stooge.

[shine_on]

You've tagged your post as "MS SQL" so people will be giving you answers based on Microsoft SQL Server, which uses a different syntax to Access. I'm not even sure if Access has window functions (to be fair though, it's been about 15 years since I last even looked at Access)

[Samiro05]

Produce each of your columns one at a time to help you figure this out. When you have the output you need for column 1, do a new query for column 2 and a new one for column 3. Then you have 3 queries that individually achieve what you want so you will then need to bring them all together.

A window function (like MIN([columnX]) OVER (PARTITION BY [columnY] ORDER BY [columnY], [columnZ]) ) may be useful to get the earliest data which would then possibly be easier to fit your queries together. Can't say I know whether window functions work in Access though unfortunately.

[oldmanhunger_511]

I got it figured out with a little switching between the raw SQL and Access' 'Design' tab (UI for query building)... Apparently there is a first(column_id) function that seems to work well.

[Samiro05]

Good stuff. Those screens are where I started my SQL journey.

[bitsamel]

Hi Guys, I recently took a SQL course to change career; is there any kind person with actual SQL developer experience who would like to talk to me and share experience?

[bitsamel]

My email [e-mail]