MS SQL How to pivot rows of 2 columns into multiple new columns using 1 pivot

I was wondering if it’s possible to improve the pivot I used to cater the values of two columns into multiple new columns using 1 pivot instead of using 2 and joining them.

I need the value of 2 of my columns, Date and Amount to be pivoted based on Type. I already did a single pivot separately on Date and Amount column then used inner join to combine them.

WORKING QUERY: http://sqlfiddle.com/#!18/34054/3

I used the pivot method mentioned here for the working query: Pivot rows into multiple columns

SELECT     aa.subscriber_id,      aa.msisdn,     aa.name,     aa.age,     bb.TOPUP as topup_date,     aa.TOPUP as topup_amount,     bb.VOICE as voice_date,     aa.VOICE as voice_amount,     bb.SMS as sms_date,     aa.SMS as sms_amount FROM   (SELECT *   FROM (       SELECT distinct       a.subscriber_id,      a.msisdn,     a.name,     a.age,      a.transaction_type,      a.amount       FROM trans a       INNER JOIN       (         SELECT max(transaction_date) mxdate, subscriber_id, transaction_type         FROM trans         WHERE CAST(transaction_date as DATE) BETWEEN '2019-01-01' and '2019-01-31'         GROUP BY subscriber_id,transaction_type        ) b         ON a.subscriber_id = b.subscriber_id         AND a.transaction_date = b.mxdate         AND a.transaction_type = b.transaction_type   ) AS SourceTable PIVOT( max(amount) FOR [transaction_type] IN ([TOPUP],    [VOICE],   [SMS])) AS PivotTable) aa INNER JOIN   (SELECT *   FROM (       SELECT distinct       a.subscriber_id,      a.msisdn,     a.name,     a.age,      a.transaction_type,      a.transaction_date       FROM trans a       INNER JOIN       (         SELECT max(transaction_date) mxdate, subscriber_id, transaction_type         FROM trans         WHERE CAST(transaction_date as DATE) BETWEEN '2019-01-01' and '2019-01-31'         GROUP BY subscriber_id,transaction_type        ) b         ON a.subscriber_id = b.subscriber_id         AND a.transaction_date = b.mxdate         AND a.transaction_type = b.transaction_type   ) AS SourceTable PIVOT( max(transaction_date) FOR [transaction_type] IN ([TOPUP],    [VOICE],   [SMS])) AS PivotTable) bb   ON aa.subscriber_id = bb.subscriber_id   AND aa.msisdn = bb.msisdn   AND aa.name = bb.name   AND aa.age = bb.age 

Error in pivot selection algorithm for merge phase [Sorting]

In the paper Comparison Based Sorting for Systems with Multiple GPUs, the authors describe the selection of a pivot element with respect to the partition on the first GPU (and its mirrored counterpart on the other GPU-partition). That pivot element is crucial for being able to merge the two partitions, given that we have already sorted them on each GPU locally.

However, the pseudo-code for that pivot-selection, as shown in the paper, doesn’t seem to reflect the whole truth since when implementing it 1:1, the selected pivot element is off by some elements in some cases, depending on the input – the amount of elements to sort and therefore the amount of elements per partition (the chunk of data that each GPU gets).

To get more specific, the problem is – to my understanding – that the while loop is exited too early due to the stride being reduced down to zero before the correct pivot element has been found. In general, the approach is binary search-like, where the range of where the pivot can fall, is halved each iteration.

Can anyone spot what needs to be done here?

Here is a C++ implementation of the pivot selection:

size_t SelectPivot(const std::vector<int> &a, const std::vector<int> &b) {   size_t pivot = a.size() / 2;   size_t stride = pivot / 2;   while (stride > 0)   {     if (a[a.size() - pivot - 1] < b[pivot])     {       if (a[a.size() - pivot - 2] < b[pivot + 1] &&           a[a.size() - pivot] > b[pivot - 1])       {         return pivot;       }       else       {         pivot = pivot - stride;       }     }     else     {       pivot = pivot + stride;     }     stride = stride / 2;   }   return pivot; } 

P.S.: I tried ceiling the stride in order to not skip iterations when the stride is odd, but this introduced the issue of moving out of bounds of the array and even after handling those cases by clipping to the array bounds, the pivot was not always correct.

Add Total Row To PIVOT Query

I want to add a TOTAL monthly row to my query, below is my DDL, how can I have an additional row, under the last employeename that is labeled TOTAL and it shows the SUM() of the sales for ALL employees for that month?

Create Table #empSales (     employeename varchar(100)     ,saleamt decimal(10,2)     ,saleMonth varchar(100) )  Insert Into #empSales VALUES ('James', '1.00', 'January') ,('Richard', '3.28', 'January') ,('Barb', '4.13', 'January')   Select  employeeName ,SUM(January) As JanAMt ,SUM(February) As FebAMt ,SUM(March) As MarAMt ,SUM(April) As AprAMt ,SUM(May) As MayAMt ,SUM(June) As JunAMt ,SUM(July) As JulAMt ,SUM(August) As AugAMt ,SUM(September) As SepAMt ,SUM(October) As OctAMt ,SUM(November) As NovAMt ,SUM(December) As DecAMt FROM #empSales PIVOT (      SUM(saleAmt) For saleMonth IN (January, February, March, April, May, June, July, August, September, October, November, December) ) As pvt GROUP BY employeeName Order By employeeName 

Improving QuickSort Algorithm with pivot as first element

I was trying to improve the algorithm since its the most effective and known algorithm among many others, I came across ” Quicksort algorithm with an early exit for sorted subfiles 1987 by University of Tulsa, Roger L. Waiwright” check it out its interesting, do you guys know any other ways/researches ? I think reducing the memory would work by reducing the amount of arrays and working on one array idk how I am going to do that.

doing bubble sort or selection sort for large arrays isn’t helpful and checking if a big array is sorted using them would increase the complexity. P.S: I am just learning and studying not doing a research etc.

Quicksort algorithm with an early exit for sorted subfiles

How to pivot following MS SQL query in posgresql?

CREATE VIEW [dbo].[vVolunteersCustomFieldAnswers] AS WITH CustomIndexesTable (OrganizationId, CustomFieldGuid, CustomIndex) AS (SELECT org.OrganizationId, cf.CustomFieldGuid, ROW_NUMBER() OVER (PARTITION BY org.OrganizationId ORDER BY cf.CustomFieldId) as CustomIndex FROM OrganizationCustomField oc INNER JOIN Organization org on org.OrganizationId = oc.OrganizationId INNER JOIN CustomField cf on cf.CustomFieldId = oc.CustomFieldId WHERE ((EntityTypeId=1) Or (EntityTypeId=2)) and cf.IsDeleted=0 )

select NEWID() as RowGuid, OrganizationId, ConstituentGuid, 1 as Custom1, [2] as Custom2, [3] as Custom3, [4] as Custom4, [5] as Custom5, [6] as Custom6, [7] as Custom7, [8] as Custom8, [9] as Custom9 ,[10] as Custom10, [11] as Custom11, [12] as Custom12, [13] as Custom13, [14] as Custom14, [15] as Custom15, [16] as Custom16, [17] as Custom17, [18] as Custom18, [19] as Custom19 ,[20] as Custom20, [21] as Custom21, [22] as Custom22, [23] as Custom23, [24] as Custom24, [25] as Custom25, [26] as Custom26, [27] as Custom27, [28] as Custom28, [29] as Custom29 ,[30] as Custom30, [31] as Custom31, [32] as Custom32, [33] as Custom33, [34] as Custom34, [35] as Custom35, [36] as Custom36, [37] as Custom37, [38] as Custom38, [39] as Custom39 ,[40] as Custom40, [41] as Custom41, [42] as Custom42, [43] as Custom43, [44] as Custom44, [45] as Custom45, [46] as Custom46, [47] as Custom47, [48] as Custom48, [49] as Custom49 ,[50] as Custom50, [51] as Custom51, [52] as Custom52, [53] as Custom53, [54] as Custom54, [55] as Custom55, [56] as Custom56, [57] as Custom57, [58] as Custom58, [59] as Custom59 ,[60] as Custom60, [61] as Custom61, [62] as Custom62, [63] as Custom63, [64] as Custom64, [65] as Custom65, [66] as Custom66, [67] as Custom67, [68] as Custom68, [69] as Custom69 ,[70] as Custom70, [71] as Custom71, [72] as Custom72, [73] as Custom73, [74] as Custom74, [75] as Custom75, [76] as Custom76, [77] as Custom77, [78] as Custom78, [79] as Custom79 ,[80] as Custom80, [81] as Custom81, [82] as Custom82, [83] as Custom83, [84] as Custom84, [85] as Custom85, [86] as Custom86, [87] as Custom87, [88] as Custom88, [89] as Custom89 ,[90] as Custom90, [91] as Custom91, [92] as Custom92, [93] as Custom93, [94] as Custom94, [95] as Custom95, [96] as Custom96, [97] as Custom97, [98] as Custom98, [99] as Custom99 ,[100] as Custom100 FROM

— Volunteer Custom Fields

(SELECT org.OrganizationId, con.ConstituentGuid, ci.CustomIndex, answers.Answer as Value
from Constituent con INNER JOIN Organization org on org.OrganizationId = con.OrganizationId INNER JOIN VolunteerProfile vp on vp.VolunteerProfileId = con.VolunteerProfileId INNER JOIN CustomFieldVolunteerAnswer cfva on cfva.VolunteerProfileId = con.VolunteerProfileId INNER JOIN (SELECT CASE WHEN cf.CustomFieldType = 1 THEN cfa.TextAnswer WHEN cf.CustomFieldType = 8 THEN CONVERT(nvarchar(max), cfa.TextAreaAnswer) WHEN cf.CustomFieldType = 2 THEN CASE WHEN cfa.CheckboxAnswer = 1 THEN ‘True’ ELSE ‘False’ END
WHEN cf.CustomFieldType = 5 THEN CONVERT(nvarchar(30), cfa.DateAnswer, 126) — UsingConvertTo_ISO8601 WHEN cf.CustomFieldType = 9 THEN — time But I don’t think this is valid for custom fields anyways CONVERT(nvarchar(30), cfa.DateAnswer, 126) WHEN cf.CustomFieldType = 6 THEN Str(cfa.NumberAnswer, 25, 5) WHEN cf.CustomFieldType = 7 THEN convert(varchar(30), cfa.CurrencyAnswer, 0) — no commas WHEN cf.CustomFieldType = 3 THEN — single item list (SELECT Top(1) cfv.CustomFieldValueName from CustomFieldAnswerValue cfav INNER JOIN CustomFieldValue cfv on cfv.CustomFieldValueId = cfav.CustomFieldValueId and cfa.CustomFieldAnswerId = cfav.CustomFieldAnswerId) WHEN cf.CustomFieldType = 4 THEN — multilist (SELECT SUBSTRING((Select ‘,’ + cfv.CustomFieldValueName as [text()] from CustomFieldAnswerValue cfav INNER JOIN CustomFieldValue cfv on cfv.CustomFieldValueId = cfav.CustomFieldValueId and cfa.CustomFieldAnswerId = cfav.CustomFieldAnswerId For XML PATH (”)), 2, 1000)) — 1,000 cahracter limit ELSE Null END AS Answer, — never should be hit anyways cf.CustomFieldLabel, cf.CustomFieldGuid, cfa.CustomFieldAnswerId from CustomFieldAnswer cfa INNER JOIN CustomField cf on cf.CustomFieldId = cfa.CustomFieldId WHERE cf.IsHidden != 1) answers on answers.CustomFieldAnswerId = cfva.CustomFieldAnswerId INNER JOIN CustomIndexesTable ci on ci.CustomFieldGuid = answers.CustomFieldGuid

UNION ALL

SELECT org.OrganizationId, con.ConstituentGuid, ci.CustomIndex, answers.Answer as Value
from Constituent con INNER JOIN Organization org on org.OrganizationId = con.OrganizationId INNER JOIN CustomFieldConstituentAnswer cfca on cfca.ConstituentId = con.ConstituentId INNER JOIN VolunteerProfile vp on vp.VolunteerProfileId = con.VolunteerProfileId INNER JOIN CustomFieldVolunteerAnswer cfva on cfva.VolunteerProfileId = con.VolunteerProfileId INNER JOIN (SELECT CASE WHEN cf.CustomFieldType = 1 THEN cfa.TextAnswer WHEN cf.CustomFieldType = 8 THEN CONVERT(nvarchar(max), cfa.TextAreaAnswer) WHEN cf.CustomFieldType = 2 THEN CASE WHEN cfa.CheckboxAnswer = 1 THEN ‘True’ ELSE ‘False’ END
WHEN cf.CustomFieldType = 5 THEN CONVERT(nvarchar(30), cfa.DateAnswer, 126) — UsingConvertTo_ISO8601 WHEN cf.CustomFieldType = 9 THEN — time But I don’t think this is valid for custom fields anyways CONVERT(nvarchar(30), cfa.DateAnswer, 126) WHEN cf.CustomFieldType = 6 THEN Str(cfa.NumberAnswer, 25, 5) WHEN cf.CustomFieldType = 7 THEN convert(varchar(30), cfa.CurrencyAnswer, 0) — no commas WHEN cf.CustomFieldType = 3 THEN — single item list (SELECT Top(1) cfv.CustomFieldValueName from CustomFieldAnswerValue cfav INNER JOIN CustomFieldValue cfv on cfv.CustomFieldValueId = cfav.CustomFieldValueId and cfa.CustomFieldAnswerId = cfav.CustomFieldAnswerId) WHEN cf.CustomFieldType = 4 THEN — multilist (SELECT SUBSTRING((Select ‘,’ + cfv.CustomFieldValueName as [text()] from CustomFieldAnswerValue cfav INNER JOIN CustomFieldValue cfv on cfv.CustomFieldValueId = cfav.CustomFieldValueId and cfa.CustomFieldAnswerId = cfav.CustomFieldAnswerId For XML PATH (”)), 2, 1000)) — 1,000 cahracter limit ELSE Null END AS Answer, — never should be hit anyways cf.CustomFieldLabel, cf.CustomFieldGuid, cfa.CustomFieldAnswerId from CustomFieldAnswer cfa INNER JOIN CustomField cf on cf.CustomFieldId = cfa.CustomFieldId WHERE cf.IsHidden != 1) answers on answers.CustomFieldAnswerId = cfca.CustomFieldAnswerId INNER JOIN CustomIndexesTable ci on ci.CustomFieldGuid = answers.CustomFieldGuid ) AS Source PIVOT (Max(Value) FOR customIndex IN (1, [2], [3], [4], [5], [6], [7],[8],[9],[10], [11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28], [29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46], [47],[48],[49],[50],[51],[52],[53],[54],[55],[56],[57],[58],[59],[60],[61],[62],[63],[64], [65],[66],[67],[68],[69],[70],[71],[72],[73],[74],[75],[76],[77],[78],[79],[80],[81],[82], [83],[84],[85],[86],[87],[88],[89],[90],[91],[92],[93],[94],[95],[96],[97],[98],[99],[100]) ) AS PivotTable;

The output of the above query looks like this

enter image description here

Pivot in postgresql

I have multiple tables for multiple events, as in this example: Example source table

I would like to pivot these tables, from multiple events, into a table with this format: Desired result

I am at a complete loss on how to achieve this result; so far I have only written pretty basic sql queries. Any help would be much appreciated (please take into account that I am a beginner in SQL/ any kind of programming).

Pivot not updating programmatically

I have a canvas. It has an empty game object as a child. That object has a RectTransform component. The RectTransform pivot value is set to 0.4, 0.5 (x, y). In some code I wanted to update the pivot value using the following:

Debug.Log(((RectTransform)transform).pivot);  // logs: (0.4, 0.5) ((RectTransform)transform).pivot.Set(0.3f, 0.8f); Debug.Log(((RectTransform)transform).pivot);  // logs: (0.4, 0.5) 

I don’t know why the set command is not working. The tooltip for Set is: “Set x and y components of an existing Vector2”

¿Cómo usar pivot para un campo de una consulta en C# .net?

Tengo una consulta

Tengo una método que devuelve una lista de datos(idColaborador,nombreColaborador,unidad Organizativa, fecha_registro).

Lo que yo quiero es invertir el campo fecha_registro de columna a fila como se trabaja en pivot.

Al principio intente hacerlo el pivot desde base de datos y llamarlo en un procedimiento almacenado pero el problema es que al recibir los datos me traía demasiadas columnas ya que trae todos los días del mes y al momento de mapearlo en mi método tendría que crear la cantidad de atributos necesarios de acuerdo a las columnas que reciba de mi procedimiento es por ello que estoy intentando hacerlo por .net.

Aquí encontré una página de ejemplo pero yo soy nuevo en estos temas y les pido que me puedan ayudar https://www.codeproject.com/Articles/22008/C-Pivot-Table

public List<Sigeri> ExportarResumenHoras()         {  List<Sigeri> lista = new List<Sigeri>();         DatabaseHelper helper = null;         SqlDataReader dr = null;         try         {              helper = new DatabaseHelper(Conexion.Instancia.CadenaConexionDS());             helper.AddParameter("@MesAño", "082019");              dr = (SqlDataReader)helper.ExecuteReader("PA_Control_horas", System.Data.CommandType.StoredProcedure);             while (dr.Read())             {                 var obj = new Sigeri()                 {                     Colaborador = new Colaborador()                     {                         IdColaborador = dr["Id_Colaborador"].ToString(),                         NombreColaborador = dr["Nombre_Colaborador"].ToString()                     },                     Proyecto = new Proyecto()                     {                         IdUnidadOrganizativa=dr["Id_Unidad_Organizativa"].ToString()                     },                     Fecha_registro = DateTime.Parse(dr["Fecha_Registro"].ToString())                  };                 lista.Add(obj);             }          }         catch (Exception ex)         {             throw ex;          }         finally         {             if (helper != null)                 helper.Dispose();         }         return lista;     } 

Ya que luego deseo exportarlo a excel y debe mostrar algo parecido a esta imagen.

introducir la descripción de la imagen aquí

Este sería mi controlador donde se exportaría en excel lo que reciba de mi método ExportarResumenHoras

public JsonResult ExportarResumenHoras()         {             List<Sigeri> data = GestionResumenHoras.Instancia.ListaResumenHoras();              ExcelPackage pkg = new ExcelPackage();             ExcelWorksheet ws = pkg.Workbook.Worksheets.Add("Detalle(Semana)");                ws.Cells["A6"].Value = "DNI";             ws.Cells["B6"].Value = "NOMBRE COLABORADOR";             ws.Cells["C6"].Value = "PROYECTO UO";              int rowstart = 7;             //int columna = 4;             if (data != null)             {                 foreach (Sigeri obj in data)                 {                     ws.Cells[string.Format("A{0}", rowstart)].Value = obj.Colaborador.IdColaborador;                     ws.Cells[string.Format("B{0}", rowstart)].Value = obj.Colaborador.NombreColaborador;                     ws.Cells[string.Format("C{0}", rowstart)].Value = obj.Proyecto.IdUnidadOrganizativa;                     ws.Cells[string.Format("D{0}", rowstart)].Value = obj.Fecha_registro;                  }             }             ws.Cells["A:AZ"].AutoFitColumns();                MemoryStream memoryStream = new MemoryStream();             pkg.SaveAs(memoryStream);             memoryStream.Position = 0;              var bytes = memoryStream.ToArray();             Session["archivoResumenHoras"] = bytes;             return Json(new { success = true }, JsonRequestBehavior.AllowGet);         }