3
Hello, I’m having a hard time turning rows into columns on SQL Server.
I haven’t been able to write anything yet... I thought about PIVOT, but since it’s not always the same amount of columns, I’m not getting to think about how to do...
I have these 2 tables:
Expected result:
It’s for a report that.
2
Leooso, you need to do a PIVOT on top of an INNER JOIN, it would be something like this... 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:
But I don’t have an exact amount of flaws, you understand? Just as I may have 5 I may have 20, I may not be able to visualize your solution
Giving a better analysis I understood your solution, but I can not have fixed these following values: (In my case) ([Defeito 1],[Defeito 2],[Defeito 3],[Defeito 4]) For I don’t know how many may come into being...
Leo, look at the link I gave you (Turn rows into columns with their respective values) because the comrade did it with a variable number of columns too, the way you want.
Sorry, I hadn’t noticed, I’ll take a look
Again sorry for not paying attention, it worked here, thank you
Browser other questions tagged sql-server
You are not signed in. Login or sign up in order to post.
How about a Function or sub-consumption?
– Marconi
@Marconi, how would you do it? My biggest problem is being in, I do not have a set amount of columns, ie in this example I did with 4 defects, but I can have more as far as I can have less
– LeoHenrique
As if I had an accountant who showed me the item that has more defect and this would be my limit for that report, following then, I would have the other items with their respective defects. That is, if the item with the largest amount of defects is 5, then I will have 5 columns of defects and so, the other defects of each item, would be distributed in these 5 columns
– LeoHenrique