I'm accessing and creating reports from a vendor via a replicated SQL Server database. They've done some absolutely insane things that I've been trying to solve for, but this one takes the cake.
They have a table that has many standard columns. But this table also has a column called "Data". The column is a legacy "text" data type, and it contains a giant (hundreds) list of key/value pairs. Each pair is separated by a CRLF, and the key and value are separated by an equal sign. Example:
select myTable.[data] from myTable where tblKey = 123
Result:
Key 1=Value 1
Key 2=Value 2
Key 3=Value 3
...
Key 500=Value 500
I'm trying to determine the most efficient way to break that column out into a usable table of data. The end goal would be to be able to query the table in a way that returns the table key along with specified key/values as column/fields as such:
tblKey | [Key 1] | [Key 3] | [Key 243]
-------|---------|---------|-----------
123 Value 1 Value 3 Value 243
124 Value 1 Value 3 Value 243
125 Value 1 Value 3 Value 243
Is there a way to mold that column into a View? I can't imagine that a Function would be particularly efficient, but I'm sure I could parse things out that way using a string_split or something of that sort. Has anyone run into this type of atrocity before and found a good way to manipulate it into usable data?
Edit to add dbfiddle sample data.
The data is replicated from a vendor's source, so I can't create new tables. I can create views, procedures and functions. That's what I'm looking for advice for a decent way to accomplish.
Best Answer
UPDATE
If as you posted in your own answer you're able to use a UDF to get specific key values let me suggest this one: (You don't need to split all key/values and you don't need to read the table again, you can get it by using text functions.)
db<>fiddle here
Original answer
The only solution I can figure out is by splitting key/values and then pivot it to obtain the desired result.
Unfortunately there are some inconveniences:
text
columns. Hence you must cast it tovarchar
before you are able to manipulate it.nchar(1)
ornvarchar(1)
, ergo you should replaceCHAR(3)+CHAR(10)
by a single character.Value
to some numeric data type.This is what I've got using your sample data:
First CTE split every
Key X=Value Y
. The second one cut this value to obtain each [Key] and [Value]. And the final PIVOT compose the final result in columns.db<>fiddle here
NOTE: I'm not sure if I should maintain [Key 1] & [Value 1] or it should be converted as a column named [Key] & [Value].
A different approach
When I work with 3rd party databases I usually add a new database, on the same server/instance if possible, and then I use it for my own purposes, just to avoid conflicts with the DB owners.
In this case you could add a new table and periodically throw a process to update it with the new values.
You could use a table with all columns:
or a table with Key/Value pairs and use a pivot to obtain the final result: