SQL PIVOT what am I doing wrong?

Question

SQL PIVOT what am I doing wrong?

Navigation

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

1

select id, SGA, ZLN, GSI, CON
from (select origen, id from ExpedientesR_2016)
pivot (count(*) for origen in ('SGA' as SGA, 'ZLN' as ZLN, 'GSI' as GSI, 'CON' as CON)) as pv

only marks error near pivot. 'SGA', 'ZLN', 'GSI', 'CON' are the possible values of origin.

Solved. Thanks for your comments and help.

sql

asked by Miguel Marti 19.09.2016 в 09:33

source

2 answers

Convert from JPG to TIFF in Android (Java) How to generate a query of two foreign keys that point to the same table?

score 1 · Answer 1

You are making three mistakes:

You must give an alias to the clause query from.

You can not use count (*), you must specifically have a column

Use the Pivot values directly as column names,

With that in mind, a possible solution would be this:

with 
ExpedientesR_2016 as (
          select 1 id, 'SGA' origen
union all select 2, 'SGA'
union all select 3, 'CON'
union all select 4, 'ZLN'
union all select 5, 'SGA'
union all select 6, 'CON'
union all select 7, 'SGA'
union all select 8, 'ZLN'
union all select 9, 'GSI'
union all select 10, 'SGA'
union all select 11, 'ZLN'
union all select 12, 'GSI'
)
select *
from (select origen, id from ExpedientesR_2016) x  
pivot (count(id) for origen in ([SGA], [ZLN], [GSI], [CON])) as pv

score 0 · Answer 2

0

Try like this:

select *
from (select origen, id from ExpedientesR_2016) as tabla1
pivot (count(origen) for origen in ("SGA" , "ZLN" , "GSI" , "CON" )) as pv

answered by 19.09.2016 в 15:51