Fetch dynamic columns in join condition

Question

Fetch dynamic columns in join condition

Abhas

SSC Enthusiast

Points: 181
More actions
February 16, 2023 at 11:31 am

#4150473

Hi Experts,
Please help me in following situation.
I am having table A and Table B.
Table A : In this table columns are constant
Table B: In this table columns are changes Monthly basis. In Jan columns will be available as jan-2023, Feb-2023 till Dec-2023.
but if move to next month then prev month's column will be disappear and next 12 months columns will be there like Feb-2023 to Jan-2024. if we move into march then Mar-2023 to Feb-2024 and so on.
Now i want to join this Table with A based on ID column and perform some aggregation and calculations.
Please help in this scenario how to select column name in the select.
Thanks,
Abhas

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Is there any possibility of educating the person maintaining the table in the ways of normalisation? Just add a date column to TableB and the need for fancy querying of dynamic columns has gone.

Alternatively, I would probably write some code to translate TableB into normal form and push the results into a temp table, for onward processing with TableA. The need for dynamism means that this code will be a little complex.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Abhas SSC Enthusiast Points: 181 More actions · Answer 2

Thanks Phil,

I will try same and will get back to you.

Thank you very much for suggestion.

Thaks,

Abhas

mappy999 Newbie Points: 6 More actions · Answer 3

mappy999

Newbie

Points: 6

February 16, 2023 at 3:15 pm

#4150579

Good content and very good post..

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

You don't need a temp table, you can just create a view of the existing table. The really good this is the view can be created using dynamic SQL, so that you only have to write the code once and run it only once per month when the table changes.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".