I have a single table with some foreign key records repeated a maximum of 5 times according to entries, eg:
id_ok|fornecedor|valor_final|escolha|ativo|
--------------------------------------------
o_11 | DOPE |R$ 20800 | SIM | NOK |
o_11 | REGAL |R$ 50000 | SIM | NOK |
o_11 | BRUNE |R$ 76000 | SIM | NOK |
o_51 | BRUNE |R$ 27600 | SIM | NOK |
o_51 | DOPE |R$ 20760 | SIM | OK |
q_21 | HOLA |R$ 20000 | SIM | NOK |
q_21 | DOPE |R$ 67050 | SIM | OK |
q_21 | GREAT |R$ 90800 | SIM | NOK |
o_11 | GREAT |R$ 24400 | SIM | OK |
o_11 | DOPE |R$ 40000 | SIM | NOK |
-------------------------------------------
I need results that merge id_ok
and put all columns of it in a single line.
id_ok|fornecedor_1|valor_final_1|escolha_1|fornecedor_2|valor_final_2|escolha_2|fornecedor_3|valor_final_3|escolha_3|fornecedor_4|valor_final_4|escolha_4
---------------------------------------------------------------------------------------------------------------------------------------------------------
o_11 |DOPE |R$ 20800 |SIM |REGAL |R$ 50000 |SIM |BRUNE |R$ 76000 |SIM
o_51 |BRUNE |R$ 76000 |SIM |DOPE |R$ 20760 |SIM
q_21 |HOLA |R$ 20000 |SIM |GREAT |R$ 90800 |SIM
--------------------------------------------------------------------------------------------------------------------------------------------------------
The columns fornecedor_x
, valor_final_x
and escolha_x
range from 1 to 5, so they do not need to be dynamic.
Think of using PIVOT TABLE, but I could not do it.
NOTE: SQL SERVER 2008