How to properly use pivot funciton to combine multiple values (or columns) into within one column in SQL Server? -

-

i have table contains upc, month# , week# these 3 main columns. (screenshot below)

enter image description here

is possible create view or display table groups week numbers 1 column each unique upc there many rows displayed. (example this)

enter image description here

do need use pivot function group grouping sets achieve this?

thanks.

sql fiddle

ms sql server 2008 schema setup:

query 1:

declare @table table (recordid int, upc bigint, monthnumberonpromo int, weeknumberonpromo int) insert @table values  (1  , 2529230005 , 1   , 1)  , (2  , 2529230005 , 2   , 6), (3  , 2529230005 , 3   , 11) , (4  , 2529230005 , 4   , 15), (5  , 2529230005 , 6   , 22) , (6  , 2529230005 , 7   , 27), (7  , 2529230005 , 8   , 31) , (8  , 2529230005 , 9   , 35), (9  , 2529230007 , 1   , 1)  , (10 , 2529230007 , 2   , 6), (11 , 2529230007 , 3   , 11) , (12 , 2529230007 , 4   , 15), (13 , 2529230007 , 6   , 22) , (14 , 2529230007 , 8   , 31), (15 , 2529230007 , 9   , 35) , (16 , 2529230007 , 10   , 38), (17 , 2529230007 , 12  , 51)  select * (     select t.upc           ,left(datename(month ,  '1900' + right('00'                         + cast(t.monthnumberonpromo nvarchar(2)),2) + '01') , 3)  [month]           ,'yes' value           ,stuff((select ',' + cast(weeknumberonpromo nvarchar(10))                   @table                   t.upc = upc                   xml path(''),type)                   .value('.','nvarchar(max)'),1,1,'') [week_list]     @table t     group t.upc              ,left(datename(month ,  '1900' + right('00'                          + cast(t.monthnumberonpromo nvarchar(2)),2) + '01') , 3)      )t pivot (max(value)        [month]        in ([jan],[feb],[mar],[apr],[may],[jun],[jul],[aug],[sep],[oct],[nov],[dec])       )p

results:

|        upc |                week_list | jan | feb | mar | apr |    may | jun |    jul | aug | sep |    oct |    nov |    dec | |------------|--------------------------|-----|-----|-----|-----|--------|-----|--------|-----|-----|--------|--------|--------| | 2529230005 |    1,6,11,15,22,27,31,35 | yes | yes | yes | yes | (null) | yes |    yes | yes | yes | (null) | (null) | (null) | | 2529230007 | 1,6,11,15,22,31,35,38,51 | yes | yes | yes | yes | (null) | yes | (null) | yes | yes |    yes | (null) |    yes |

Comments

Post a Comment

Popular posts from this blog

javascript - how to protect a flash video from refresh？ -

-
<!doctype html> <html> <head> <meta http-equiv="content-type" content="text/html; charset=utf-8"> <title>flash video refresh</title> </head> <body> <div id="playerzone"><embed height="496" width="580" flashvars="flvid=19742822&amp;createtime=2012-4-19 13:46:33" wmode="opaque" allowscriptaccess="always" allowfullscreen="true" quality="high" bgcolor="#000000" name="player" id="player" src="swf/mine.swf" type="application/x-shockwave-flash"></div> <a onclick="return test();" id="test">click me popup div,and hide scrollbar</a> </body> the code upon： for example,when click a#test button,popup div,it's no problem,but want hide scrollbar while div popup. i used $('html').css('overflow','hidden') in...
Read more

visual studio 2010 - Connect to informix database windows form application -

-
i trying figure out how connect ibm informix database. have been doing research , have found threads 5 years ago examples not working. i have installed latest sdk ibm informix. i have included ibm.data.informix.dll references in project. i have included using ibm.data.informix; i adding button , on click testing conenction. debug error "sql0035n file "c:\users\adam\documents\visual studio 2010\projects\test\test\msg\en_us\db2nmp.xml" cannot opened." this file not exist , dont see anywhere in program files (x86)\ibm informix client sdk directory. my on click code is private void button1_click(object sender, eventargs e) { const string host = "192.168.obfuscated"; const string servicenum = "1525"; //port? const string server = "serverobfuscated"; const string database = "dbobfuscatedy"; const string user = "myusername"; const string password = ...
Read more

android - Associate same looper with different threads -

-
Image
in application, want create multiple threads working on single queue. create queue know need call looper.prepare() . i want other threads create should associated looper created first thread, how can achieve this? use 1 of java thread safe queue class java.util.concurrent package instead of looper achieve goal. it's more common practice. share queue between threads , post task queue entry 1 thread can entry thread. blockingqueue can - in 1 thread call take() , block thread until thread put() entry queue. that's all. producer consumer pattern example blockingqueue or concurrentlinkedqueue about blockingqueue
Read more