#sql #ms-access #multiple-columns
#sql #ms-access #несколько столбцов
Вопрос:
Я работаю с запросом, который извлекает данные из таблицы и упорядочивает их способом, аналогичным приведенному ниже:
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
Я хотел бы изменить вывод запроса в SQL / Design, чтобы каждый житель, который совместно использует здание / единицу, отображался как новый столбец в той же строке, как показано ниже:
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
Я прошу прощения, если это грубая / недостаточно информации, но любая помощь будет принята с благодарностью.
Ответ №1:
Вы можете попробовать использовать 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
Комментарии:
1. Спасибо за ваш ответ @Fahmi. Полагаю, мне следовало уточнить больше. Резидентное поле — это, по сути, все уникальные записи. У каждого резидента есть другое имя. Повлияет ли это на результат, и если да, то что я буду использовать для выбора уникальных пользователей для достижения того же результата?
2. @llOmni, не могли бы вы, пожалуйста, пересмотреть свой образец, чтобы указать на эту уникальность и лучше сформулировать, что требуется? При этих двух условиях получение сплошных динамических столбцов из MSAcess таким способом может потребовать больше операторов, чем вы привыкли, и, вероятно, использует подзапрос для получения правильных результатов.
3. @JaazCole Прошу прощения, поскольку изначально он не был отформатирован таким образом. Я удалил общие идентификаторы A, B, C с некоторыми общими именами в формате вывода, который я ищу. Что касается уточняющего утверждения, все, что я могу добавить, это то, что каждое здание / единица является уникальным объектом с несколькими жителями в каждом. 1/1 1/2 1/3, 2/1/2/2 2/3, и я пытаюсь выделить этих резидентов, поскольку сейчас все они перечислены в разделе Res1. Еще раз спасибо за помощь.
4. @llOmni, я обновил asnwer — теперь вы можете попробовать
5. @JaazCole и Fahmi Я пробовал оба ответа, и пока безуспешно. Вероятно, это с моей стороны, поскольку я все еще новичок в SQL. Faami при вводе ваших данных я получаю неверный оператор SQL. Похоже, что-то не нравится в структуре CTE. JaazCole Я буду честен, ты выше моей головы в этом вопросе. Имя таблицы как T1 и имя таблицы как T2 относятся к каким таблицам? Даже если я извлекаю данные из своего исходного запроса, должен ли я заменить это как таблицу T1 и T2, или я должен извлекать из исходной таблицы и запроса из нее? Еще раз спасибо за помощь.
Ответ №2:
Итак, опираясь на несколько разных источников, это может сработать, попробуйте вставить это в редактор запросов и посмотреть, будет ли он выполняться.
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;
Ответ №3:
с опозданием на 2 года, но, может быть, я смогу что-то добавить, в Access мы хирурги, работающие с кухонными ножами, все должно быть сделано способом Access…
Я протестировал его с помощью этой таблицы UnitStudentBlock
| BldID | Идентификатор единицы | Res1 |
|---|---|---|
| 1 | 201 | Джон Смит |
| 1 | 201 | Джейн Доу |
| 1 | 202 | Дэниел Джонс |
| 1 | 202 | Марк Гарсия |
| 2 | 201 | Мария Ли |
| 2 | 201 | Пол Уильямс |
| 2 | 201 | Майк Джонс |
| 2 | 201 | Джулиан Гомес |
Поскольку Access не имеет row_number, сначала я создал таблицу с полем автоматического увеличения, чтобы у нас могло быть что-то вроде номера строки:
CREATE TABLE TableWithId
(
Id COUNTER,
BldID INT,
UnitID INT,
Res1 VARCHAR(100),
ResNumber VARCHAR(100)
)
Затем я вставил все данные из исходной таблицы в эту вновь созданную таблицу:
INSERT INTO TableWithId (BldID, UnitID, Res1)
SELECT *
FROM UnitStudentBlock
ORDER BY BldID,
UnitID
Затем я обновил все, используя DCOUNT, чтобы разделить row_number:
UPDATE TableWithId
SET ResNumber = 'Res' Cstr(DCOUNT("*", "TableWithId", "ID >=" amp; [ID]
amp; " AND UnitId = " amp; [UnitId]
amp; " AND BldId = " amp; [BldId]))
И, наконец, мы можем запустить запрос, который возвращает данные:
TRANSFORM MAX(Res1)
SELECT BldID, UnitID
FROM TableWithId
GROUP BY BldID, UnitID
PIVOT ResNumber