Select Columns from One Table Based on the Column Names from 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)

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

You have to use dynamic SQL to do this - here is one fairly simple way:

DECLARE @SQL nvarchar(4000) = '';

SELECT @SQL = @SQL +', '+ name 
FROM dbo.AA_COLUMN_NAMES
WHERE active = 1

SET @SQL = 'SELECT id, username' + @SQL + ' FROM dbo.AA_PAYMENT_DETAILS'

EXEC(@SQL)

How to use value from one table as column name in another table in SQL query

SQL requires all identifiers are fixed in your expressions at the time the query is parsed. But you could do a CASE expression like this:

SELECT
    F.date,
    F.flight_num,
    F.dep_apt_code as dep,
    F.arr_apt_code as arr,
    CASE F.dep_apt_code 
      WHEN 'ATL' THEN W.ATL 
      WHEN 'IAD' THEN W.IAD 
      WHEN 'JFK' THEN W.JFK
      WHEN 'SFO' THEN W.SFO 
    END AS dep_weather
FROM Flights AS F JOIN Weather AS W ON F.date = W.date;

The comment above says that you should normalize your structure. This means to store the weather per city on individual rows instead of in columns on one row.

date	apt_code	weather
2022-06-01	ATL	cloudy
2022-06-01	IAD	windy
2022-06-01	JFK	rainy
2022-06-01	SFO	sunny
2022-06-02	ATL	sunny
2022-06-02	IAD	rainy
2022-06-02	JFK	rainy
2022-06-02	SFO	windy

Select the column names from one table and Corresponding data from another table

If I understand you correctly, given the following:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FormData](
    [formDataID] [int] IDENTITY(1,1) NOT NULL,
    [formRowNumber] [int] NOT NULL,
    [formColumnID] [int] NOT NULL,
    [formDataDate] [datetime] NULL,
    [formDataInt] [int] NULL,
    [formDataChar] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[FormData] ON
INSERT [dbo].[FormData] ([formDataID], [formRowNumber], [formColumnID], [formDataDate], [formDataInt], [formDataChar]) VALUES (1, 1, 1, NULL, NULL, N'John')
INSERT [dbo].[FormData] ([formDataID], [formRowNumber], [formColumnID], [formDataDate], [formDataInt], [formDataChar]) VALUES (2, 1, 2, NULL, NULL, N'Private')
INSERT [dbo].[FormData] ([formDataID], [formRowNumber], [formColumnID], [formDataDate], [formDataInt], [formDataChar]) VALUES (3, 1, 3, NULL, NULL, N'123456')
INSERT [dbo].[FormData] ([formDataID], [formRowNumber], [formColumnID], [formDataDate], [formDataInt], [formDataChar]) VALUES (4, 2, 1, NULL, NULL, N'Bill')
INSERT [dbo].[FormData] ([formDataID], [formRowNumber], [formColumnID], [formDataDate], [formDataInt], [formDataChar]) VALUES (5, 2, 2, NULL, NULL, N'Captain')
INSERT [dbo].[FormData] ([formDataID], [formRowNumber], [formColumnID], [formDataDate], [formDataInt], [formDataChar]) VALUES (6, 2, 3, NULL, NULL, N'789352')
SET IDENTITY_INSERT [dbo].[FormData] OFF

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FormColumnDefinition](
    [formColumnID] [int] IDENTITY(1,1) NOT NULL,
    [formColumnDataType] [varchar](50) NOT NULL,
    [formColumnName] [varchar](50) NOT NULL,
    [formColumnLabel] [varchar](50) NOT NULL,
    [formColumnSeqNumber] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[FormColumnDefinition] ON
INSERT [dbo].[FormColumnDefinition] ([formColumnID], [FormColumnDataType], [formColumnName], [formColumnLabel], [formColumnSeqNumber]) VALUES (1, N'System.String', N'Name', N'Name', 0)
INSERT [dbo].[FormColumnDefinition] ([formColumnID], [FormColumnDataType], [formColumnName], [formColumnLabel], [formColumnSeqNumber]) VALUES (2, N'String.String', N'Rank', N'Rank', 1)
INSERT [dbo].[FormColumnDefinition] ([formColumnID], [FormColumnDataType], [formColumnName], [formColumnLabel], [formColumnSeqNumber]) VALUES (3, N'System.Int32', N'SerialNumber', N'Serial Number', 2)
SET IDENTITY_INSERT [dbo].[FormColumnDefinition] OFF

You could use a pivot command like:


SELECT formRowNumber, [Name],[Rank],[SerialNumber]
FROM
(SELECT fcd.formColumnName, fd.formRowNumber, fd.formDataChar
FROM FormColumnDefinition fcd INNER JOIN FormData fd
    ON fcd.formColumnID = fd.formColumnID) AS src
PIVOT
(
MAX(formDataChar)
FOR formColumnName IN ([Name],[Rank],[SerialNumber])
) AS pvt
ORDER BY pvt.formRowNumber

to get:


formRowNumber   Name    Rank    SerialNumber
1               John    Private 123456
2               Bill    Captain 789352

The problem with PIVOT is that you have to know the column names ahead of time. If you don't mind a little dynamic SQL, you can work around this too. Here's an example that I shamelessly stole from Andras at http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx.



DECLARE @cols NVARCHAR(2000);
SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                '],[' + formColumnName
                        FROM FormColumnDefinition
                        ORDER BY '],[' + formColumnName
                        FOR XML PATH('')
                      ), 1, 2, '') + ']';                

DECLARE @query NVARCHAR(4000);
SET @query = N'SELECT formRowNumber, ' + @cols + 
    'FROM (SELECT fcd.formColumnName, fd.formRowNumber, fd.formDataChar ' +
    'FROM FormColumnDefinition fcd INNER JOIN FormData fd ' +
    'ON fcd.formColumnID = fd.formColumnID) AS src PIVOT ' +
    '(MAX(formDataChar) FOR formColumnName IN ('+ @cols + ')) AS pvt ORDER BY pvt.formRowNumber;';

EXECUTE(@query);

Select column names from one table based off of values in column of another table

These is one type of example.

select * from table1 join table2 on table1.col1= table2.col2

we follow these above syntax.

select * from table1 join table2 on table1.col1= table2.foo

These is the how find out column_name is present in another table

SELECT * FROM(    SELECT letter  FROM `Table_2` ) a JOIN
(SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='database_name' 
    AND `TABLE_NAME`='Table_1') b ON a.letter= b. COLUMN_NAME

Thank you.

Select Columns from One Table Based on the Column Names from Another Table