sql server - How to convert rows into column in sql -


i have table,

table 1

and need convert

table 2

i have tried pivot can't figure out please me out .

pivot not work more 1 column out-of-the-box there several approaches solve this:

use table variable tests , please state sample data copy'n'pasteable. best mcve (minimal complete verifyable example) set code mine here.

declare @tbl table(id int, code int,employeename varchar(100),examname varchar(100),board varchar(100),result varchar(100)); insert @tbl values  (11537,12984,'thename','ssc','b04','1st') ,(11537,12984,'thename','hsc','b04','2nd') ,(11537,12984,'thename','ba(h)','u33','2nd');

this code first concatenate data 1 single column. allows pivot:

select p.* (     select tbl.id         ,tbl.code         ,tbl.employeename         ,'exam_' + cast(row_number() over(partition tbl.id order tbl.code) varchar(100)) colname         ,examname + ' (' + board + '): ' + result concatenated     @tbl tbl ) t pivot (     min(concatenated) colname in(exam_1,exam_2,exam_3 /*add many need*/) ) p

the result:

11537   12984   thename ssc (b04): 1st  hsc (b04): 2nd  ba(h) (u33): 2nd

the next code quite same, creates xml instead of plain text, allows separate data afterwards:

select p.id       ,p.code       ,p.employeename       ,e1       ,e1.value('(/exam/@examname)[1]','varchar(100)') examname1       ,e1.value('(/exam/@board)[1]','varchar(100)') board1       ,e1.value('(/exam/@result)[1]','varchar(100)') result1       ,e2       ,e2.value('(/exam/@examname)[1]','varchar(100)') examname2       ,e2.value('(/exam/@board)[1]','varchar(100)') board2       ,e2.value('(/exam/@result)[1]','varchar(100)') result2       ,e3       ,e3.value('(/exam/@examname)[1]','varchar(100)') examname3       ,e3.value('(/exam/@board)[1]','varchar(100)') board3       ,e3.value('(/exam/@result)[1]','varchar(100)') result3 (     select tbl.id         ,tbl.code         ,tbl.employeename         ,'exam_' + cast(row_number() over(partition tbl.id order tbl.code) varchar(100)) colname         ,(select examname [@examname],board [@board],result [@result] xml path('exam')) asxml     @tbl tbl ) t pivot (     min(asxml) colname in(exam_1,exam_2,exam_3 /*add many need*/) ) p outer apply (     select cast(p.exam_1 xml) e1           ,cast(p.exam_2 xml) e2           ,cast(p.exam_3 xml) e3 ) castedtoxml

the result:

11537   12984   thename <exam examname="ssc" board="b04" result="1st" />    ssc b04 1st <exam examname="hsc" board="b04" result="2nd" />    hsc b04 2nd <exam examname="ba(h)" board="u33" result="2nd" />  ba(h)   u33 2nd

this old-fashioned-pivot quite better normal pivot:

;with numberd (     select *           ,row_number() over(partition tbl.id order tbl.code) number     @tbl tbl ) select id,code,employeename       ,max(case when number=1 examname end) examname1       ,max(case when number=1 board end) board1       ,max(case when number=1 result end) result1       ,max(case when number=2 examname end) examname2       ,max(case when number=2 board end) board2       ,max(case when number=2 result end) result2       ,max(case when number=3 examname end) examname3       ,max(case when number=3 board end) board3       ,max(case when number=3 result end) result3 numberd group id,code,employeename

the last option dynamic sql...


-

Comments

Post a Comment

Popular posts from this blog

amazon web services - S3 Pre-signed POST validate file type? -

i know it's possible put limit on file size content-length-range header. possible validate file type? https://docs.aws.amazon.com/amazons3/latest/dev/httppostforms.html#policyconditions i see there content-type header, if set say, audio/mp3 allow mp3 files , return error if file not mp3? i found previous question answers mention validating file size: s3 direct upload restricting file size , type you can specify content-type @ post request. you can specify @ signature, post must done content-type: { "expiration": "2007-12-01t12:00:00.000z", "conditions": [ {"acl": "public-read" }, {"bucket": "johnsmith" }, {"content-type: "audio/mp3"} ] } creating html form (using aws signature version 4) edit: @ previous question found, checking content-type.
Read more

c# - Check Keyboard Input Winforms -

i wondering if instead of doing this protected override void onkeydown(keyeventargs e) { if (e.keycode == keys.a) console.writeline("the key down."); } i set bool method , this: if(keydown(keys.a)) // whatever here i've been sitting here ages trying figure out how it. can't wrap head around it. in case wondering, plan call bool inside different method, check input. since want perform action after pressing key, using keydown event enough. but in cases suppose want check if specific key down in middle of process, can use getkeystate method way: [dllimport("user32.dll", charset = charset.auto, exactspelling = true)] public static extern short getkeystate(int keycode); public const int key_pressed = 0x8000; public static bool iskeydown(keys key) { return convert.toboolean(getkeystate((int)key) & key_pressed); } you should know, each time check key state using example iskeydown(keys.a) method returns true if ke...
Read more