Solved

sql server pivot?

Posted on 2011-02-23
3
352 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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

685 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