sql server pivot?

hi experts, i have this query
select  
CodSolicitud,
FlagAplicacionFondos,
ApliPor1,
ApliCodProd1,
ApliCred1,
ApliPor2,
ApliCodProd2,
ApliCred2,
ApliPor3,
ApliCodProd3,
ApliCred3,
ApliPor4,
ApliCodProd4,
ApliCred4,
ApliPor5,
ApliCodProd5,
ApliCred5
FROM dbo.Solicitud
WHERE CodSolicitud = 1000030657
The result is a single record

In a new table dbo.Solicitud_Vertical these columns are a record
row 1:       ApliPor1, ApliCodProd1,      ApliCred1
row 2:       ApliPor2, ApliCodProd2,      ApliCred2
row 3:       ApliPor3, ApliCodProd3,      ApliCred3
row 4:       ApliPor4, ApliCodProd4,      ApliCred4
row 5:       ApliPor5, ApliCodProd5,      ApliCred5

As I can do to read the information table and be able to request the insertion in my new table dbo.Solicitud_Vertical


row-vertical.jpg
row-horizontal.jpg
enrique_aeoAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
wdosanjosConnect With a Mentor Commented:
PIVOT and UNPIVOT can only aggregate one column, but your scenario has two columns. So, the only way I know to accomplish the insert is by using UNION.
0
 
wdosanjosCommented:
You can try something like this:

Insert into dbo.Solicitud_Vertical (CodSolicitud, FlagApplicationFondos, ApliPor, ApliCodProd)
	select * from 
		(
		select CodSolicitud, FlagAplicacionFondos, ApliPor1 as ApliPor, ApliCodProd1 as ApliCodProd 
		  from dbo.Solicitud
		 where ApliPor1 is not null or ApliCodProd1 is not null
		union
		select CodSolicitud, FlagAplicacionFondos, ApliPor2 as ApliPor, ApliCodProd2 as ApliCodProd 
		  from dbo.Solicitud
		 where ApliPor2 is not null or ApliCodProd2 is not null
		union
		select CodSolicitud, FlagAplicacionFondos, ApliPor3 as ApliPor, ApliCodProd3 as ApliCodProd 
		  from dbo.Solicitud
		 where ApliPor3 is not null or ApliCodProd3 is not null
		union
		select CodSolicitud, FlagAplicacionFondos, ApliPor4 as ApliPor, ApliCodProd4 as ApliCodProd 
		  from dbo.Solicitud
		 where ApliPor4 is not null or ApliCodProd4 is not null
		union
		select CodSolicitud, FlagAplicacionFondos, ApliPor5 as ApliPor, ApliCodProd5 as ApliCodProd 
		  from dbo.Solicitud
		 where ApliPor5 is not null or ApliCodProd5 is not null
		) as s
	where CodSolicitud = 1000030657

Open in new window

0
 
enrique_aeoAuthor Commented:
it is working fine.
can be solved using pivot or UNPIVOT? and what would be the code?
0
All Courses

From novice to tech pro — start learning today.