sql - Correlate these tables dynamically (or automated) - Stack Overflow

admin管理员组

文章数量:1122832

I have a situation where I have a couple of tables (TblA and TblB) that link together to start a record. I then have a set of tables (TblSet1_A, TblSet1_B, TblSet1_C, etc.) that hold values linked by another table, tblLink.

The tblLink has:

1. Key_Val: key value to find in tblSet tables
2. Type: determines which set of tables to look at (1 = TblSet1, 2 = TblSet2, etc.)
3. DataType: determines which table of a set to look at (1=_A, 2=_B or _C)
4. Length: max length of value (if type = 2 and <= 255 then =_B else _C)
5. Description: attribute description

Example TblLink entry:

Key_Val	Type	DataType	Length	Description
4	1	2	200	Item 4

I have a situation where I have a couple of tables (TblA and TblB) that link together to start a record. I then have a set of tables (TblSet1_A, TblSet1_B, TblSet1_C, etc.) that hold values linked by another table, tblLink.

The tblLink has:

1. Key_Val: key value to find in tblSet tables
2. Type: determines which set of tables to look at (1 = TblSet1, 2 = TblSet2, etc.)
3. DataType: determines which table of a set to look at (1=_A, 2=_B or _C)
4. Length: max length of value (if type = 2 and <= 255 then =_B else _C)
5. Description: attribute description

Example TblLink entry:

Key_Val	Type	DataType	Length	Description
4	1	2	200	Item 4

This would indicate the Key_Val = 4, tblSet table is tblSet1_B, attribute name 'Key_Val 4'.

Example TblSet1_B Entry:

ID	Key_Val	Value
1	4	Value 4

If I manually go through the system and map everything, I can write a query like this.

Note: TblLink Type = 1 indicates matching with TblA.ID, Type = 2 matches with TblB.ID.

SELECT
    a.ID AS A_ID,
    a.LINKID,
    b.ID AS B_ID,  
    b.OTHER,
    (SELECT [Value] 
     FROM TblSet1_B 
     WHERE [ID] = a.ID AND [KEY_VAL] = 4) AS item_4,
    (SELECT [Value] 
     FROM TblSet2_A 
     WHERE [ID] = b.ID AND [KEY_VAL] = 7) AS item_7
FROM 
    TblA a
JOIN
    TblB b ON (a.LINKID = B.LINKID);

Output would be like (using table data not shown above, see full example link):

A_ID	LINKID	B_ID	OTHER	item_4	item_7
1	100	1	ID3	Value 4	null
2	200	2	ID4	null	50
2	200	3	ID5	null	null

Is there a way to dynamically do this mapping and build a query in SQL or Transact-SQL (T-SQL) instead of building the (SELECT) statements manually? I'm currently on MS SQL Server 2019.

Please see this dbfiddle link for a full tables and example: https://dbfiddle.uk/Atku04Bo.

• sql
• sql-server
• t-sql

Share Improve this question Follow edited Nov 21, 2024 at 19:07 Dale K 27.1k1515 gold badges5454 silver badges8282 bronze badges asked Nov 21, 2024 at 16:29 DBoxDBox 7111 silver badge77 bronze badges 4

• Please read : Why should I provide a Minimal Reproducible Example, even for a very simple SQL query? – MatBailie Commented Nov 21, 2024 at 18:05
• @MatBailie I don't understand your comment, I gave a minimal reproducible example with the dbfiddle, I thought it was too much to put here. – DBox Commented Nov 21, 2024 at 20:27
• This looks like a terribly complicated design which will be very difficult for anyone else to maintain or amend. Why don't you describe the business problem you are trying to solve and design an appropriate normalised solution. This looks a bit like a one true lookup table type of design, if you haven't heard of that you should do a search for Phil Factor articles. red-gate.com/simple-talk/blogs/… – Steve Ford Commented Nov 21, 2024 at 22:15
• @SteveFord I couldn't agree more. This is a product someone uses and I'm trying to make sense of the backend DB for them. – DBox Commented Nov 21, 2024 at 22:50

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

This can be done with dynamic SQL, using STRING_AGG to concatenate the dynamic SELECT statements.

DECLARE @sql NVARCHAR(MAX);
DECLARE @selectColumns NVARCHAR(MAX);
DECLARE @fromClause NVARCHAR(MAX);

SET @sql = '
SELECT a.ID AS A_ID, a.LINKID, b.ID AS B_ID, b.OTHER';

SET @fromClause = '
FROM TblA a
JOIN TblB b ON a.LINKID = b.LINKID;';

WITH LinkData AS (
    SELECT
        Key_Val,
        Type,
        DataType,
        Length,
        Description,
        'TblSet' + CAST(Type AS VARCHAR(10)) + 
        CASE DataType 
            WHEN 1 THEN '_A' 
            WHEN 2 THEN '_B' 
            ELSE '_C' END AS TblSetTable,
        CASE Type WHEN 1 THEN 'a.ID' ELSE 'b.ID' END AS IDColumn
    FROM TblLink
)
SELECT @selectColumns = STRING_AGG(
    '(SELECT [Value] FROM ' + TblSetTable + ' WHERE [ID] = ' + IDColumn + ' AND [Key_Val] = ' + CAST(Key_Val AS VARCHAR(10)) + ') AS [Item_' + CAST(Key_Val AS VARCHAR(10)) + ']',
    ', ')
FROM LinkData;

SET @sql = @sql + ', ' + @selectColumns + @fromClause;
EXEC sp_executesql @sql;

Output:

A_ID	LINKID	B_ID	OTHER	Item_2	Item_3	Item_4	Item_5	Item_7	Item_8	Item_9	Item_10	Item_15	Item_16	Item_17	Item_18
1	100	1	ID3	20	null	Value 4	30	null	null	null	null	Value 15	null	null	null
2	200	2	ID4	null	null	null	45	50	null	Value 9	60	Value 15_2	Value 16	null	null
2	200	3	ID5	null	null	null	45	null	null	Value 9	60	null	null	null	null

fiddle

本文标签： sqlCorrelate these tables dynamically (or automated)Stack Overflow