SQL Query: New columns for data with some matching criteria

user_14750286 0
Viewed 67

I'm working with a query that pulls data from a table and arranges it in a manner similar to below:

Query1

BldID  UnitID   Res1

1      201      John Smith   
1      201      Jane Doe
1      202      Daniel Jones
1      202      Mark Garcia
2      201      Maria Lee
2      201      Paul Williams
2      201      Mike Jones

I'd like to modify the query output in SQL/Design so that each resident that shares a building / unit shows as a new column on the same row as shown below:

BldID  UnitID   Res1          Res2           Res3 
1      201      John Smith    Jane Doe
1      202      Daniel Jones  Mark Garcia
2      201      Maria Lee     Paul Williams  Mike Jones

I apologize if this is crude/not enough information but any help would be greatly appreciated.

multiple-columns ms-access sql
3 Answers

You can try using conditional aggregation

with cte as
(
select *, row_number() over(partition by BldID,UnitID order by Res1) as rn
from tablename
)

select BldID,UnitID,
       max(case when rn=1 then Res1 end) as Res1,
       max(case when rn=2 then Res1 end) as Res2,
       max(case when rn=3 then Res1 end) as Res3
from cte
group by BldID,UnitID
user_69148640

So, drawing from a few different sources, this might work, try pasting this intoa query editor, and see if it'll run.

TRANSFORM MAX(Res1)
SELECT BldID, UnitID
    , (
         SELECT COUNT(T1.Marks)
         FROM tableName AS T1
         WHERE 
             T1.BldgID = T2.BldgID AND
             T1.UnitID = T2.UnitID AND
             T1.Res1 >= T2.Res1
      ) AS Rank, Res1
FROM tableName t2
GROUP BY BldID, UnitID
PIVOT Rank;
user_36099060

2 years late, but maybe I can add something, in Access we are surgeons operating with kitchen knifes, things must be done in the Access Way...

I tested it having this table UnitStudentBlock

BldID UnitID Res1
1 201 John Smith
1 201 Jane Doe
1 202 Daniel Jones
1 202 Mark Garcia
2 201 Maria Lee
2 201 Paul Williams
2 201 Mike Jones
2 201 Julian Gomes

As Access doesn't have row_number, first I created a table with an auto increment field so that we can have something like a row number:

CREATE TABLE TableWithId
(
Id COUNTER,
BldID  INT,
UnitID INT,
Res1 VARCHAR(100),
ResNumber VARCHAR(100)
)

Then I inserted all the data from the initial table into this newly created table:

INSERT INTO TableWithId (BldID, UnitID, Res1)
SELECT * 
FROM UnitStudentBlock
ORDER BY BldID, 
         UnitID

Then I updated everything using DCOUNT to have a row_number partitioned:

UPDATE TableWithId
SET ResNumber = 'Res' + Cstr(DCOUNT("*", "TableWithId", "ID >=" & [ID] 
                                                     & " AND UnitId = " & [UnitId] 
                                                     & " AND BldId = " & [BldId]))

And finally we can run the query that returns the data:

TRANSFORM MAX(Res1)
SELECT BldID, UnitID
FROM TableWithId
GROUP BY BldID, UnitID 
PIVOT ResNumber
user_192235860
Related