Hello, I'm having trouble turning rows into columns in SQL Server .
I could not write anything yet ... I thought of PIVOT , but since it's not always the same amount of columns, I can not think of how to do ...
I have these 2 tables:
Expectedresult:
It's for a report.
Leooso, you need to do a PIVOT over an INNER JOIN, it would be something like ... example
SELECT equ.Id, equ.Equipamento, def.*
FROM equipamento equ
INNER JOIN defeito def
ON equ.Id = def.EquipamentoID
Now the PIVOT:
DECLARE @registros as table (
id int,
equipamento varchar(50),
Defeito1 varchar(50),
Defeito2 varchar(50),
Defeito3 varchar(50),
Defeito4 varchar(50)
)
SELECT *
FROM @registros
PIVOT (
MAX(Valor)
FOR Campo IN
([Nome], [Email], [tel], [campoX], [campoY])
) AS pvt
ORDER BY idRegistro
See also:
Leo,
... If you know the columns at run-time only, 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