Transposing SQLite Rows and Columns with Average Per Hour

Transposing SQLite rows and columns with average per hour

SQLite does not have a PIVOT function but you can use an aggregate function with a CASE expression to turn the rows into columns:

select param,
  avg(case when time = '00' then param_val end) AvgHour0Val,
  avg(case when time = '00' then breach_count end) AvgHour0Count,
  avg(case when time = '01' then param_val end) AvgHour1Val,
  avg(case when time = '01' then breach_count end) AvgHour1Count,
  avg(case when time = '02' then param_val end) AvgHour2Val,
  avg(case when time = '02' then breach_count end) AvgHour2Count
from
(
  select param,
    strftime('%H', date_time) time,
    param_val,
    breach_count
  from param_vals_breaches
  where queue = 'a'
) src
group by param;

See SQL Fiddle with Demo

SQLite: Transposing results of a GROUP BY and filling in IDs with names

Consider using subqueries to achieve your desired transposed output:

SELECT DISTINCT m.usid,

   IFNULL((SELECT t1.loccount FROM tablename t1 
         WHERE t1.usid = m.usid AND t1.loc=1),0) AS Loc1,
   IFNULL((SELECT t2.loccount FROM tablename t2 
         WHERE t2.usid = m.usid AND t2.loc=13),0) AS Loc13,
   IFNULL((SELECT t3.loccount FROM tablename t3 
         WHERE t3.usid = m.usid AND t3.loc=27),0) AS Loc27

FROM tablename As m

Alternatively, you can use nested IF statements (or in the case of SQLite that uses CASE/WHEN) as derived table:

SELECT temp.usid, Max(temp.loc1) As Loc1, 
       Max(temp.loc13) As Loc13, Max(temp.loc27) As Loc27
FROM    
   (SELECT tablename.usid, 
      CASE WHEN loc=1 THEN loccount ELSE 0 As Loc1 END,  
      CASE WHEN loc=13 THEN loccount ELSE 0 As Loc13 END, 
      CASE WHEN loc=27 THEN loccount ELSE 0 As Loc27 END
    FROM tablename) AS temp    
GROUP BY temp.usid

Merging rows from SQL SELECT query

Assuming your table is like so (I have added column names):

A           | B |    C     |  D
------------+---+----------+------
Same values | 1 | 20130101 | City1
Same values | 2 | 20130102 | City2
Same values | 3 | 20130103 | City3

Your query would be:

SELECT  A,
        MAX(CASE WHEN B = 1 THEN C END) C1,
        MAX(CASE WHEN B = 1 THEN D END) D1,
        MAX(CASE WHEN B = 2 THEN C END) C1,
        MAX(CASE WHEN B = 2 THEN D END) D1,
        MAX(CASE WHEN B = 3 THEN C END) C1,
        MAX(CASE WHEN B = 3 THEN D END) D1
FROM    T
GROUP BY A;

Example On SQL Fiddle

Which gives:

A            |  C1       |  D1    | C1       |  D1    | C1       |  D1
-------------+-----------+--------+----------+--------+----------+--------
Same values  |  20130101 |  City1 | 20130102 |  City2 | 20130103 |  City3

As far as I know SQLite does not support dynamic SQL, so if you don't know how many columns you will need until run time you will need to build your query in your application layer, then send it to the database.

So you would need to get the maximum value for B:

SELECT MAX(B) B FROM T;

Then loop through 1 to the Max Value of B (I don't know what language you are using, but in C# as it is what I am using today):

var sb = new StringBuilder();
var template = ", MAX(CASE WHEN B = {0} THEN C END) C{0}, MAX(CASE WHEN B = {0} THEN D END) D{0}";
sb.Append("SELECT A");

for (int i = 1; i < MaxB; i++) {
    sb.Append(string.Format(template, i);
}
sb.Append(" FROM T GROUP BY A");

How to pivot in SQLite or i.e. select in wide format a table stored in long format?

First you need to change the current table to a temp table:

alter table student_info rename to student_name

Then, you'll want to recreate student_info:

create table student_info add column (
    stuid VARCHAR(5) PRIMARY KEY,
    name VARCHAR(255),
    subjectid_3 INTEGER,
    subjectid_4 INTEGER,
    subjectid_5 INTEGER
)

Then, populate student_info:

insert into student_info
select
    u.stuid,
    u.name,
    s3.marks as subjectid_3,
    s4.marks as subjectid_4,
    s5.marks as subjectid_5
from
    student_temp u
    left outer join markdetails s3 on
        u.stuid = s3.stuid
        and s3.subjectid = 3
    left outer join markdetails s4 on
        u.stuid = s4.stuid
        and s4.subjectid = 4
    left outer join markdetails s5 on
        u.stuid = s5.stuid
        and s5.subjectid = 5

Now, just drop your temp table:

drop table student_temp

And that's how you can quickly update your table.

SQLite lacks a pivot function, so the best you can do is hard-code some left joins. A left join will bring match any rows in its join conditions and return null for any rows from the first, or left, table that don't meet the join conditions for the second table.

Performance issues with transpose and insert large, variable column data files into SQL Server

The dynamic string is going to be SLOW. Each SQLCommand is a separate call to the database. You are much better off streaming the output as a bulk insertion operation.

I understand that all your files are different formats, so you are having to parse and unpivot in code to get it into your EAV database form.

However, because the output is in a consistent schema you would be better off either using separate connection managers and the built-in unpivot operator, or in a script task adding multiple rows to the data flow in the common output (just like you are currently doing in building your SQL INSERT...INSERT...INSERT for each input row) and then letting it all stream into a destination.

i.e. Read your data and in the script source, assign the FileID, RowId, AttributeName and Value to multiple rows (so this is doing the unpivot in code, but instead of generating a varying number of inserts, you are just inserting a varying number of rows into the dataflow based on the input row).

Then pass that through a lookup to get from AttributeName to AttributeID (erroring the rows with invalid attributes).

Stream straight into an OLEDB destination, and it should be a lot quicker.

Group key-value columns into a single row

The other available option is to use conditional aggregation:

SELECT external_id,
       MAX(CASE WHEN key = 'foo' THEN value END) AS foo,
       MAX(CASE WHEN key = 'bar' THEN value END) AS bar,
       MAX(CASE WHEN key = 'man' THEN value END) AS man,
       ... etc
FROM mytable 
GROUP BY external_id 

Mysql, reshape data from long / tall to wide

Cross-tabs or pivot tables is the answer. From there you can SELECT FROM ... INSERT INTO ... or create a VIEW from the single SELECT.

Something like:

SELECT country, 
       MAX( IF( key='President', value, NULL ) ) AS President,
       MAX( IF( key='Currency', value, NULL ) ) AS Currency,
       ...

FROM table 
GROUP BY country;

Excel: How to transpose select columns and group by repeated values? (1D to 2D table)

Depending on your version of Excel, you can use either Power Pivot (2010/2013) or Get & Transform (2016) to pivot the data appropriately. Your data, if not already in a table, will be converted into one.

For the latter, Selecting From Table opens the Query Editor. After selecting the Field Name and Value columns, select Transform ► Pivot Column

This will bring up a Pivot Column dialog. You want to be sure the selections are as below. Also you must select advanced to get to the do not aggregate option.

Select OK and you have your results as in your question. When you save the query, it will write the results to a new worksheet. You'll need to format the date column properly.

I'm not sure how this will work with 700,000 rows. You might need 64-bit Excel.

However, looking at some of the comments to other responses, this solution should work with varying numbers of Field Name / Value pairs.

---

Related Topics