Arrange columns of one table according to positions as rows in other table in microsoft sql server

Question

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

  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

Do you have a query that you have tried to do this with? – Jenn Jun 30 '14 at 17:27 — Jenn, Jun 30 '14 at 17:27

Paul Maxwell · Accepted Answer · 2014-07-01T12:33:27.033

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,

see this sqlfiddle

Arrange columns of one table according to positions as rows in other table in microsoft sql server

1 Answers1