June 26, 2019 at 1:52 pm
I have been presented with some source data and mapping tables where the source data is stored as :
MyData
Type Id Field1 Field2 Field3
1 1 MR John Smith
1 2 MRS Jane Doe
2 3 Bloggs Betty Miss
what I would like to ideally be able to do is produce a view for end users where they can write "select Id, Saluation, Forename, Surname from view_MyData", I have two mapping tables :
Mapping1
Type Field1 Field2 Field3
1 Salutation Forename Surname
2 Surname Forename Saluation
Mapping2
Type Salutation Forename Surname
1 Field1 Field2 Field3
2 Field3 Field2 Field1
which give me how my fields marry up and I can write something using dynamic SQL along the lines of this
declare @SQL nvarchar(2000);
select @SQL='
select d.Id,
'+m.Salutation+' as Saluation,
'+m.ForeName+' as Forename,
'+m.Surname+' as Surname
from dbo.MyData d
inner join dbo.Mapping2 m on m.Type = d.Type'
from dbo.Mydata d
inner join dbo.Mapping2 m on m.Type=d.Type
exec (@SQL)
but I wonder if there is a simpler way, I almost feel that there must be a way to pivot one of the mapping tables but my brain can't figure it out so I thought I would ask the community!
Thank you.
June 26, 2019 at 6:13 pm
>> I have been presented with some source data and mapping tables where the source data is stored as : <<
why did you fail to post any DDL? Did you read the rules for the forum? it’s very hard to compile a pretty little picture instead of DDL. You might want to learn what type, ID and field mean in SQL. You might also learn not to use repeated groups in this language
I’m going to make some guesses and try to repair the garbage that you posted.
CREATE TABLE Customers
(blood_type CHAR(1) NOT NULL,
foobar_id CHAR(1) NOT NULL,
PRIMARY KEY (blood_type, foobar_id),
salutation VARCHAR(4) NOT NULL
CHECK(cust_title IN (‘Mr’, ‘Mrs’, ‘Miss’))
forename VARCHAR(15) NOT NULL,
surname VARCHAR(15) NOT NULL);
there is no such thing as a generic type or a generic id in RDBMS.the most fundamental law of logic to the law of identity, which says that to be is to be something in particular and to be nothing in particular or anything in general is to be nothing at all. Now that we have DDL we can look for a primary key which is required for a table.
INSERT INTO Customers
VALUES (‘1’, ‘1’, ‘Mr’, ‘John’, ‘Smith’),
(‘1’, ’2’, ’Mrs’, ‘Jane’, ‘Doe’),
(‘2’, ’3’, ‘Miss’, ‘Betty’, ‘Bloggs’);
>> what I would like to ideally be able to do is produce a view for end users where they can write "select Id, Salutation, Forename, Surname from view_MyData", I have two mapping tables: <<
Why not use two views? You have to clean up your data, so each column is drawn from one and only one domain. This is the definition of 1NF! Also, we never used the term "mapping table" when I was on ANSI X3H2.
Please post DDL and follow ANSI/ISO standards when asking for help.
June 26, 2019 at 6:21 pm
Ignore Joe, he's not entirely wrong, but grumpy. Why don't you use a CASE and depending on the type, return the column you want?
select d.Id,
CASE WHEN Type=1 THEN Field1 ELSE Field3 END as Saluation,
Field2 as Forename,
CASE WHEN Type=1 THEN Field3 ELSE Field1 as Surname
from dbo.MyData d
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
June 26, 2019 at 6:56 pm
>> Why don't you use a CASE and depending on the [sic] type, return the column you want? <<
You don't do this because it doesn't work. Did you notice when I corrected the DDL that was never posted the salutation was drawn from a limited domain of possible titles? How do you put a check constraint on such a datatype and domain in your data model? The answer is you can't and a column that has to switch datatypes, you lost all the advantages of SQL as a strongly typed language. Hey, why not go to the other extreme where every column can be of any possible datatype at any time, varying from row to row in your non-table?
I don't know if you like Jewish humor, but one of the classic routines in the borscht belt is "Levitan the Tailor". The gag is that Levitan shows a customer how he can hunch over, grab a sleeve, and contort his body in a truly horribly fitted suit in such a way that he can barely walk in it. At the end of the routine, a little Jewish lady comes up to the customer and ask for the name of his tailor on the grounds that "anyone who could fit a horrible deformed crippled like you, must be the greatest tailor in New York City!"
Please post DDL and follow ANSI/ISO standards when asking for help.
June 26, 2019 at 7:24 pm
You are assuming again (in a development forum) that the user *can* make a change to the structure. You're solving the design of the cart when we can only guide the horse. I don't disagree with you that the data shouldn't be mixed like that.
But that's not what they asked.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
June 27, 2019 at 8:37 am
I've previously found this forum to be a community and so it's a shame if some people feel the need to be unpleasant.
I hold my hands up, I did indeed fail to post any DDL, I completely forgot, simple as that.
My first thoughts were indeed to use a case statement and that would work perfectly but my example is just that, my real world data will 20 fields across hundreds of types which are being extended as part of a legacy design but very much current application handling data loads from third parties where we don't control for the format of the source data.
Personally I would have loaded the diverse format source data into a common format as the earliest step possible, it seems obvious to me, but that isn't what happened and it's not in my gift to change.
June 27, 2019 at 12:27 pm
Well, two things. 1 - you can't use dynamic SQL in a view, so you might have to make it a table valued function or something. 2 - perhaps your users would be better served to have you pre-process the data for them into a table, where you could consider Joe's suggestions? Then you could index it in useful ways for how they search, etc.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
June 27, 2019 at 1:00 pm
That was where my problem started, dynamic SQL will do the job but isn't elegant or efficient and won't work in a view hence why I asked the question in case someone could see an obvious route but I might well resign myself to rehashing the data properly so at least it is easier to use, I can't mess with the current processes but there is nothing to stop me adding a step for convenience. Thanks
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply