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
Could you explain your doubt better?
– Marconi
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
@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/
– José Diz