• Sign In
  1. Messages

Column Formatting And Pivot Queries?

LabKey Support Forum (Inactive)

View Message

view list print
Column Formatting And Pivot Queries? Ben Bimber  2014-06-13 09:22
Status: Closed
 
We have a number of queries that use PIVOT. There are instances where applying formatting to the result of this (number formatting, textAlign, etc) would be very helpful. I cant see a way to accomplish this currently. Since the pivoted column is the result of an aggregate, it loses most of the metadata from its source ColumnInfo. Although it is ugly, for most of these situations we have a finite number of PIVOT values, so we could in theory add metadata to each expected child column by name (ie. "testId::Value"). That doesnt appear to be supported in XML though. Do we have any other options? Thanks for any help.
 
 
adam responded:  2014-06-13 13:30
Not an ideal solution, but I've used sub-SELECTs to alias the PIVOT columns with names that can be used in XML. Example from a customized issues list:

  SELECT Client,
  "P1::C" AS P1, "P2::C" AS P2, "P3::C" AS P3, "P4::C" AS P4,
  COALESCE("P1::C", 0) + COALESCE("P2::C", 0) + COALESCE("P3::C", 0) + COALESCE("P4::C", 0) AS Total FROM
  (
    SELECT Client, Priority, COUNT(*) AS C
    FROM CurrentOpen
    GROUP BY Client, Priority   
    PIVOT C BY Priority IN (1 AS P1,2 AS P2,3 AS P3,4 AS P4)
  ) x
  ORDER BY Client

Adam
 
jonesga responded:  2014-06-17 07:55
Your technique worked very well and helped meet the need for one of our projects.

Did have to Cast the Values as Varchar to make the XML Text Align property work, not sure what changed the values that HTML did not interpret it as a string

Thanks