Turn rows into columns with their respective values

Question

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?

Answer 1

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

Answer 2

Select * Into #Paises From _Países

just replace * with the desired columns.

Answer 3

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