Table with 31 columns (id_overviewcode)

1

Hello,
I have two tables

  1. table Calendar as Cal
  • with 32 columns :
    (id_calendar,
    id_overviewcode1,
    id_overviewcode2......until
    id_overviewcode31)
    each day of the calendar contains an id_overview (from 1...1000)
  1. table OverviewCode 'code'
  • with all the id's (1...1000, and their descriptions)

For each column cal.id_overviewcode(n1...31)
I would like it joined together and view both the
id_overviewcode with the description.

I can join it to one column but how could I join it to multiple columns efficiently?

select
cal.*
from calendar cal
inner join overviewcode code on cal.id_overviewcode1 = code.id_overviewcode

2

sysobjects , syscolumns

or

information_schema.columns ( not always reliable )

select schema_name(tab.schema_id) as schema_name,
    tab.name as table_name, 
    col.column_id,
    col.name as column_name, 
    t.name as data_type,    
    col.max_length,
    col.precision
from sys.tables as tab
    inner join sys.columns as col
        on tab.object_id = col.object_id
    left join sys.types as t
    on col.user_type_id = t.user_type_id
order by schema_name,
    table_name, 
    column_id;
3 
SELECT
    cal.id_calendar,
    cal.id_overviewcode1, code1.description,
    cal.id_overviewcode2, code2.description,
    cal.id_overviewcode3, code3.description,
    cal.id_overviewcode4, code4.description,
    cal.id_overviewcode5, code5.description /*,...*/
FROM dbo.calendar cal
INNER JOIN dbo.overviewcode code1 ON cal.id_overviewcode1 = code1.id_overviewcode
INNER JOIN dbo.overviewcode code2 ON cal.id_overviewcode2 = code2.id_overviewcode
INNER JOIN dbo.overviewcode code3 ON cal.id_overviewcode3 = code3.id_overviewcode
INNER JOIN dbo.overviewcode code4 ON cal.id_overviewcode4 = code4.id_overviewcode
INNER JOIN dbo.overviewcode code5 ON cal.id_overviewcode5 = code5.id_overviewcode
/*...*/
3 Likes
4

generate scripting .... its very easy

please let me know OR you google search

example
if you have 100 columns you want to join on
one single very small statement will generate the 100 column script

1 Like
5

Hello, thanks for your answer.

What I did was do a select script so it would write all the columns and then select all the text with the mouse and click on quick replace, through that I bulk added text like inner join.
Not the if it was the most efficient so will look at all the solutions here too. Thank you.

6

(yeah that's what I did but writing it out was annoying.
So you did a select script, and then you select the text where you want to add a bulk change and click quick replace right?)
Thanks for the answer!

7

I've never seen this before wil try it out thank you!