Navegando por distintos foros me he encontrado esta maravilla, un script de SQL server que a partir de una consulta nos devuelve el resultado en formato JSON.
-- la consulta
select *
into #json_base
from tabla;
-- a partir de aqui no tocar
declare @columns table (
id int identity primary key,
name sysname,
datatype sysname,
is_number bit,
is_date bit);
insert into @columns(name, datatype, is_number, is_date)
select columns.name, types.name,
case when number_types.name is not NULL then 1 else 0
end as is_number,
case when date_types.name is not NULL then 1 else 0
end as is_date
from tempdb.sys.columns
join tempdb.sys.types
on (columns.system_type_id = types.system_type_id)
left join (values ('int'), ('real'), ('numeric'),
('decimal'), ('bigint'), ('tinyint')) as number_types(name)
on (types.name = number_types.name)
left join (values ('date'), ('datetime'), ('datetime2'),
('smalldatetime'), ('time'), ('datetimeoffset')) as date_types(name)
on (types.name = date_types.name)
where object_id = OBJECT_ID('tempdb..#json_base');
declare @field_list varchar(max) = STUFF((
select '+'',''+' + QUOTENAME(QUOTENAME(name, '"') + ':', '''')
+ '+' + case when is_number = 1
then 'COALESCE(LTRIM('
+ QUOTENAME(name) + '),''null'')'
when is_date = 1
then 'COALESCE(QUOTENAME(LTRIM(convert(varchar(max), '
+ QUOTENAME(name) + ', 126)),''"''),''null'')'
else 'COALESCE(QUOTENAME('
+ QUOTENAME(name) + ',''"''),''null'')'
end
from @columns
for xml path('')),
1, 5, '');
create table #json_result (
id int identity primary key,
line varchar(max));
declare @sql varchar(max) = REPLACE(
'insert into #json_result '
+ 'select '',{''+{f}+''}'' '
+ 'from #json_base', '{f}', @field_list);
exec(@sql);
update #json_result
set line = STUFF(line, 1, 1, '')
where id = 1;
select '['
UNION ALL
select line
from #json_result
UNION ALL
select ']';
drop table #json_base;
drop table #json_result;
Por cierto, lo he encontrado aquí
0 Comentarios.