Dynamic SQL SQL Server 2012 Get Result into a #temp table -


i using sql server 2012. final result @sql temp table.here code far. help.

if object_id ('tempdb.dbo.#my_dt_cte') not null drop table #my_dt_cte create table #my_dt_cte ([rowid] int not null identity (1,1) ,[yyyymm] int ) ; my_dt_cte ( select convert(int,convert(varchar(6),eomonth(getdate(),-1),112)) [yyyymm] union select convert(int,convert(varchar(6),eomonth(getdate(),-2),112)) [yyyymm] union select convert(int,convert(varchar(6),eomonth(getdate(),-3),112)) [yyyymm] union select convert(int,convert(varchar(6),eomonth(getdate(),-4),112)) [yyyymm] union select convert(int,convert(varchar(6),eomonth(getdate(),-5),112)) [yyyymm] union select convert(int,convert(varchar(6),eomonth(getdate(),-6),112)) [yyyymm] ) insert #my_dt_cte select [yyyymm] my_dt_cte order [yyyymm] desc; -- select * #my_dt_cte  declare @columns nvarchar(max), @sql nvarchar(max); set @columns = n''; select @columns += n', p.' + quotename(yyyymm) (select p.yyyymm #my_dt_cte p group p.yyyymm) x; set @sql = n' select ' + stuff(@columns, 1, 2, '') + ' ( select p.yyyymm #my_dt_cte p ) j pivot ( count(yyyymm) yyyymm in (' + stuff(replace(@columns, ', p.[', ',['), 1, 1, '') + ') ) p;'; print @sql; exec sp_executesql @sql;

need bring result @sql temp table. 201605 201606 201607 201608 201609 201610

an easy way use global temporary table. can selected into in dynamic sql scope automatically have desired schema , still available after exits.

set @sql = n' select ' + stuff(@columns, 1, 2, '') + ' ##globaltemp ( select p.yyyymm #my_dt_cte p ) j pivot ( count(yyyymm) yyyymm in (' + stuff(replace(@columns, ', p.[', ',['), 1, 1, '') + ') ) p;';  exec sp_executesql @sql;  select *   ##globaltemp

however can cause issues naming clashes if code ever executed concurrently.

it possible use local temp table code more involved involves creating temp table @ upper scope , using dynamic sql alter dynamically determined schema before inserting it.

create table #t   (      dummy int   );  set @sql = 'alter table #t add   dummy2 int' + replace(replace(@columns, 'p.', ''), ']', '] int') + ';             alter table #t drop column dummy, dummy2;'  exec (@sql);  set @sql = n' insert #t select ' + stuff(@columns, 1, 2, '') + ' ( select p.yyyymm #my_dt_cte p ) j pivot ( count(yyyymm) yyyymm in (' + stuff(replace(@columns, ', p.[', ',['), 1, 1, '') + ') ) p;';  exec sp_executesql @sql;  select *   #t

-

Comments

Post a Comment

Popular posts from this blog

java - SSE Emitter : Manage timeouts and complete() -

i writing web app multiple listeners(evcentsource sse clients js) connected server . want store sse emitter per connected listener : can done in memory or other means assigning id each client able achieve far now question ; how send response/event specific client connected web app ? while doing sseemmiters stored either getting completed or timed out. how prevent ? how keep sseemmiter available open infinite time (till client closes) , send events selectively .
Read more

jquery - uncaught exception: DataTables Editor - remote hosting of code not allowed -

i'm trying implement crud operation using datatableseditor using datatables.but i'm getting error messages: 1)uncaught exception: datatables editor - remote hosting of code not allowed. please see http://editor.datatables.net details on how purchase editor license 2)typeerror: a.editor undefined 3) $.fn.datatable.editor not constructor" what reason? here configuration: <%@ page language="java" contenttype="text/html; charset=iso-8859-1" pageencoding="iso-8859-1"%> <!doctype html public "-//w3c//dtd html 4.01 transitional//en" "http://www.w3.org/tr/html4/loose.dtd"> <html> <head> <meta http-equiv="content-type" content="text/html; charset=iso-8859-1"> <title>insert title here</title> <link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.12/css/jquery.datatables.min.css"> <link re...
Read more

java - How to resolve error - package com.squareup.okhttp3 doesn't exist? -

i trying convert json java object use of gson in maven, following youtube video guidance - https://www.youtube.com/watch?v=vqgghm9pwe0 , theres error occurs when in main class error - package com.squareup.okhttp3 doesn't exist. code below: java package com.codebeasty.json; import com.squareup.okhttp3.okhttpclient; public class main { private static okhttpclient client = new okhttpclient(); public static void main (string [] args) { } } i put in dependency in pom.xml: <dependencies> <dependency> <groupid>com.squareup.okhttp3</groupid> <artifactid>okhttp</artifactid> <version>3.4.2</version> </dependency> <dependency> <groupid>com.google.code.gson</groupid> <artifactid>gson</artifactid> <version>2.8.0</version> </dependency> </dependencies> i dont understand why doesn't recognize com.squareup. there may need download? have downl...
Read more