sql server - Transpose the columns to rows -

-

i have result set table such following picture:

enter image description here

returned following query:

select [id],[siteid],[variableid],[qualifierid],[value],[valuedate],[insertdate],[insertuserid] ,[deletedate],[deleteuserid],[datastatus] mytable

i transpose according other picture:

enter image description here

i have tried follow pivot samples couldn't reach result. attempt (i did not include columns):

select  [1281] q1, [1282] q2, [1283] q3, [1284] q4, [1285] q5, [1286] q6, [1287] q7, [1288] q8, [1289] q9  ( select  id, qualifierid, value, insertdate badmentries variableid=1000) p pivot (     max(value)     qualifierid in     ( [1281] , [1282], [1283], [1284], [1285], [1286], [1287], [1288], [1289] ) ) pvt

but did not work, because result get: enter image description here

where error?! thanks, diego

remove id column sub-query not using in pivot clause , in final select statement used grouping:

select  [1281] q1, [1282] q2, [1283] q3, [1284] q4, [1285] q5, [1286] q6, [1287] q7, [1288] q8, [1289] q9  ( select  qualifierid, value, insertdate badmentries variableid=1000) p pivot (     max(value)     qualifierid in     ( [1281] , [1282], [1283], [1284], [1285], [1286], [1287], [1288], [1289] ) ) pvt

explanation:

let's have following table:

declare @datasource table (     [recordid] tinyint    ,[team] varchar(12)    ,[matchid] tinyint    ,[score] tinyint );  insert @datasource ([recordid], [team], [matchid], [score]) values (1, 'a', 1, 50)       ,(2, 'a', 2, 10)       ,(3, 'b', 1, 20)       ,(4, 'b', 2, 40)       ,(5, 'b', 3, 70);

enter image description here

and want make pivot [matchid] column , write following query:

select *  (     select *     @datasource ) ds pivot (     max([score]) [matchid] in ([1], [2], [3]) ) pvt;

i getting following result (similar you):

enter image description here

the issue "caused" recordid column unique , results group it. so, fix issue need remove it:

select *  (     select [team]           ,[matchid]           ,[score]     @datasource ) ds pivot (     max([score]) [matchid] in ([1], [2], [3]) ) pvt;

and works fine:

enter image description here


Comments

Post a Comment

Popular posts from this blog

qt - Using float or double for own QML classes -

-
i've created components , helper methods using c++ , qt used in qml gui. of methods calculate gui elements (size, transformations, scene graph items creation). target arm 32 bit using opengl / eglfs render output. opengl using float , qml using real (defined double ). what should use in c++ do not lose precision? if use qreal ( double ) shouldn't lose precision. if git grep float in qtbase.git , can find out bit more: dist/changes-5.2.0-**************************************************************************** dist/changes-5.2.0-* important behavior changes * dist/changes-5.2.0-**************************************************************************** dist/changes-5.2.0- dist/changes-5.2.0- - qt compiled qreal typedef'ed double on dist/changes-5.2.0: platforms. qreal float on arm chipsets before. guarantees more dist/changes-5.2.0- consistent behavior between platforms qt supports, binary dist/chan...
Read more

Create Outlook appointment via C# .Net -

-
i need create outlook appointment using microsoft.office.interop.outlook , while can work locally on own workstation , works if run web application via server's browser, not work when connect server externally. hence, think might permission issue. i changed application pool identity " localsystem " don't access denied error. unfortunately, doesn't work. the app behaves if appointment created, appointment doesn't appear in outlook. here include file on top of aspx.cs page using outlook = microsoft.office.interop.outlook; here code using pop appointment. outlook.application apptapp = new outlook.application(); outlook.appointmentitem appt = apptapp.createitem(outlook.olitemtype.olappointmentitem) outlook.appointmentitem; appt.subject = txtfirstname.text + " " + txtlastname.text; appt.body = txtcomment.text; appt.alldayevent = false; appt.start = datetime.parse(txtreminderdate.text + " 8:00 am"); appt.end = datetime.p...
Read more

ios - Swift Array Resetting Itself -

-
i have 2 arrays: pointhistory , datehistory . have nstimer , , when timer runs out, action called. in action appending int pointhistory , , current date string datehistory . here timer action: func timeraction() { let steppervalue = int(stepper.value) employee.numberofpoints = steppervalue networking.saveemployee(employee, completion: { (error) -> void in if error != nil { let alert = uialertcontroller(title: "error", message: "there error updating \(self.employee.name)'s points", preferredstyle: .alert) let alertaction = uialertaction(title: "ok", style: .cancel, handler: nil) alert.addaction(alertaction) self.presentviewcontroller(alert, animated: true, completion: nil) } }) var dateformatter = nsdateformatter() dateformatter.dateformat = "mm-dd-yyyy" dateformatter.timezone = nstimezone.localtimezone() var date = nsdate() var ...
Read more