CODEWITHSUNDEEP
sqlsql-serverviewdynamic-sql
James H
James H

Reputation: 24

Convert table design without impacting code

Background

We have a very old database design. The worst example is the config table. It's one row with a separate column for each value. It should be a key value pair setup (obviously). It's used everywhere in both our old legacy code, and now in our new web based code. Horrifyingly there are now over 300 columns and worse more are added every now and again.

Goal

I'd love to chuck the table in the bin and replace it with a correct key value pair. However coding time is short so I'm trying to come up with a clever way of displaying it as the old one row for the old legacy code retrieving it from a key value pair.

Attempted Solution

Ordinarily I'd use a view - but given that the best code to display it (pinched from here: https://stackoverflow.com/a/15745076/12059261) is dynamic SQL, I've come unstuck as I cannot use dynamic SQL in a view (my searching tells me that views do not allow dynamic SQL - I'm assuming this is correct?)

Table example

CurrentConfig

Key   Name         Address      etc.
1     My Company   My Address   etc.

WantedConfig

ID     Key     Value
1      Name    MyCompany
2      Address My Address
3      etc.    etc.

I want to change the table from its current form to the new form, but still be able to display it as the current form to legacy code.

Upvotes: 0

Views: 57

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

If the issue is displaying the new config to legacy code, then use conditional aggregation:

create view oldconfig as
    select max(case when key = 'Name' then value end) as name,
           max(case when key = 'Address' then value end) as address,
           . . .
    from newconfig;

Don't worry about dynamic SQL. There are a fixed set of columns used in the legacy code and that is what needs to go in the view. If you add new keys, they will not go in the view, but presumably the new keys are not used by the old code anyway.

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use apply :

select tt.id, tt.Key, tt.Val
from table t cross apply 
     ( values (1, 'Name', Name), 
              (2, 'Address', Address), 
              (3, 'etc.', etc)
     ) tt(id, Key, Val)

Upvotes: 0

Related Questions