Solved

sql server pivot?

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

773 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