September 17, 2020 at 7:16 am
Hi all, I must say at the start that I can do basic SQL so I really don't know where to start on this one so come on this board for some expert help.
As context, all the Reference data is packed into 3 tables with each row holding only part of the structure. I have simplified the structure below to show how it all fits together.
SOURCE_SYSTEM_TABLE_A
id (PK)
tableName
SOURCE_SYSTEM_COLUMN_B
id(PK)
SOURCE_SYSTEM_TABLE_A_id
(FK)columnName
columnSequenceNumber
The above tables are filled with all the reference data names and columns e.g.
SOURCE_SYSTEM_TABLE_A
id tableName
1 refCountry
2 refContinents
SOURCE_SYSTEM_COLUMN_B
id SOURCE_SYSTEM_TABLE_A_id columnName columnSequenceNumber
100 1 id 1
101 1 countryName 2
102 1 countryCode 3
103 2 id 1
104 2 continentName 1
Now it gets really confusing, the 3rd table holds the actual values each of the columns:
COLUMN_VALUE_C
id(PK) SOURCE_SYSTEM_TABLE_A_id(fk) SOURCE_SYSTEM_COLUMN_B_id(fk) columnValue rowGroup(guid)
1000 1 100 826 AAAA
1001 1 101 United Kingdom AAAA
1002 1 102 GBR AAAA
1003 1 100 620 BBBB
1004 1 101 Portugal BBBB
1005 1 102 PRT BBBB
My problem is that I want to do a select across the 3 tables by selecting one of the reference table names in TABLE_A with the results being displayed in the normal way e.g.
refCountry
id countryName countryCode
826 United Kingdom GBR
620 Portugal PRT
Any pointers would be greatly appreciated. It's not the sort of table structure I have come across before.
Many thanks
Rob
September 17, 2020 at 9:15 am
Not a solution since am at work, but for the future, if you have TSQL Questions, rather than what u did, its much more helpful and easier for you and the forum members if you post something like this. So everyone who wants to take a shot at your question doesnt have to write this by hand to test
create table #A
(A_ID int
,tablename varchar(40)
)
create table #B
(B_ID int
,SOURCE_SYSTEM_TABLE_A_id varchar(40)
,columnName varchar(40)
,columnSequenceNumber int
)
create table #C
(C_ID int
,A_ID int
,B_ID int
,columnValue varchar(40)
,rowGroup varchar(40)
)
insert into #A
values (1,'refCountry'),(2,'refContinents')
insert into #B
values
(100,1,'ID',1)
,(101,1,'countryName',1)
,(102,1,'countryCode',1)
,(103,2,'ID',1)
,(104,2,'continentName',1)
insert into #C
values
(1000,1,100,'826','AAAA')
,(1001,1,101,'United','AAAA')
,(1002,1,102,'GBR','AAAA')
,(1003,1,100,'620','AAAA')
,(1004,1,101,'Portugal','BBBB')
,(1005,1,102,'PRT','BBBB')
select *
from #A
select *
from #B
select *
from #C
I want to be the very best
Like no one ever was
September 17, 2020 at 9:23 am
Ha, ok I am sorry. I was trying to explain the best way I could. Many thanks for the advice, I will do it that way in future.
Mike
September 17, 2020 at 9:47 am
Using the sample data from above (with a couple of correction), you can use a cross tab to achieve what you're after:
SELECT MAX(CASE B.ColumnName WHEN 'ID' THEN C.ColumnValue END) AS ID,
MAX(CASE B.ColumnName WHEN 'CountryName' THEN C.ColumnValue END) AS CountryName,
MAX(CASE B.ColumnName WHEN 'CountryCode' THEN C.ColumnValue END) AS CountryCode
FROM #C C
JOIN #B B ON C.B_ID = B.B_ID
GROUP BY C.rowGroup;
Note, however, that the real problem here is your design; it's massively denormalised. You really need to fix that. The end result, above, should be how your data is stored, not how you currently have it.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 17, 2020 at 9:50 am
The following SQL should get the results as requested in your post.
SELECT ID = MAX( CASE WHEN col.columnName = 'ID' THEN val.columnValue END )
, countryName = MAX( CASE WHEN col.columnName = 'countryName' THEN val.columnValue END )
, countryCode = MAX( CASE WHEN col.columnName = 'countryCode' THEN val.columnValue END )
FROM SOURCE_SYSTEM_TABLE_A AS tab
INNER JOIN SOURCE_SYSTEM_COLUMN_B AS col
ON tab.id = col.SOURCE_SYSTEM_TABLE_A_id
INNER JOIN COLUMN_VALUE_C AS val
ON tab.id = val.SOURCE_SYSTEM_TABLE_A_id
AND col.id = val.SOURCE_SYSTEM_TABLE_B_id
WHERE tab.tablename = 'refCountry'
GROUP BY val.rowGroup;
Now, I would suggest that you take the time to familiarise yourself with these 2 articles by Jeff Moden to see how to do it dynamically
https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs
September 17, 2020 at 10:07 am
Many thanks for your help both,
I will have a go in an hour and see whether I can get it working.
One added bit of complexity (I don't think I explained too well). TableB holds the structure for each reference data set (the columns for each are different).
refCounty has:
id
CountryName
CountryCode
refContinents has:
id
ContinentName
I am also trying to figure out how to inject the structure from TableB somehow into the query so at runtime I only need to provide the tablename, refCountry, refContinents etc.
Would I need to do this as 2 stages (i.e. retrieve the column structure from TableB) then pass these into the query.
Sorry if there is a better way to explain this
September 17, 2020 at 10:46 am
Neither your example data nor requirements mentioned continent. If you don't come right, then come back with sample data that represents both continent and country, and show us what you have tried. then we can show you how close you are.
September 17, 2020 at 11:32 am
Thanks Des,
If you look at the very first post my example showed both Country and continents as reference data in TableA and TableB (here I was trying to show how the data is actually stored).
SOURCE_SYSTEM_TABLE_A
id tableName
1 refCountry
2 refContinents
SOURCE_SYSTEM_COLUMN_B
id SOURCE_SYSTEM_TABLE_A_id columnName columnSequenceNumber
100 1 id 1
101 1 countryName 2
102 1 countryCode 3
103 2 id 1
104 2 continentName 1
I also tried to explain my requirement:
"My problem is that I want to do a select across the 3 tables by selecting one of the reference table names in TABLE_A with the results being displayed in the normal way"
I will have a go and let you all know how I get on.
September 18, 2020 at 12:10 pm
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply