Select Column Based on Column Name Stored in Another Table

Select columns from one table based on the column names from another table

I got it to work by doing what @lobo said with a slight change.

DECLARE @listStr varchar(MAX);
set @liststr =
        (
select [column] + ',' from dbo.columns where nwlevel = '1' for xml path('')
        )

DECLARE @query varchar(MAX);
set @query =
        (
        'SELECT ' + LEFT(@listStr, LEN(@listStr)-1) + ' FROM staff'
        )
execute(@query)

Get value based on column name stored in another table

You can use to_jsonb() to make a JSON for the row from "DocumentRegistryAttributes", with the column names as keys and then select the text from the JSON where the key is the text in "DocumentSubject"."List1Left".

SELECT *,
       to_jsonb(dra)->>ds."List1Left"
       FROM "DocumentSubject" ds
            LEFT JOIN "DocumentRegistryAttributes" dra
                      ON dra."DocumentSubjectId" = ds."Id";

Select column based on column name stored in another table

You cannot do this dynamically. But you can build a CASE statement that maps column names to columns:

SELECT DataTable.*, (
    SELECT CASE DataTable.ColC
        WHEN 'c1' THEN UserTable.c1
        WHEN 'c2' THEN UserTable.c3
        WHEN 'c3' THEN UserTable.c3
    END
    FROM UserTable
    WHERE UserTable.userid = 'id2'
) AS score
FROM DataTable
ORDER BY score DESC

How to retrieve a column name from a table that is stored as a value in another table

i add the [Last Refreshed] column to my tables and write this query and give me the correct answer

DROP TABLE  IF EXISTS #DB_DUMMY

CREATE TABLE  #DB_DUMMY (
[TABLENAME] VARCHAR(512),
[LAST_REFRESHED] VARCHAR(533)
);

DECLARE @COMMAND NVARCHAR(MAX)


SELECT @COMMAND = STRING_AGG(' INSERT INTO #DB_DUMMY SELECT DISTINCT '+CHAR(39)+T.name+CHAR(39)+',['+C.name+'] FROM '+S.name+'.'+T.name + ' GO', CHAR(13)+CHAR(10))
FROM sys.all_columns C
INNER JOIN sys.tables T ON C.object_id = T.object_id
INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
WHERE C.name = 'Last Refreshed'


PRINT(@COMMAND)

EXEC(@COMMAND)


SELECT * FROM #DB_DUMMY

two first line with IF EXISTS is new syntax in sql server 2017

Select Column as Column names of values in another table in SQL SERVER

To get this done with your current design you will have to use dynamic SQL. I wont go into the pros and cons of dynamic SQL here. For more info you can go here. I highly suggest reading up on it if you are not familiar with dynamic SQL.

CREATE TABLE #TableA (Type int, Col1 int, Col2 int, Col3 int, Col4 int, Col5 int);

CREATE TABLE #TableB (ColId varchar(50), ColName varchar(50));

INSERT INTO #TableA VALUES
(101, 1, 2, 3, 2, 5),
(102, 4, 2, 3, 2, 0),
(103, 2, 1, 0, 0, 5),
(103, 7, 2, 0, 0, 5),
(105, 8, 3, 0, 0, 0);

INSERT INTO #TableB VALUES
('Col1', 'Math'),
('Col2', 'English'),
('Col3', 'French'),
('Col4', 'Fine Arts'),
('Col5', 'Biology');

DECLARE @Sql nvarchar(MAX);

/*   Build SELECT   */
SET @Sql = 'SELECT ';

SELECT @Sql = @Sql + ColId + ' AS ''' + ColName + ''', ' FROM #TableB;

/*   Remove trailing comma   */
SET @Sql = (SELECT LEFT(@Sql, LEN(@Sql)-1));

/*   Add FROM   */
SET @Sql = @Sql + ' FROM #TableA';

/*   Output query   */
SELECT @Sql;

/*   Execute query   */
EXEC sp_executesql @Sql;

DROP TABLE #TableA;
DROP TABLE #TableB;

---

Related Topics