Turn rows into columns with their respective values

Asked

Viewed 21,403 times

5

I have the following table

idRegistro|Campo |Valor
   1      |Nome  |Zonaro
   1      |Email |[email protected]
   1      |campoX|valorX
   2      |Nome  |Fulano
   2      |tel   |1188889999
   2      |campoY|valorY

I need to create a select that returns the field column as the column name and all its values as a result:

idRegistro|Nome  |Email             |campoX|tel       |campoY
   1      |Zonaro|[email protected]|valorX|          |      
   2      |Fulano|                  |      |1188889999|valorY

How should I proceed in this case?

  • 1

    Could you explain your doubt better?

  • I don’t know how to transform the values of the FIELD column into columns and the values of the VALUE column into values of those columns

  • @kaizonaro In addition to Tobias' response, you can find other ways to pivot in the "Alas & Pivots" article. Log in: https://portosql.wordpress.com/2019/05/04/pivot/

3 answers

13


you must do a PIVOT in your table.

DECLARE @registros as table (
    idRegistro int,
    Campo varchar(50),
    Valor varchar(50)
)

INSERT INTO @registros VALUES (1, 'Nome', 'Zonaro');
INSERT INTO @registros VALUES (1, 'Email', '[email protected]');
INSERT INTO @registros VALUES (1, 'campoX', 'valorX');
INSERT INTO @registros VALUES (2, 'Nome', 'Fulano');
INSERT INTO @registros VALUES (2, 'tel', '1188889999');
INSERT INTO @registros VALUES (2, 'campoY', 'valorY');

SELECT * 
FROM @registros
PIVOT (
    MAX(Valor)
    FOR Campo IN
    ([Nome], [Email], [tel], [campoX], [campoY])
) AS pvt
ORDER BY idRegistro

however for it to work, you must know all the columns, in the example above ([Nome], [Email], [tel], [campoX], [campoY]).

if you know the columns only at runtime, you can mount the query dynamically, as in the example below.

DECLARE @sql nvarchar(max);    
DECLARE @campos varchar(max)
SELECT 
    @campos = COALESCE(@campos + ', ', '') + '[' + Campo + ']'
FROM (
    SELECT DISTINCT Campo 
    FROM @registros
) campos

SELECT @sql = N'
    SELECT * 
    FROM @registros
    PIVOT (
        MAX(Valor)
        FOR Campo IN
        (' + @campos + ')
    ) AS pvt
    ORDER BY idRegistro
'

EXEC sp_executesql @sql

1

Select * Into #Paises From _Países

just replace * with the desired columns.

1

You can be doing it this way.

select idRegistro,Nome  ,Email             ,campoX,tel       ,campoY
INTO #tabelatemp from suatabela

select * from #tabelatemp
--where idRegistro = 1

More details here

  • The fields differ for each case, hours I will have 3 fields, hours I will have 50. I need a way to turn all the results into columns

  • Use the *, but one thing like you mount select so that it changes the columns? is Pivot?

  • I don’t ride, that’s exactly what I need. I’m getting it from the anchor

  • Got it. Change your question to be clearer, change it to. "How do you change rows by columns with Pivot" something like that.

Browser other questions tagged

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