根据位置将一个表的列排列为microsoft sql server中其他表中的行

In my first table A, I am having data which i need to display, but displaying order would be differnt from the one here . Display order depends on position in the second table B. I want the field having lowest position comes first and with name price

在我的第一个表A中,我有需要显示的数据,但显示顺序与此处的顺序不同。显示顺序取决于第二个表B中的位置。我希望具有最低位置的字段首先出现并且具有名称价格

  Field_21     Field_31    field_41
    112            wed         www
    111            tue         dse
    123             sun        edwd




    Name            POSITION         Name
    Field_31           2              ask
    Field_21           1              bid
    Field_41           0              price

Final Data would be like

最终数据就像

price      bid         ask
www        112         wed
dse        111         tue
edwd       123         sun

1 个解决方案

#1

Try the following:

请尝试以下方法:

DECLARE @tbl VARCHAR(60), @sql VARCHAR(8000)

SET @tbl = 'tblData' -- change for the table                     

SELECT @sql = 'SELECT '
              + STUFF(
                         (
                           SELECT      ', ' + ColumnName + ' as ' + ColumnLabel
                           FROM       columnOrder
                           ORDER BY Position
                           FOR XML PATH('')
                         )
                      , 1, 1, ''
                     )
              + ' FROM '
              + @tbl


--SELECT @sql
EXEC (@sql)

note as you cannot have 2 columns called [name] in one table, I use ColumnName and ColumnLabel,

请注意,因为在一个表中不能有2个名为[name]的列,所以我使用ColumnName和ColumnLabel,

see this sqlfiddle

看到这个sqlfiddle

#1