Dynamic SQL - How to use a value from a result as a column name?

Question

I am working on a dynamic SQL query for a MsSQL stored procedure.

There is a table search.ProfileFields that contains the actual column names in a table I need to query.

My goal is to have the SQL select the specific column in the table, dynamically from its parent query..

A little confusing, heres an example:

DECLARE @sql nvarchar(max)

SELECT @sql = '
    SELECT 
           pfs.SectionID, 
           pfs.SectionName, 
           pfs.Link,
           (
                SELECT 
                        pf.FieldID,
                        pf.FieldTitle,
                        pf.FieldSQL,
                        pf.Restricted,
                        pf.Optional,
                        (
                            SELECT 
                                pf.FieldSQL
                            FROM 
                                Resources.emp.EmployeeComplete as e
                            WHERE
                                e.QID = @QID
                        ) as Value
                FROM 
                    search.ProfileFields as pf
                WHERE 
                    pf.SectionID = pfs.SectionID
                ORDER BY
                    pf.[Order]
                FOR    XML PATH (''field''), ELEMENTS, TYPE, ROOT (''fields'')
           ) 
    FROM 
        search.ProfileFieldSections as pfs
    WHERE
        pfs.Status = 1
    FOR    XML PATH (''data''), ELEMENTS, TYPE, ROOT (''root'')'

PRINT @sql
EXECUTE sp_executesql @sql, N'@QID varchar(10)', @QID = @QID

In the inner most select. I am querying pf.FieldSQL. I am looking for the actual value that was received by the parent select.

search.ProfileFields has a column called FieldSQL with a few results such as Name, Age, Location.

That is what I am trying to get my inner most select to do.

SELECT Name FROM ... - Name in this case comes from the value of pf.FieldSQL.

How can I go about querying a dynamic column name in this situation?

score 1 · Accepted Answer · answered Jul 03 '18 at 02:59

Have a look at this answer for a couple of suggestions. If your table definition is complex or changes occasionally you probably should use pivot. Here's one that might work for you, so long as column names in the FieldSQL column are well defined, there are not too many of them, and they don't ever change or get added to:

DECLARE @sql nvarchar(max)

SELECT @sql = '
    SELECT 
       pfs.SectionID, 
       pfs.SectionName, 
       pfs.Link,
       (
            SELECT 
                pf.FieldID,
                pf.FieldTitle,
                pf.FieldSQL,
                pf.Restricted,
                pf.Optional,
                (
                    SELECT case pf.FieldSQL 
                        when 'Name' then e.Name
                        when 'DOB' then convert(nvarchar(10), e.DOB, 126)
                        -- ... etc.
                        -- NOTE: may need to aggregated depending on uniqueness of QID:
                        -- when 'Name' then min(e.Name)
                        -- when 'DOB' then convert(nvarchar(10), min(e.DOB), 126)
                        end
                    FROM 
                        Resources.emp.EmployeeComplete as e
                    WHERE
                        e.QID = @QID
                ) as Value
            FROM 
                search.ProfileFields as pf
            WHERE 
                pf.SectionID = pfs.SectionID
            ORDER BY
                pf.[Order]
            FOR    XML PATH (''field''), ELEMENTS, TYPE, ROOT (''fields'')
       ) 
    FROM 
        search.ProfileFieldSections as pfs
    WHERE
        pfs.Status = 1
    FOR    XML PATH (''data''), ELEMENTS, TYPE, ROOT (''root'')'

PRINT @sql
EXECUTE sp_executesql @sql, N'@QID varchar(10)', @QID = @QID

I like the approach to this and may have to use it as a plan b. My issue is we do have a lot of fields and the end goal was to be able to just have a single table i could drop a record in and have it work. Might be too complex with this setup though. — SBB, Jul 03 '18 at 03:12
Have a look at PIVOT then, this should get you what you need — pcdev, Jul 03 '18 at 03:13

score 0 · Answer 2 · answered Jul 03 '18 at 14:01

0

Take a look at "PIVOT" operators here PIVOT Thisshould give you some ideas how to use them.

answered Jul 03 '18 at 14:01

Danielson Sanches

124
4

Dynamic SQL - How to use a value from a result as a column name?

2 Answers2