sql server - SQL: How to Pivot a Table with Pivot Columns Sharing the Same Name? -

-

this question has answer here:

i using sql server 2008 r2 version 10.50.1600.1.

i'm trying pivot table strings pivot column shares same name. data looks like.

 +------------+-----------+------------+ | patient_id | code_type | code_value | +------------+-----------+------------+ |          1 | icd9      | v70        | |          1 | icd9      | 401.9      | |          1 | icd9      | 616        | |          1 | icd9      | 338.21     | |          2 | icd9      | v10        | |          2 | icd9      | 250        | +------------+-----------+------------+

what i'm trying ...

 +------------+--------+--------+--------+--------+--------+--------+ | patient_id | icd9_1 | icd9_2 | icd9_3 | icd9_4 | icd9_5 | icd9_x | +------------+--------+--------+--------+--------+--------+--------+ |          1 | v70    | 401.9  | 616    | 338.21 | null   | null   | |          2 | v10    | 250    | null   | null   | null   | null   | +------------+--------+--------+--------+--------+--------+--------+

icd9_x can stretch infinity because don't know how many icd9 codes there given patient.

is there way in sql?

thanks!

update:

thanks help! have received 2 errors. looks pivoting requires values int because of sum right? there way use pivot table string values? icd9 codes strings.

secondly, hit unexpected error. says "the number of elements in select list exceeds maximum allowed number of 4096 elements." there solution large data set?

thanks again!

do have primary key or column use ordering make sure codes in correct order?

if have 1 can concatenate code_type value output of: 

row_number () on (partition patient_id, code_type order patient_id, /* codes order column here */)

... , able use pivot ( technet info ) concatenated values.

here code illustrate mean:

 -- preparing demo data per sample: declare @yourtable table (     id int identity (1,1) primary key,     patient_id int,     code_type varchar(20),     code_value varchar(20) )  insert @yourtable     (patient_id, code_type, code_value) values     (1, 'icd9', 'v70'),     (1, 'icd9', '401.9'),     (1, 'icd9', '616'),     (1, 'icd9', '338.21'),     (2, 'icd9', 'v10'),     (2, 'icd9', '250')  -- should starting point: select * @yourtable  -- suffix code_type: select     patient_id,      code_type + '_' + cast(             row_number () on (partition patient_id, code_type order patient_id, id)             varchar(20)         ) code_type,      code_value     @yourtable  -- ... , pivot: select     patient_id,     icd9_1,      icd9_2,      icd9_3,      icd9_4 (     select         patient_id,          code_type + '_' + cast(                 row_number () on (partition patient_id, code_type order patient_id, id)                 varchar(20)             ) code_type,          code_value             @yourtable ) data pivot (     max(code_value)     -- need list here:     code_type in (icd9_1, icd9_2, icd9_3, icd9_4) ) piv

Comments

Post a Comment

Popular posts from this blog

java - Static nested class instance -

-
this question has answer here: java inner class , static nested class 23 answers i came across in java, know static nested class why create instance of 'static'. isn't whole idea of 'static' use without instance? understand concept of inner classes, why (and frankly how possible to) create 'instance' of 'static' member @ ? why this: 1- outerclass.staticnestedclass nestedobject = new outerclass.staticnestedclass(); 2- nestedobject.anestedclassmethod(); and not this: 1- outerclass outerinstance=new outerclass(); 2- outerinstance.staticnestedclass.anestedclassmethod(); use on inner classes, keyword static indicates can access inner class without instance of outer class. for example: public class outer { public static class inner { /* code here. */ } } with construction, can create instance of inn
Read more

c# - Bluetooth LE CanUpdate Characteristic property -

-
i building xamarin.forms cross platform mobile app, uses monkey.robotics bluetoth low energy functionality. connecting mbed based implimentation of custom gatt service . in xamarin c#, triggers characteristic. canupdate property in monkey.robotics? this standard example c# based on: if (characteristic.canupdate) { characteristic.valueupdated += (s, e) => { debug.writeline("characteristic.valueupdated"); device.begininvokeonmainthread( () => { updatedisplay(characteristic); }); isbusy = false; // spin until first result received }; isbusy = true; characteristic.startupdates(); } this has been working, since changed own custom gatt service connecting to, canupdate property false. property , how triggered? me debugging gatt service code. thanks monkey.robotics open sour
Read more

JavaScript - Replace variable from string in all occurrences -

-
ok, know if have character '-' , want remove in places in string javascript, ... someword = someword.replace(/-/g, ''); but, when applying array of characters, s not working ... const badchars = ('\/:*?"<>|').split(''); let filename = title.replace(/ /g, '-').tolocalelowercase(); (let item = 0; item < badchars.length; item++) { // below not work global '/ /g' filename = filename.replace(/badchars[item]/g, ''); } any ideas? /badchars[item]/g looks badchars , literally, followed i , t , e , or m . if you're trying use character badchars[item] , you'll need use regexp constructor, , you'll need escape regex-specific characters. escaping regular expression has been well-covered . using that: filename = filename.replace(new regexp(regexp.quote(badchars[item]), 'g'), ''); but , don't want that. want character class: let filename = title.replac
Read more