Show columns that have SQL SERVER value

Question

Show columns that have SQL SERVER value

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

Good I have this table that has as 100 columns as the image and the query but what I want is only to obtain the columns that have amount

just choose these columns:

Numero prestatamo
NumeroCuota
Fechavencimiento
columns that have an amount are shown:

select numerocredito as  'NumeroPrestamo' , Numerocuota as 'Numero Cuota', Fechavencimiento as 'Fecha de Vencimiento' ,
SEGUROSASISTENCIAHOSPITALARIA,SEGURODEDESGRAVAMEN,
SEGUROVOLUNTARIO,
SEGURODEGARANTIA ,
SEGURODEINCENDIO,
SEGUROFAMILIASEGURA,
SEGUROTEPROTEGEMOS,
LEGALIZACIÓNNOTARIAL,
SERVICIODETASACION,
FONDODEGARANTIAFINANCIADO ,
PRECANCELACIONOPERACIONES, 
DESFASEPLAZOPRIMERPAGO,
MIGRACIONORO ,
ASISTTECNICA ,
CARGOPTMONOCOBRADOS,
CERTIFICADODEGRAVAMEN,
GASTVISITAEINSPECCFO,
GASTOSCARTANOTARIAL,
GASTOSDEARANCELES ,
GASTOSDEPROTESTO,
GASTOSDEVIGILANCIA,
GASTOSNOTARIALES,
INTSUSPENSO ,
INTCREDITOCASTIGADOS,
LOSLAURELES,
MIGRACION,
OTROS ,
OTROSCARGOS ,
OTROSCARGOSLOSLAURELES,
OTROS36 ,
PORCOBRARREGISTRALES ,
REGINTERESNOCOBRADOS ,
VIGILANCIACULTIVOS ,
VISITASCOBRANZACLIENT ,
COMISIONES ,
CARGOSFIJOS ,
IMPUESTOALASTRANSACCIONESFINANCIERAS ,
INTERESADELANTADO ,
COMISIONFINANCIADA ,
COMISIONDIFERIDA ,
GASTOSJUDICIALES ,
INTERESREESTRUCTURA ,
SEGUROGARCUOTA ,
DESCUENTOENCOMPRADECARTERA ,
PREMIOENCOMPRADECARTERA ,
FONDODEGARANTIA ,
IMPUESTOALACIRCULACIONDECAPITALES ,
CONTRIBUCIONSIBSEGURODESGRAVAMENMICROEMPRESA ,
CONTRIBUCIONSIBSEGURODESGRAVAMENOLLADEORO ,
SEGURODECREDITO ,
INTSUSPENSOORCAII  ,
GASTOSORCA2,
PENALIDAD ,
GASTOSORCA3 ,
OTROSCARGOSORCAIV,
INTSUSPENSOORCA3 ,
INTSUSPENSOORCA4 ,
OTROSCARGOSORCA5 ,
INTSUSPENSOORCA5 ,
OTROSCARGOSORCAVI ,
INTSUSPENSOORCAVI ,
OTROSCARGOSORCAVII,
INTSUSPENSOORCAVII,
OTROSCARGOSORCAIX ,
INTSUSPENSOORCAIX ,
OTROSCARGOSORCAX ,
INTERESSUSPENSOORCAX,
OTROSCARGOSORCAXI,
INTSUSPENSOORCAXI,
OTROSCARGOSORCAXII ,
INTSUSPENSOORCAXII,
SEGDESSOLIDARIDAD ,
OTROSCARGOSORCAXIII ,
INTSUSPENSOORCAXIII ,
INTERESENSUSPENSOORCAVIII,
OTROSCARGOSORCAVIII ,
INTENSUSPENSOORCAXIV,
OTROSCARGOSORCAXIV,
OTROSCARGOSORCAXV,
INTENSUSPENSOORCAXV


 from prueba

sql sql-server

asked by PieroDev 21.02.2017 в 23:05

source

2 answers

Connect PHP linux to a database hosted on the windows (DB POSTGRESQL)? Sort a FirebaseRecyclerAdapter

score 1 · Answer 1

If what you want is to find out which fields to include, you can check in the SQL Server the structure of the table to determine which fields are of type money, numeric, decimal, etc. (of the type of data they are using for these amounts ), and then manually write these fields, one by one in the query:

select numerocredito as  'NumeroPrestamo' , Numerocuota as 'Numero Cuota', Fechavencimiento as 'Fecha de Vencimiento' ,
GASTVISITAEINSPECCFO,
GASTOSCARTANOTARIAL,
GASTOSDEARANCELES ,
GASTOSDEPROTESTO,
GASTOSDEVIGILANCIA,
GASTOSNOTARIALES
from prueba

You could also obtain the list of fields that are of the type that you handle for amount, by means of the metadata of the table, review this question , get the list of fields and include them in the selection of your query.

Now, if you want to include in the same metadata query of the table to include those fields of type money, numeric, etc., together with the data itself, that is not possible.

score 0 · Answer 2

I would try with a dynamic sql:

declare @sql varchar(2000)

select @sql ='select numerocredito , Numerocuota, Fechavencimiento'
 + case when sum (GASTVISITAEINSPECCFO)>0 then ',GASTVISITAEINSPECCFO' else '' end
 + case when sum (GASTOSCARTANOTARIAL)>0 then ',GASTOSCARTANOTARIAL' else '' end
 + case when sum (GASTOSDEARANCELES)>0 then ',GASTOSDEARANCELES' else '' end
 + case when sum (GASTOSDEPROTESTO)>0 then ',GASTOSDEPROTESTO' else '' end
 + case when sum (GASTOSDEVIGILANCIA)>0 then ',GASTOSDEVIGILANCIA' else '' end
 + case when sum (GASTOSNOTARIALES)>0 then ',GASTOSNOTARIALES' else '' end
 + 'from prueba'
from prueba;

Exec(@sql);