CODEWITHSUNDEEP
androidsqlsqlite
Shoham Ben-Har
Shoham Ben-Har

Reputation: 327

select query - each two row into single result row

Lets assume I have the following 'Products' table:

ProductID | ProductName 
----------+---------
0255463   | ProductA       
0254483   | ProductB
0255341   | ProductC
0905454   | ProductD

Is there a way (in Android's SQLite) to select each two consecutive rows into single result row? Here is the desired query result:

FirstProductID | FirstProductName | SecondProductID | SecondProductName 
---------------+------------------+-----------------+---------
0255463        | ProductA         |        0254483  | ProductB
0255341        | ProductC         |       0905454   | ProductD

I would like a generic solution that can be used to any table, regardless the table content.

Upvotes: 1

Views: 250

Answers (5)

cs_alumnus
cs_alumnus

Reputation: 1659

Create a temporary table with an auto increment column

CREATE TEMP TABLE temp(
  id int not null primary auto increment,
  pid int,
  pname text,    
);

Insert select this data into the temporary table

INSERT INTO temp (pid, pname) SELECT * FROM Products;

Join the temporary table on id = id + 1 where the first instance has id % 2 = 0

SELECT t1.pid AS t1_pid, t1.pname AS t1_pname,
  t2.pid AS t2_pid, t2.pname AS t2_pname
FROM temp as t1 LEFT JOIN temp AS t2
  ON t1.id + 1 = t2.id
WHERE t1.id % 2 = 0;

Upvotes: 3

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11151

Single query (not faster):

SELECT
    First.ProductId AS FirstProductId,
    First.ProductName AS FirstProductName,
    Second.ProductId AS SecondProductId,
    Second.ProductName AS SecondProductName 
FROM
    (SELECT *, Cnt/2 AS Line FROM (
        SELECT *, (
            SELECT COUNT() FROM Products AS _ WHERE ROWID<Products.ROWID
        ) AS Cnt FROM Products WHERE Cnt%2=0
    )) AS First
LEFT JOIN
    (SELECT *, Cnt/2 AS Line FROM (
        SELECT *, (
            SELECT COUNT() FROM Products AS _ WHERE ROWID<Products.ROWID
        ) AS Cnt FROM Products WHERE Cnt%2=1
    )) AS Second
ON First.Line = Second.Line
ORDER BY First.Line;

If you need a faster solution, @kzarns proposed a good one.

Upvotes: 1

CL.
CL.

Reputation: 180020

Doing this in SQL would be rather complex and slow.

The cleanest way to reorder column values like this would be to implement your own Cursor class that wraps the original database cursor, but doubles the number of columns, and redirects all column accesses to the appropriate record. Something like this:

class DoubleColumnCursor implements Cursor {
    Cursor baseCursor;
    int baseColumns;
    int currentPosition;

    public getColumnCount() {
        return baseColumns * 2;
    }
    public String getColumnName(int columnIndex) {
        if (columnIndex < baseColumns)
            return baseCursor.getColumnName(columnIndex) + "1";
        else
            return baseCursor.getColumnName(columnIndex - baseColumns) + "2";
    }
    public boolean moveToPosition(int position) {
        boolean result = baseCursor.moveToPosition(position * 2);
        if (result)
            currentPosition = position;
        return result;
    }
    public String getString(int columnIndex) {
        if (columnIndex < baseColumns) {
            baseCursor.moveToPosition(currentPosition * 2);
            return baseCursor.getString(columnIndex);
        } else {
            baseCursor.moveToPosition(currentPosition * 2 + 1);
            return baseCursor.getString(columnIndex - baseColumns);
        }
    }
    ...
};

Upvotes: 0

Low Chee Mun
Low Chee Mun

Reputation: 610

this is the sample idea , basically what am i doing is obtain different result from 2 select statement and join it , have a try on it, cheer =)

--sample table

DECLARE @SAMPLE TABLE
(
ProductID INT,
ProductName NVARCHAR(255)
)

--sample data

INSERT INTO @SAMPLE
VALUES
('1','ProductA'),
('2','ProductB'),
('3','ProductC'),
('4','ProductD')

SELECT FirstProductID,FirstProductName,SecondProductID,SecondProductName FROM
(

--Query to get firstProduct items

SELECT ROW_NUMBER() OVER (ORDER BY FirstProductID) firstrn,FirstProductID,FirstProductName
FROM(
SELECT ProductID 'FirstProductID',ProductName 'FirstProductName' FROM 
(
SELECT ROW_NUMBER() OVER (Order by productid) firstrn1,ProductID,ProductName
FROM @SAMPLE
) FIRSTPRODUCTTABLE
WHERE firstrn1%2 = 1
) FIRSTPRODUCTTABLE1 
)t1

-A join is performed

LEFT OUTER JOIN (

--Query to get second product items

SELECT * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY SecondProductID) rownumber,SecondProductID,SecondProductName
FROM(
SELECT ProductID 'SecondProductID',ProductName 'SecondProductName' FROM 
(
SELECT ROW_NUMBER() OVER (Order by productid) rn,ProductID,ProductName
FROM @SAMPLE
) SECONDPRODUCTTABLE
WHERE rn%2 = 0
)SECONDPRODUCTTABLE1
) t2
) t3 ON t1.firstrn=t3.rownumber

Upvotes: 0

Cyril Joudieh
Cyril Joudieh

Reputation: 124

It is easy if you have consecutive IDs for the rows:

SELECT t1.productID AS productID1, t1.ProductName AS productName1, t2.productID AS productID2, t2.ProductName AS ProductName2
FROM product t1
JOIN product t2 WHERE t1.id+1 = t2.id
WHERE MOD(t1.id,2) = 1
ORDER BY t1.id

Upvotes: 0

Related Questions