Solved

sql server pivot?

Posted on 2011-02-23
3
363 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

691 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