Help Tweaking T-SQL to export values into a table

Question

222654-newcode.txtHi Community,

First, my apologies for the confusing title.

I have accused of not being very descriptive with my questions, so I'm going to be as descriptive as I possibly can.

I have a SQL script that runs against a table called 'GlobalOptionsetMetadata' and a table called 'account'. After the script is run on the tables it produces a view called 'account'.

The view has two fields, ts_easeofworking, and ts_companytype. The script successfully works on field ts_easeofworking, but fails on field ts_companytype.

The authors of the script have explicity informed me that the script needs to be tweaked to work with fields that have multiple values.
222679-account.txt
222656-account-results.txt
222648-globaloptionsetmetadata.txt

For example, ts_companytype has values such as 717750007;717750012. This is known as multi-value field. Whereas you will notice that ts_easeofworking has only single values.

I have attached a table showing the incorrect results that I'm getting with the script in ts_companytype in column b and what I would like to get with the script in Column C named ts_companytype_expected.

I have also attached the tables 'GlobalOptionsetMetadata' and the table called 'account' that the script runs against which when run successfully produces a view called 'account' with the fields ts_easeofworking and ts_company.

Any help tweaking this script will be most appreciated.

Answer 1

Hi LiHong

Thanks for sticking with me on this. However, looking at your sample, I feel you may be on the wrong track.

Let me try and give you another example:

If you were to run the script using the sample data you will find that the field in the 'account' view resolves to:

717750001;717750002'

But

I would like to see

''Target;Vendor'

Does that make sense?

Answer 2

Hi @Carlton Patterson

the script needs to be tweaked to work with fields that have multiple values

It seems you need to use STRING_SPLIT, please check this sample:

CREATE TABLE #TEMP (STRING varchar(300))  
INSERT INTO #TEMP(STRING) VALUES  
('REACH Version|REACH Version|REACH'),('ASDC|DESC|QWER')  
  
--Using String_Split(SQL Sever 2016+)  
SELECT VALUE   
FROM #TEMP CROSS APPLY String_Split(STRING,'|')  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

The authors of the script have explicity informed me that the script needs to be tweaked to work with fields that have multiple values.

No, it is not the script that needs to be tweaked. It is the tables that needs to be tweaked. As Olaf says, storing multiple values in a single cell is a very bad idea. Relational databases are built on some fundamentals. One of these is one value per cell. There is nothing to stop you from breaking this rule, but you will be duly punished if you do. Because you are not intended to do this, there is no built-in support to handle operations on these values, and you end up with a set of ugly and inefficient kludges.

On the other hand, if you create sub-tables to hold these multiple values, it all becomes a lot easier.

Answer 4

Hi Community,

I have just realised the following tables will help members help me:

CREATE TABLE dbo.GlobalOptionsetMetadata  
(  
  OptionSetName VARCHAR(100),  
  [Option] VARCHAR(100),  
  IsUserLocalizedLabel VARCHAR(100),  
  LocalizedLabelLanguageCode VARCHAR(100),  
  LocalizedLabel VARCHAR(100),  
  GlobalOptionSetName VARCHAR(100),  
  EntityName VARCHAR(100)  
)  
  
INSERT dbo.GlobalOptionsetMetadata VALUES   
('ts_companytype','717750000','False','1033','Deal Introducer','',''),  
('ts_companytype','717750006','False','1033','Debt Advisory','',''),  
('ts_companytype','717750011','False','1033','Debt Provider','',''),  
('ts_companytype','717750003','False','1033','Diligence','',''),  
('ts_companytype','717750016','False','1033','Excluded','',''),  
('ts_companytype','717750012','False','1033','Gatekeeper','',''),  
('ts_companytype','930580000','False','1033','HR Recruiter','',''),  
('ts_companytype','717750007','False','1033','Investor Group','',''),  
('ts_companytype','717750015','False','1033','Investor Reporting','',''),  
('ts_companytype','717750010','False','1033','M&A Advisory','',''),  
('ts_companytype','717750009','False','1033','Montagu Supplier','',''),  
('ts_companytype','717750013','False','1033','PE Firm','',''),  
('ts_companytype','717750014','False','1033','Placement Agent','',''),  
('ts_companytype','717750008','False','1033','Portfolio Company','',''),  
('ts_companytype','717750004','False','1033','Portfolio Support','',''),  
('ts_companytype','717750005','False','1033','Talent Source','',''),  
('ts_companytype','717750001','False','1033','Target','',''),  
('ts_companytype','717750002','False','1033','Vendor','','')  


CREATE TABLE dbo.account  
(   
  ts_easeofworking VARCHAR(100),  
  ts_companytype VARCHAR(100)  
)  
  
INSERT dbo.account VALUES  
('', '717750000;717750002;717750013'),  
('', '717750007;717750012'),  
('', '717750001;717750005'),  
('', '717750001;717750003'),  
('', '717750001')  
  
CREATE TABLE StateMetadata (  
    EntityName varchar(100),  
    State int,  
    IsUserLocalizedLabel bit,  
    LocalizedLabelLanguageCode int,  
    LocalizedLabel varchar(50))  
  
INSERT StateMetadata VALUES  
('account',0,CONVERT(bit, 'False'),1033,'Active'),  
('account',1,CONVERT(bit, 'False'),1033,'Inactive'),  
('connection',0,CONVERT(bit, 'False'),1033,'Active'),  
('connection',1,CONVERT(bit, 'False'),1033,'Inactive'),  
('appointment',0,CONVERT(bit, 'False'),1033,'Open'),  
('appointment',1,CONVERT(bit, 'False'),1033,'Completed')  
  
CREATE TABLE StatusMetadata (  
    EntityName varchar(50),  
    State int,  
    Status int,  
    IsUserLocalizedLabel bit,  
    LocalizedLabelLanguageCode int,  
    LocalizedLabel varchar(50))  
  
INSERT StatusMetadata VALUES  
('account',0,1,CONVERT(bit, 'False'),1033,'Active'),  
('account',1,2,CONVERT(bit, 'False'),1033,'Inactive'),  
('connection',0,1,CONVERT(bit, 'False'),1033,'Active'),  
('connection',1,2,CONVERT(bit, 'False'),1033,'Inactive'),  
('appointment',0,1,CONVERT(bit, 'False'),1033,'Free'),  
('appointment',0,2,CONVERT(bit, 'False'),1033,'Tentative'),  
('appointment',1,3,CONVERT(bit, 'False'),1033,'Completed'),  
('appointment',2,4,CONVERT(bit, 'False'),1033,'Canceled'),  
('appointment',3,5,CONVERT(bit, 'False'),1033,'Busy'),  
('appointment',3,6,CONVERT(bit, 'False'),1033,'Out of Office'),  
('email',0,1,CONVERT(bit, 'False'),1033,'Draft'),  
('email',1,2,CONVERT(bit, 'False'),1033,'Completed'),  
('email',1,3,CONVERT(bit, 'False'),1033,'Sent')  

SELECT * FROM StatusMetadata

CREATE TABLE TargetMetadata (  
    EntityName varchar(50),  
    AttributeName varchar(50),  
    ReferencedEntity varchar(50),  
    ReferencedAttribute varchar(50))  
  
INSERT TargetMetadata VALUES  
('activityparty','partyid','ts_boardmember','ts_boardmemberid'),  
('activityparty','activityid','activitypointer','activityid'),  
('activityparty','ownerid','systemuser',NULL),  
('activityparty','ownerid','team',NULL),  
('account','parentaccountid','account',NULL),  
('account','ts_recommendedby','systemuser','systemuserid'),  
('account','ts_countryofeconomicoriginforthecapital','ts_country','ts_countryid'),  
('account','xpd_reminderto','systemuser','systemuserid'),  
('account','preferredserviceid','service','serviceid'),  
('account','msdyn_segmentid','msdyn_segment','msdyn_segmentid')  
  
SELECT * FROM TargetMetadata  

Answer 5

has values such as 717750007;717750012. This is known as multi-value field

It's also known as the worst way to store relational data.

I have attached ...

There is nothing attached in your post.