Arrange columns in rows for equal IDs in SQL server

2

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

    
asked by anonymous 04.12.2015 / 15:48

2 answers

5

Using the PIVOT and undefined approach (not limited to 5) of columns follows example:

DECLARE 
  @MaxCount INT, 
  @a CHAR(10) = 'fornecedor', @f NVARCHAR(MAX), 
  @b CHAR(11) = 'valor_final', @v NVARCHAR(MAX), 
  @c CHAR(7) = 'escolha', @e NVARCHAR(MAX), 
  @s VARCHAR(MAX), @l VARCHAR(MAX),
  @i INT, @j NVARCHAR(10)

SELECT @MaxCount = MAX(r), @i = 1 FROM (SELECT id_ok, row_number() OVER (PARTITION BY id_ok ORDER BY id_ok) r FROM Tbl) T
WHILE @i <= @MaxCount
    SELECT @j = cast(@i AS NVARCHAR(10)), @f = COALESCE(@f+', ', '') + '['+@a+'_'+@j+']', @v = COALESCE(@v+', ', '') + '['+@b+'_'+@j+']', @e = COALESCE(@e+', ', '') + '['+@c+'_'+@j+']', @s = COALESCE(@s+', ', '') + '['+@a+'_'+@j+'],['+@b+'_'+@j+'],['+@c+'_'+@j+']', @i = @i+1

SELECT 
    @l = 'SELECT * FROM (SELECT id_ok, ''@2_'' + CAST(row_number() OVER (PARTITION BY id_ok ORDER BY id_ok) AS VARCHAR) A, @2 FROM Tbl) T PIVOT (MAX(@2) FOR A IN (@1)) PVT',
    @s = 'SELECT A1.id_ok, '+@s+' FROM ('+REPLACE(REPLACE(@l, '@1', @f), '@2', @a)+') A1
    LEFT OUTER JOIN ('+REPLACE(REPLACE(@l, '@1', @v), '@2', @b)+') A2 ON A1.id_ok = A2.id_ok
    LEFT OUTER JOIN ('+REPLACE(REPLACE(@l, '@1', @e), '@2', @c)+') A3 ON A2.id_ok = A3.id_ok'

EXEC(@s)

In SQL Fiddle

    
04.12.2015 / 22:59
3

I do not think it's the case with Pivot Tables because it's not a simple transposition. I solved it like this:

select id_ok, max(fornecedor_1) as fornecedor_1, max(valor_final_1) as valor_final_1, max(escolha_1) as escolha_1, 
  max(fornecedor_2) as fornecedor_2, max(valor_final_2) as valor_final_2, max(escolha_2) as escolha_2, 
  max(fornecedor_3) as fornecedor_2, max(valor_final_3) as valor_final_3, max(escolha_3) as escolha_3, 
  max(fornecedor_4) as fornecedor_2, max(valor_final_4) as valor_final_4, max(escolha_4) as escolha_4, 
  max(fornecedor_5) as fornecedor_2, max(valor_final_5) as valor_final_5, max(escolha_5) as escolha_5
  from (select id_ok, 
       (case when Numero = 1 then fornecedor end) as fornecedor_1,
       (case when Numero = 1 then valor_final end) as valor_final_1,
       (case when Numero = 1 then escolha end) as escolha_1,
       (case when Numero = 2 then fornecedor end) as fornecedor_2,
       (case when Numero = 2 then valor_final end) as valor_final_2,
       (case when Numero = 2 then escolha end) as escolha_2,        
       (case when Numero = 3 then fornecedor end) as fornecedor_3,
       (case when Numero = 3 then valor_final end) as valor_final_3,
       (case when Numero = 3 then escolha end) as escolha_3,
       (case when Numero = 4 then fornecedor end) as fornecedor_4,
       (case when Numero = 4 then valor_final end) as valor_final_4,
       (case when Numero = 4 then escolha end) as escolha_4,
       (case when Numero = 5 then fornecedor end) as fornecedor_5,
       (case when Numero = 5 then valor_final end) as valor_final_5,
       (case when Numero = 5 then escolha end) as escolha_5
from (select *, row_number() over (partition by id_ok order by fornecedor) as Numero
from Tabela) as Origem) Teste
group by id_ok;

View SQL Fiddle with Schema and SQL working .

    
04.12.2015 / 20:23