Organize columns in rows for equal ID in SQL server

Asked

Viewed 531 times

2

I have a single table with some key records(Foreign key) repeated at most 5 times according to entries, example:

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 the id_ok and put in a single row all its columns.

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 don’t have to be dynamic.

Think about using PIVOT TABLE, but I could not do.

OBS: SQL SERVER 2008

sql-server sql-server-2008 pivot

  • I do not know if there is how you define the column dynamically no... I believe that in the name and quantity will have to be fixed.

2 answers

5


Using the approach PIVOT and with an indefinite number (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

  • The two answers worked very well, but I will choose yours because it offers the option of indefinite number of suppliers, and approaches PIVOT table, which was the method I started working.

  • This is not quite the approach PIVOT. It is an approach with dynamic query construction. Furthermore, it is correct.

3

I think it is not the case of Pivot Tables because it is not a simple transposition. I decided to do so:

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;

See SQL Fiddle with Schema and SQL working.

Browser other questions tagged sql-server sql-server-2008 pivot

You are not signed in. Login or sign up in order to post.