Transposing Data SQL

Question

The data looks similar to this:

+----+------+-----------+-------+---------+---------+--------+
| ID | Unit | Floorplan | Sq Ft |  Name   | Amenity | Charge |
+----+------+-----------+-------+---------+---------+--------+
|  1 |  110 | A1        |   750 |  Alan   | GARAGE  |     50 |
|  2 |      |           |       |         | RENT    |    850 |
|  3 |      |           |       |         | PEST    |      2 |
|  4 |      |           |       |         | TRASH   |     15 |
|  5 |      |           |       |         | TOTAL   |     20 |
|  6 |  111 | A2        |   760 |  Bill   | STORAGE |     35 |
|  7 |      |           |       |         | GARAGE  |     50 |
|  8 |      |           |       |         | RENT    |    850 |
|  9 |      |           |       |         | PEST    |      2 |
| 10 |      |           |       |         | TOTAL   |     15 |
| 11 |  112 | A3        |   770 | Charlie | PETRENT |     20 |
| 12 |      |           |       |         | STORAGE |     35 |
| 13 |      |           |       |         | GARAGE  |     50 |
| 14 |      |           |       |         | RENT    |    850 |
| 15 |      |           |       |         | TOTAL   |      2 |
+----+------+-----------+-------+---------+---------+--------+

I am new to SQL and trying my best using Microsoft Access, but I need help.

The data needs to look like this:

My first step is to separate the units from the rest with

SELECT * FROM table WHERE Unit <> NULL;

and after that I've usually just hard-input the rest.

My idea was as follows:

INSERT INTO table

VALUES (NULL,NULL,...,'Pest',$2)

FROM table

WHERE NOT EXIST 'Pest' BETWEEN x AND y

/* where x = Total 1 and y = Total 2*/

Am I on the right track? I probably need a loop or a join, but I'm not at that level yet.

Answer 1

You can use a crosstab query, though a bit convoluted it is:

TRANSFORM 
    Sum(TableUnit.Charge) AS SumOfCharge
SELECT 
    S.Unit, 
    S.Floorplan, 
    S.SqFt, 
    S.Name, 
    S.Amenity
FROM 
    TableUnit, 
        (SELECT 
            Q.Id, 
            Val(DMax("Id","TableUnit","Id<=" & Q.[Id] & " And Unit Is Not Null")) AS ParentId
        FROM TableUnit As Q) AS T, 
        (SELECT 
            TableUnit.Id, 
            TableUnit.Unit, 
            TableUnit.Floorplan, 
            TableUnit.SqFt, 
            TableUnit.Name, 
            TableUnit.Amenity
        FROM 
            TableUnit
        WHERE 
            TableUnit.Unit Is Not Null) AS S
WHERE 
    TableUnit.Id=[T].[Id] 
    AND 
    T.ParentId)=[S].[Id]
GROUP BY 
    T.ParentId, 
    S.Unit, 
    S.Floorplan, 
    S.SqFt, 
    S.Name, 
    S.Amenity
PIVOT 
    TableUnit.Amenity In
        ("Garage","Pest","Trash","PetRent","Storage","Rent");

Your test data differs a little from your expected output, so:

Answer 2

My MSAccess is rather rusty, but something like this should work:

SELECT t0.Unit, t0.Floorplan, t0.[Sq Ft], t0.Name, t0.Amenity
   , SUM(IIF(tM.Amenity = 'GARAGE', Charge, 0)) AS [Garage]
   , SUM(IIF(tM.Amenity = 'PEST', Charge, 0)) AS [Pest]
FROM (
   SELECT t1.id AS id0, MIN(t2.id) AS idN
   FROM t AS t1
   INNER JOIN t AS t2 ON t1.id < t2.id
   WHERE t1.Unit <> '' AND t2.Unit <> ''
) AS groups
INNER JOIN t AS t0 ON t0.id = groups.id0
LEFT JOIN t AS tM ON tM.id > groups.id0 AND tm.id < groups.idN
GROUP BY t0.Unit, t0.Floorplan, t0.[Sq Ft], t0.Name, t0.Amenity
;

Though, if I remember correctly, and it hasn't changed in newer versions; you can't have true subqueries and will need to make groups a separate query you can join to as if it were a table/view.