SQL Server List the sql output columns in a specific order. The order of the columns are kept on another table

wbrvyc0a 于 2023-11-16 发布在其他

关注(0)|答案(1)|浏览(119)

I have 2 tables as below. The first table holds the order of the SQL columns I want to output. The second table has the transaction data with matching column names to the first table. Note: All the column names in the second table is a data row in the first table and matched to the "TableColumnName" field. Client can change the output order of the columns from zGenericOrder table.

E.g. Country is the first column I want to output from the zAddress table as it is the column I want to output first (this is kept in zGenericOrder table)

Pretty much my final output required, based on this example is something like below. This requires to do dynamically.

SELECT  Country,FirstName,LastName,Suburb,Address1,Address2 FROM dbo.zAddress

Is this achievable using SQL only?

sql-server

来源：https://stackoverflow.com/questions/77396062/list-the-sql-output-columns-in-a-specific-order-the-order-of-the-columns-are-ke

1条答案

按热度按时间

qlckcl4x1#

This is just a simple dynamic sql exercise:

-- Prepare data
select *
into zGenericOrder
from 
(
    VALUES  (1, 2, 1, N'Country')
    ,   (1, 3, 2, N'FirstName')
    ,   (1, 4, 3, N'LastName')
    ,   (1, 5, 4, N'Suburb')
    ,   (1, 6, 5, N'Address')
    ,   (1, 7, 6, N'Address2')
) t (ClientID, id,OrderBy,TableColumnName)

select *
into zAddress
from 
(
    VALUES  (1, N'John', N'Nash', 23, N'AubumRd', N'Sydney', N'Australia')
    ,   (2, N'Johnny', N'Bing', 34, N'KappRd', N'Sydney', N'Australia')
) t (Id,FirstName,LastName,Address,Address2,Suburb,Country)

-- Build dynamic sql
DECLARE @cols NVARCHAR(MAX)

SELECT  @cols = STRING_AGG(QUOTENAME(TableColumnName), ',') WITHIN GROUP (ORDER BY OrderBy)
FROM    zGenericOrder
WHERE   ClientID = 1 -- your client

-- Execute the select
EXEC('SELECT ' + @cols + ' FROM zAddress')

To get a list of columns i use STRING_AGG which creates a CSV list of columns in form of: Col1,Col2,Col3. WITHIN GROUP (ORDER BY OrderBy) ensures correct order.

Then you just execute the select by concatenating your column list.

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server List the sql output columns in a specific order. The order of the columns are kept on another table

1条答案

相关问题

热门标签

最新问答