Mdthbs

I have a table like this:

TB1:



COD | A001 | A002 | A003

 1    cars   baby   nasa

and then a second table:

TB2:



COD | NO_COL_TB1 | DESCRIPTION |

 1  |    A001    |  Something

 2  |    A002    |   lasagna

What I'm trying to do is something like this ( obviously this doesnt work )

select A001 as (select description from TB2 WHERE no_col_tb1= A001 )

      ,A002 AS (select description from TB2 WHERE no_col_tb1= A002 )

from TB1

i tried with inner joins and some dynamic sql but I just can't think in a logic for this. There's some questions about this but none of them could help me. I think this is impossible to do with a single statement and only possible with a Dynamic SQL.

EDIT:

The correct result would be:

SELECT COD,

       TB1.A001 AS 'Something',

       TB1.A002 AS 'Lasagna'

FROM TB1

and the result would be:

COD |SOMETHING | LASAGNA |

 1      cars       baby

Sorry to say but your table structure is difficult to work with considering what you want to do. There are various ways you can probably get the result, one way would be to use UNPIVOT and PIVOT, but it's ugly.

You could start by UNPIVOTing the date in TB1 from your columns to rows:

select *

from tb1 t1

unpivot 

(

    val

    for col in (A001, A002, A003)

) u

This is going to return the data in the format of:

| COD |  val |  col |

|-----|------|------|

|   1 | cars | A001 |

|   1 | baby | A002 |

|   1 | nasa | A003 |

Then you could take that result and join it to TB2:

select 

    d.COD,

    d.val,

    t2.DESCRIPTION

from

(

    select *

    from tb1 t1

    unpivot 

    (

        val

        for col in (A001, A002, A003)

    ) u

)d

inner join tb2 t2

    on d.col = t2.NO_COL_TB1;

Which gives the result:

| COD |  val | DESCRIPTION |

|-----|------|-------------|

|   1 | cars |   Something |

|   1 | baby |     lasagna |

Now you have your new column names in the Description and the val in rows, but you want them in columns, so now you can apply the PIVOT function to it:

select *

from

(

    select 

        d.COD,

        d.val,

        t2.DESCRIPTION

    from

    (

        select *

        from tb1 t1

        unpivot 

        (

            val

            for col in (A001, A002, A003)

        ) u

    )d

    inner join tb2 t2

        on d.col = t2.NO_COL_TB1

) x

pivot

(

    max(val)

    for description in (Something, Lasagna)

) p;

This generates the final result you want:

| COD | Something | Lasagna |

|-----|-----------|---------|

|   1 |      cars |    baby |

Now all that is great if you know all the columns that you need to UNPIVOT and then PIVOT, but if you don't then you'll need to use dynamic SQL to solve it. Which will look something like this:

DECLARE 

    @colsUnpivot AS NVARCHAR(MAX),

    @colsPivot AS NVARCHAR(MAX),

    @query  AS NVARCHAR(MAX)



select @colsUnpivot 

  = stuff((select ','+quotename(C.column_name)

           from information_schema.columns as C

           where C.table_name = 'tb1' and

                 C.column_name like 'A%'

           for xml path('')), 1, 1, '')



select @colsPivot 

  = stuff((select ','+quotename([DESCRIPTION])

           from Tb2

           for xml path('')), 1, 1, '')





set @query = 'select *

            from

            (

                select 

                    d.COD,

                    d.val,

                    t2.DESCRIPTION

                from

                (

                    select *

                    from tb1 t1

                    unpivot 

                    (

                        val

                        for col in ('+ @colsUnpivot +')

                    ) u

                )d

                inner join tb2 t2

                    on d.col = t2.NO_COL_TB1

            ) x

            pivot

            (

                max(val)

                for description in ('+@colsPivot+')

            ) p;';



exec sp_executesql @query;

It's long, but it should give you the same result. (dbfiddle demo)

DECLARE @SQL NVARCHAR(MAX)



SELECT @SQL = STUFF(

    ( SELECT ', ' + QUOTENAME(C.COLUMN_NAME) + ' AS ' + QUOTENAME(F.DESCRICAO)

FROM INFORMATION_SCHEMA.COLUMNS C 

    INNER JOIN TABBASE F ON C.COLUMN_NAME = F.NO_COL_TABBASE

WHERE C.TABLE_NAME = 'TABELA_ENTRADA'

ORDER BY C.ORDINAL_POSITION FOR XML PATH('')),1,2,'')



SELECT @SQL = 'SELECT ' + @SQL + ' FROM tabela_entrada'



PRINT @SQL



EXECUTE(@SQL)

jesus. Is there a better way than this? I didn't know i could use STUFF for this. I found this query on this microsoft page