2
I have much search a way to mount a query in sql server but have not been very successful.
First of all, I have a system that controls industrial patio in which there are organizational levels and I have the structure that has the name of the sector. The organizational can increase or decrease depending on the demand and structure has a one to many relationship with itself.
Ex.:
> Organizacional {[Id: 1, Nm: Administracao], [Id: 2, Nm: Carregamento],
> [Id:3, Nm: Descarga], ...}
>
> Estrutura {[Id: 1, Nm: XPTO, Organizacional_Id: 1], [Id: 2, Nm:
> Sementes, Organizacional_Id: 2], [Id: 3, Nm: Animal,
> Organizacional_Id: 2], [Id: 4, Nm: XPTO4, Organizacional_Id: 1], [Id:
> 5, Nm: Semente, Organizacional_Id: 3], ...}
Well, I wonder if it is possible to do a recursive query where the result would be structure and the columns name would be provided by organizational
Ex.:
> [Organizacional.Nm]+'_Id',[Organizacional.Nm]+'_Nm' Estrutura.Id > , Estrutura.Nm
and so on.
You will need to use Pivot, http://www.devmedia.com.br/pivot-no-sql-server-invertendo-linhas-e-colunas-em-um-exemplo-real/28318
– Tiago Oliveira de Freitas
https://msdn.microsoft.com/pt-br/library/ms175972%28v=sql.120%29.aspx , I believe this is what is in sql server CTE usage
– Motta
Thanks Motta and Tiago, I could not do as I wanted but I got close. I used CTE for structure and pivot for result delivery.
– Rodrigo Barbosa