{"id":110,"date":"2015-12-08T07:39:25","date_gmt":"2015-12-08T07:39:25","guid":{"rendered":"http:\/\/www.onepage.co.kr\/wordpress\/?p=110"},"modified":"2015-12-08T07:39:25","modified_gmt":"2015-12-08T07:39:25","slug":"mssql-pivot-sample","status":"publish","type":"post","link":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/2015\/12\/08\/mssql-pivot-sample\/","title":{"rendered":"mssql pivot sample"},"content":{"rendered":"<p>&nbsp;<\/p>\n<div>select * from (<\/div>\n<div>SELECT ROW_NUMBER() OVER (ORDER BY [01]) AS [00]<\/div>\n<div>\u00a0 \u00a0 \u00a0, left(cast([01] as varchar),7) [99]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [01]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [02]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [03]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [04]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [05]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [06]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [07]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [08]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [09]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [10]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [11]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [12]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [13]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [14]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [15]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [16]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [17]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [18]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [19]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [20]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [21]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [22]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [23]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [24]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [25]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [26]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [27]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [28]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [29]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [30]<\/div>\n<div>\u00a0 \u00a0 \u00a0, [31]<\/div>\n<div><\/div>\n<div>\u00a0 FROM (SELECT dt+&#8217;_&#8217;+amst+&#8217;_&#8217;+pmst+&#8217;_&#8217;+apst d<\/div>\n<div>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0, DATEPART(d, dt) dd<\/div>\n<div>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0, left(dt,7) ww<\/div>\n<div><\/div>\n<div>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM ( select \u00a0solardate dt, isnull(cast(am_state as varchar),&#8217;0&#8242;) amst, isnull(cast(pm_state as varchar),&#8217;0&#8242;) pmst, isnull(cast(ap_state as varchar),&#8217;0&#8242;) apst from sldate a left outer join agent_work b on a.solardate = b.work_date<\/div>\n<div>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0and agent_idx = 1115<\/div>\n<div>\u00a0 and \u00a0(ISNULL(am_state,0)+ ISNULL(pm_state,0) + ISNULL(ap_state,0)) &gt; 0<\/div>\n<div>\u00a0 where solardate between convert(varchar(10),Dateadd(m,0,&#8217;2015-10-01&#8242;),120) and convert(varchar(10),dateadd(ss,-1,Dateadd(m,12,&#8217;2015-10-01&#8242;)),120)<\/div>\n<div>\u00a0 ) T<\/div>\n<div>\u00a0 \u00a0 \u00a0 \u00a0 ) a<\/div>\n<div>\u00a0PIVOT( MIN(d) FOR dd IN<\/div>\n<div>\u00a0 \u00a0 \u00a0 \u00a0 ( [00], [99], [01], [02], [03], [04], [05], [06], [07], [08], [09], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]) ) a<\/div>\n<div>\u00a0 ) ta<\/div>\n<div>\u00a0 join copy_t ct on 1 = 1 and \u00a0no &lt; 3<\/div>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; select * from ( SELECT ROW_NUMBER() OVER (ORDER BY [01]) AS [00] \u00a0 \u00a0 \u00a0, left(cast([01] as varchar),7) [99] \u00a0 \u00a0 \u00a0, [01] \u00a0 \u00a0 \u00a0, [02] \u00a0 \u00a0 \u00a0, [03] \u00a0 \u00a0 \u00a0, [04] \u00a0 \u00a0 \u00a0, [05] \u00a0 \u00a0 \u00a0, [06] \u00a0 \u00a0 \u00a0, [07] \u00a0 \u00a0 \u00a0, [08] \u00a0 \u00a0 \u00a0, [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-110","post","type-post","status-publish","format-standard","hentry","category-mssql"],"_links":{"self":[{"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/110","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/comments?post=110"}],"version-history":[{"count":1,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/110\/revisions"}],"predecessor-version":[{"id":111,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/110\/revisions\/111"}],"wp:attachment":[{"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/media?parent=110"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/categories?post=110"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/tags?post=110"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}