convert rows to columns in sql 2012 [closed]

Question

convert rows to columns in sql 2012 [closed]

Navigation

#1 by (10 votes)
#2 by (2 votes)

1

hi how are I have this query

SELECT 
    nom_emp,
    ISNULL (LENTES,0)[LENTE],
    Fecha_Gestion,
    ISNULL (AYUDA_ODONTOLOGICA,0)[AYUDA ODONTOLOGICA],
    Fecha_Gestion
FROM 
    (SELECT me.nom_emp,Fecha_Gestion,TB.Tipo_Benef,Monto
       FROM Tabla_Principal AS tp INNER JOIN Mes AS m ON M.id=TP.mes
    INNER JOIN Tipo_Beneficio AS tb ON TB.Id=TP.Tipo_Benef
    INNER JOIN Maestro_Empl AS me ON me.No_emp = tp.No_emp
    ) AS  TablaDatos
PIVOT 
    (
        SUM (Monto)
        FOR Tipo_Benef IN (LENTES,AYUDA_ODONTOLOGICA)
    )AS PivotTable
GO

As a result I get

My question is why the column DENTAL HELP gives me 0 since it has data

I wanted to know if you can help me or say if I am doing wrong with PIVOT

these are the data I have in the table

sql sql-server sql-server-2012

asked by norma 07.03.2017 в 16:12

source

2 answers

Save two values and two scores in localstorage call function from Google map InfoWindows on ionic?

score 10 · Answer 1

The PIVOT command requires that you use exactly the value you want to "pivot", in FOR . If you check your data well, you can see that the value AYUDA ODONTOLOGICA has a space, it is not written as AYUDA_ODONTOLOGICA . Therefore, you must use that value in your PIVOT :

SELECT 
    nom_emp,
    ISNULL (LENTES,0)[LENTE],
    Fecha_Gestion,
    ISNULL ([AYUDA ODONTOLOGICA],0)[AYUDA ODONTOLOGICA],
    Fecha_Gestion
FROM 
    (SELECT me.nom_emp,Fecha_Gestion,TB.Tipo_Benef,Monto
       FROM Tabla_Principal AS tp INNER JOIN Mes AS m ON M.id=TP.mes
    INNER JOIN Tipo_Beneficio AS tb ON TB.Id=TP.Tipo_Benef
    INNER JOIN Maestro_Empl AS me ON me.No_emp = tp.No_emp
    ) AS  TablaDatos
PIVOT 
    (
        SUM (Monto)
        FOR Tipo_Benef IN (LENTES,[AYUDA ODONTOLOGICA])
    )AS PivotTable
;

score 2 · Answer 2

In your table Type_Benefit make sure that the field Type_Benef contains VALUE_HEADS values, if these do not exist recorded in the table when removing ISNULL of the line:

ISNULL(AYUDA_ODONTOLOGICA,0)[AYUDA ODONTOLOGICA]

You will get the column with pure NULL value

Now that if ODONTOLOGICAL HELP is ODONTOLOGICAL AID, do not use the underscore and leave it as:

FOR Tipo_Benef in (LENTES, [AYUDA ODONTOLOGICA])

Note: Blanks in the cell at the beginning and end should be considered ex:

FOR Tipo_Benef in (LENTES, [AYUDA ODONTOLOGICA ]) --Espacio en blanco al final