Solved

sql server pivot?

Posted on 2011-02-23
3
316 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:enrique_aeo
  • 2
3 Comments
 
LVL 23

Expert Comment

by:wdosanjos
ID: 34963020
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
 

Author Comment

by:enrique_aeo
ID: 34963350
it is working fine.
can be solved using pivot or UNPIVOT? and what would be the code?
0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 500 total points
ID: 34963755
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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now