0

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.

Community
  • 1
  • 1
James H
  • 24
  • 5
  • Try using PIVOT tables with unknown number of columnnames: https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server?noredirect=1&lq=1 – Nicola Lepetit Jan 16 '20 at 15:31
  • Your old code "knows" that this table has a fixed schema - so you don't need to use a dynamic pivot as Gordon mentions. It's a risky move trying to fake things so that the old code works with a significant schema change. Plan and test carefully. – SMor Jan 16 '20 at 15:59
  • @NicolaLepetit you've linked the same article as I have linked in my question. – James H Jan 17 '20 at 09:08
  • @SMor There are two issues. One: We might well add more columns/rows to the config even for the legacy app. Two: There are over 300 rows. These two issues mean that dynamic code is certainly desirable. – James H Jan 17 '20 at 09:26

2 Answers2

1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • The old code is still in production (and will be for a few years yet), it still gets the odd enhancement so new keys are a possibility. That said it should be possible to put the new keys into the new table only. I've tested this and it would work for reads. But when I try to update via the view it gives this message: ` Msg 4406, Level 16, State 1, Line 1 Update or insert of view or function 'oldconfig' failed because it contains a derived or constant field. `code` – James H Jan 17 '20 at 09:32
  • So I think my solution to updating will be to create triggers. Hopefully that will work. – James H Jan 17 '20 at 11:17
0

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)
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
  • I get what this does, but I actually want to change the schema to the key value pair, and then display as a horizontal row rather than display as vertical row and keep the schema. – James H Jan 17 '20 at 10:53