sql server - How to do Pivoting for the below query? -


i have record set under

agreementid     feedbackdate            dispositioncode 0003sbml00151   2017-03-08 00:00:00.000 ptp 0004sbhl00705   2017-03-17 00:00:00.000 bptp 0007sbml01987   null                    null 0026mss00108    2017-05-20 00:00:00.000 ptp 0026mss00108    2017-03-22 00:00:00.000 ptp 0026mss00108    2016-12-30 00:00:00.000 bptp 0026mss00108    2016-12-29 00:00:00.000 bptp 0026mss00108    2016-12-28 00:00:00.000 bptp 0037sbhl02361   null                    null 0038sbml00291   2017-05-04 00:00:00.000 ptp 0038sbml00291   2017-04-24 00:00:00.000 bptp 0038sbml00291   2017-04-11 00:00:00.000 nc 0038sbml00291   2016-12-22 00:00:00.000 ptp 0038sbml00291   2016-12-09 00:00:00.000 dc

the desired output be

agreementid     l1      l2      l3      l4      l5 0003sbml00151   ptp     null    null    null    null 0004sbhl00705   bptp    null    null    null    null 0007sbml01987   null    null    null    null    null 0026mss00108    ptp     ptp     bptp    bptp    bptp 0037sbhl02361   null    null    null    null    null 0038sbml00291   ptp     bptp    nc      ptp     dc

sql schema

declare @t table(agreementid varchar(50),feedbackdate varchar(50),dispositioncode varchar(10)) insert @t  select '0003sbml00151','2017-03-08 00:00:00.000','ptp' union select '0004sbhl00705','2017-03-17 00:00:00.000','bptp' union select '0007sbml01987',null,null union select '0026mss00108','2017-05-20 00:00:00.000','ptp' union select '0026mss00108','2017-03-22 00:00:00.000','ptp' union select '0026mss00108','2016-12-30 00:00:00.000','bptp' union select '0026mss00108','2016-12-29 00:00:00.000','bptp' union select '0026mss00108','2016-12-28 00:00:00.000','bptp' union select '0037sbhl02361',null,null union select '0038sbml00291','2017-05-04 00:00:00.000','ptp' union select '0038sbml00291','2017-04-24 00:00:00.000','bptp' union select '0038sbml00291','2017-04-11 00:00:00.000','nc' union select '0038sbml00291','2016-12-22 00:00:00.000','ptp' union select '0038sbml00291','2016-12-09 00:00:00.000','dc'  select * @t

here attempt

;with cte1 as(  select agreementid,  abc = stuff(              (select '.' + dispositioncode                @t t1               t1.agreementid = t2.agreementid --and t1.rn = t2.rn               xml path (''))              , 1, 1, '') @t t2 group agreementid)  --select * --from cte1 ,cte2 as(  select agreementid,      x= iif(charindex('.',abc,1) null,'null value',iif(charindex('.',abc,1) = 0,'single value','multiple value'))          --,col1 = substring(abc,1,iif(charindex('.',abc,1) = null,0,iif(charindex('.',abc,1) = 0,len(abc),(charindex('.',abc,1)-1))))         --,charindex('.',abc,1)         ,abc cte1) ,cte3 as( select      agreementid     ,col1 =iif(x = 'null value', null,iif(x='single value',substring(abc,1,len(abc)),substring(abc,1,(charindex('.',abc,1)-1))))     ,abc     ,othercols = iif(x = 'multiple value',substring(abc,charindex('.',abc,1)+1,len(abc)),'') cte2)  select      agreementid      ,l1 = iif(col1 null, '--',col1)     ,l2 = iif(parsename(othercols,4)is null, '--',parsename(othercols,4))     ,l3 = iif(parsename(othercols,3)is null, '--',parsename(othercols,3))     ,l4 = iif(parsename(othercols,2)is null, '--',parsename(othercols,2))     ,l5 = iif(parsename(othercols,1)is null, '--',parsename(othercols,1))    cte3

disadvantages

a) slow query

b) failed below case

agreementid      feedbackdate           dispositioncode 0002sbml01241   2017-05-04 00:00:00.000 today 0002sbml01241   2017-04-24 00:00:00.000 ptp 0002sbml01241   2017-04-11 00:00:00.000 ptp 0002sbml01241   2016-12-22 00:00:00.000 ptp

actual

agreementid     l1      l2  l3  l4  l5 0002sbml01241   today   --  ptp ptp ptp

expected

 agreementid     l1     l2  l3  l4  l5 0002sbml01241   today   ptp ptp ptp --

thanks support in advance.

this in case dynamic levels:

declare @t table(agreementid varchar(50),feedbackdate varchar(50),dispositioncode varchar(10)) insert @t  select '0003sbml00151','2017-03-08 00:00:00.000','ptp' union select '0004sbhl00705','2017-03-17 00:00:00.000','bptp' union select '0007sbml01987',null,null union select '0026mss00108','2017-05-20 00:00:00.000','ptp' union select '0026mss00108','2017-03-22 00:00:00.000','ptp' union select '0026mss00108','2016-12-30 00:00:00.000','bptp' union select '0026mss00108','2016-12-29 00:00:00.000','bptp' union select '0026mss00108','2016-12-28 00:00:00.000','bptp' union select '0037sbhl02361',null,null union select '0038sbml00291','2017-05-04 00:00:00.000','ptp' union select '0038sbml00291','2017-04-24 00:00:00.000','bptp' union select '0038sbml00291','2017-04-11 00:00:00.000','nc' union select '0038sbml00291','2016-12-22 00:00:00.000','ptp' union select '0038sbml00291','2016-12-09 00:00:00.000','dc'  select   'l'+convert(varchar(255),row_number()over(partition agreementid order agreementid))'rno',* test @t order agreementid  declare @levels nvarchar(max),@sql nvarchar(max)  select @levels= stuff((select distinct ','+rno test  xml path('')),1,1,'')  set @sql= 'select agreementid,'+@levels+' (select agreementid,dispositioncode,rno test )as temp'+' pivot'+' (max(dispositioncode) rno in ('+@levels+') )as pvt'  exec sp_executesql @sql

Comments

Post a Comment

Popular posts from this blog

python - Operations inside variables -

this question has answer here: evaluating mathematical expression in string 12 answers i want make python script finds maximum possible output using 2 operations. problem says there syntax error @ 2nd last line, in b in there. how can fix it? x = 0 = int(input("1st number:")) c = int(input("2nd number:" )) e = int(input("3rd number:" )) in range(4): if == 0: b = "+" elif == 1: b = "-" elif == 2: b = "/" else: b = "*" j in range(4): if j == 0: d = "+" elif j == 1: d = "-" elif j == 2: d = "/" else: d = "*" k = b c d e print(k) if you're after variables e on single line, couldn...
Read more

Generic Map Parameter java -

i having lot of methods of type: public static list<eduusers> getdetails(class c, map<string, integer> params) { list<eduusers> details = null; session session = hibernateutil.getsessionfactory().opensession(); transaction tx = null; try { tx = session.begintransaction(); criteria cr = session.createcriteria(c); (map.entry<string, integer> entry : params.entryset()) { cr.add(restrictions.eq(entry.getkey(), entry.getvalue())); } details = cr.list(); tx.commit(); } catch (exception asd) { log.debug(asd.getmessage()); if (tx != null) { tx.commit(); } } { session.close(); } return details; } i trying have generic method them , have written far: public static <t> list<t> getdetails(class<t> c, class<t> b) { l...
Read more

arrays - What causes a java.lang.ArrayIndexOutOfBoundsException and how do I prevent it? -

what arrayindexoutofboundsexception mean , how rid of it? here code sample triggers exception: string[] name = {"tom", "dick", "harry"}; for(int = 0; i<=name.length; i++) { system.out.print(name[i] +'\n'); } your first port of call should documentation explains reasonably clearly: thrown indicate array has been accessed illegal index. index either negative or greater or equal size of array. so example: int[] array = new int[5]; int boom = array[10]; // throws exception as how avoid it... um, don't that. careful array indexes. one problem people run thinking arrays 1-indexed, e.g. int[] array = new int[5]; // ... populate array here ... (int index = 1; index <= array.length; index++) { system.out.println(array[index]); } that miss out first element (index 0) , throw exception when index 5. valid indexes here 0-4 inclusive. correct, idiomatic for statement here be: for (int index = 0; index <...
Read more