Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Разворто строк в столбцы для объединенной таблицы  [new]
Катерина
Member

Откуда: Екатеринбург
Сообщений: 45
Добрый день!

Запросом соединяю две таблицы по идентификатору. При этом, вторая таблица er содержит несколько записей, соответствущих каждой строке первой таблице (от одной и до двадцати), как следствие, после связывания таблиц, получаю дублирование строк в первой таблице. Требуется, чтобы все строки второй таблицы (которые соответствуют условию связывания) выводились столбцами, добавляясь к строке первой таблицы.

Подскажите, пожалуйста, как это можно сделать


Связывающий запрос вот такой:
select e.id, e.exam. er.id, er.mark
from uni.entrant_t e
left join uni.entrantrequest_t er on e.id=er.entrant_id
where e. registrationdate_p between '20120601' and '20120701'
16 июл 12, 03:14    [12870615]     Ответить | Цитировать Сообщить модератору
 Re: Разворто строк в столбцы для объединенной таблицы  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 2996
версия сервера какая?
если 2005 и выше, то сначала вторую таблицу (uni.entrantrequest_t ) разверните с PIVOT, а потом уже джойните с первой
16 июл 12, 08:10    [12870728]     Ответить | Цитировать Сообщить модератору
 Re: Разворто строк в столбцы для объединенной таблицы  [new]
Катерина
Member

Откуда: Екатеринбург
Сообщений: 45
HandKot, MSQ SQL 2005 SP3

Спасибо большое, за подсказку. А можете пример, запроса для данного случая привести?
16 июл 12, 14:19    [12872572]     Ответить | Цитировать Сообщить модератору
 Re: Разворто строк в столбцы для объединенной таблицы  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
with 
cteCommonDictionary as --for example only
(
 select 1 as pk, cast(null as int) as fkOwner, 'Entity type dictionary' as name
 union all
 select 5, 1, 'Person'
 union all 
 select 51, 1, 'some other entity type'

 union all 
 select 2, null, 'Attribute dictionary'
 union all 
 select 3, 2, 'Attributes for Person'
 union all 
 select 6, 3, 'last name'
 union all 
 select 6, 3, 'first name'
),

cteEntity as
(
 select 10 as pk, 5 as fkType --person
 union all
 select 11 as pk, 5 as fkType --person
 union all
 select 12 as pk, 5 as fkType --person
 union all
 select 13 as pk, 51 as fkType --some other entity type
 
),

cteEntityData as --For any type, test case
(
 select 10 as fkEntity, 6 /*last name*/ as fkAttribute, 'Test Last Name 1' value 
 union all
 select 10 as fkEntity, 7 /*first name*/ as fkAttribute, 'Test First Name 1' value 
 
 union all
 select 11 as fkEntity, 6 /*last name*/ as fkAttribute, 'Test Last Name 2' value 
 union all
 select 11 as fkEntity, 7 /*first name*/ as fkAttribute, 'Test First Name 2' value 
 
 union all
 select 12 as fkEntity, 6 /*last name*/ as fkAttribute, 'Test Last Name 3' value 
 union all
 select 12 as fkEntity, 7 /*first name*/ as fkAttribute, 'Test First Name 3' value 
 
)

/*
--good performance and execution plan.
--the best for searching
--this method should be used by default
select e.pk,
       ed_last_name.value as last_name,
       ed_last_name.value as first_name
  from cteEntity e 
  
  left
  join cteEntityData ed_last_name
    on e.pk = ed_last_name.fkEntity
   and ed_last_name.fkAttribute = 6 --Should be selected into variable
  
  
  left
  join cteEntityData ed_first_name
    on e.pk = ed_last_name.fkEntity
   and ed_last_name.fkAttribute = 7 --Should be selected into variable
   

 where e.fkType = 5 --Should be selected into variable
 */


/*
--This method has the similar logic and execution plan as "PIVOT" operator but more flexible
--this method and "PIVOT" is not good in some cases for performance (serching, paging, big data volume)
select e.pk,
       max(
           case ed.fkAttribute
            when 6 --Should be selected into variable
            then value
           end           
          ) as last_name,
       max(
           case ed.fkAttribute
            when 7 --Should be selected into variable
            then value
           end           
          ) as first_name
  from cteEntity e
 inner
  join cteEntityData ed
    on ed.fkEntity = e.pk
 where e.fkType = 5
 
 group 
    by e.pk
*/
,
pvt as 
(
select e.pk,
       ed.fkAttribute,
       ed.value
  from cteEntity e
 inner
  join cteEntityData ed
    on ed.fkEntity = e.pk
 where e.fkType = 5
)

select pt.pk,
       [6] as last_name,
       [7] as first_name
  from pvt p
 pivot (
        max(p.value) 
        for fkAttribute 
         in (
             [6],
             [7]
            )
       ) as pt
  


Осозновайте.
16 июл 12, 15:19    [12872964]     Ответить | Цитировать Сообщить модератору
 Re: Разворто строк в столбцы для объединенной таблицы  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Кстати,
если атрибут обязательный или используется для поиска (значит его значение есть и он обязательный), то необходимо писать
INNER JOIN и делать проверку на уровне джойна.
16 июл 12, 15:30    [12873039]     Ответить | Цитировать Сообщить модератору
 Re: Разворто строк в столбцы для объединенной таблицы  [new]
Катерина
Member

Откуда: Екатеринбург
Сообщений: 45
Спасибо за идеи!
Буду пробовать.
16 июл 12, 15:57    [12873190]     Ответить | Цитировать Сообщить модератору
 Re: Разворто строк в столбцы для объединенной таблицы  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Катерина,

Помните, что часто равзорачивать строки в столбцы бывает просто НЕ надо на уровне базы.
Тем более любой из методов без динамического запроса не даст возможности получить динамическое количество столбцов.

Я обычно практикую (в задачах расписания или там где количество столбцов интерфейсно может изменяться) именно на клиенте PIVOT,
например который я использовал не умному заказчику.

К сообщению приложен файл (016_buildFormExample.sql - 2Kb) cкачать
16 июл 12, 17:05    [12873618]     Ответить | Цитировать Сообщить модератору
 Re: Разворто строк в столбцы для объединенной таблицы  [new]
Катерина
Member

Откуда: Екатеринбург
Сообщений: 45
NIIIK,

Здесь задача простая (с т.з. сути) - нужно просто сформировать выгрузку из БД по заданному внешнему формату.
Нюанс только такой - для каждой строки число столбцов получится разным (в зависимости от того, сколько записей (одна-две-три и т.п.) будет присоединено к исходной таблице).
16 июл 12, 19:33    [12874408]     Ответить | Цитировать Сообщить модератору
 Re: Разворто строк в столбцы для объединенной таблицы  [new]
Катерина
Member

Откуда: Екатеринбург
Сообщений: 45
Поняла, что PIVOT, похоже, не подойдет - ибо заранее неизвестно сколько строк во второй таблице.
Примерная ситуация описана здесь: https://www.sql.ru/forum/actualthread.aspx?bid=1&tid=912580&hl=pivot
16 июл 12, 22:54    [12874969]     Ответить | Цитировать Сообщить модератору
 Re: Разворто строк в столбцы для объединенной таблицы  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 2996
Катерина
Поняла, что PIVOT, похоже, не подойдет - ибо заранее неизвестно сколько строк во второй таблице.
Примерная ситуация описана здесь: https://www.sql.ru/forum/actualthread.aspx?bid=1&tid=912580&hl=pivot


а кто мешает сразу заложится на n-число столбцов (в Вашем случае двадцать) ?
правда в большинстве случаев они будут пустые
17 июл 12, 08:56    [12875753]     Ответить | Цитировать Сообщить модератору
 Re: Разворто строк в столбцы для объединенной таблицы  [new]
Катерина
Member

Откуда: Екатеринбург
Сообщений: 45
HandKot,

В принципе, можно и так, просто небольшие затруднения с запросом - пока разбираюсь.
17 июл 12, 13:16    [12877436]     Ответить | Цитировать Сообщить модератору
 Re: Разворто строк в столбцы для объединенной таблицы  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Катерина,

Вероятнее всего у вас ошибка в архитекторе или подходе. Смотрите шире на задачу.
1) как я уже сказал редко когда надо делать транспонирование (и пример привёл) на базе
2) "разное количество столбцов" (хоть вы и написали "ибо заранее неизвестно сколько строк во второй таблице.")
- не бывает такого что для одной строчки одно количество столбцов а для другой другое в одном запросе физически
- динамическое количество столбцов, как я писал раньше, только динамическим запросом, но если "кому-то понадобилось динамическое количиество столбцов" - он принципиально не правильно строит архитектуру.
- "заранее неизвестное количество строк" - это так и должно быть в любом запросе
"select ... from table"
вся идея в БД в том что количество экземляров сущностей должно быть динамическим (а не столбцов).

3) Количество столбцов/атрибутов либо определяется заранее при правильной архитектуре, например, делается запрос в какую-то таблицу где хранятся эти столбцы (в моём примере это все атрибуты человека с cteCommonDictionary и получаются названия столбцов до того как выполняется основной запрос.
Так же вохможно сначала выбрать данные (либо во временную таблицу, либо по distinct отдельным запросом) и определить "по данным" возможнный список будущих столбцов. Этот метод ещё более козлячий.

Если вы всё же решаете что вам нужны "динамические столбцы" - любой из способов будет работать только динаическим запросом (лишь бы формировали правильно). НЕ бывает в БД такого что бы один и тот запрос возразал разное количество столбцов (а вот строк бывает), кстати это поэтому транспонирование делать обычно не надо.

Даже ХМЛ его не делает когда есть

<xml>
 <row>
  <col name="col1">value</col>
  <col name="col2">value</col>
  <col name="col5">value</col>
  <col name="col10">value</col>
 </row>
</xml>


Хотя тэг <table> в HTML более наглядно показывает.
Попробуйте результат моего .sql сохранить в текстовый файл и открыть браузером.
17 июл 12, 15:05    [12878208]     Ответить | Цитировать Сообщить модератору
 Re: Разворто строк в столбцы для объединенной таблицы  [new]
Катерина
Member

Откуда: Екатеринбург
Сообщений: 45
NIIIK
Катерина,
2) "разное количество столбцов" (хоть вы и написали "ибо заранее неизвестно сколько строк во второй таблице.")
- не бывает такого что для одной строчки одно количество столбцов а для другой другое в одном запросе физически


Почему не бывает? Например, в первой таблице храним идентификаторы персоны, а во второй таблице телефоны сотрудников и их эдектронные адреса (телеыонов и адресов может не быть, может быть один или два а может быть 15 в зависимости от сотудника).
18 июл 12, 15:57    [12884171]     Ответить | Цитировать Сообщить модератору
 Re: Разворто строк в столбцы для объединенной таблицы  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Катерина
NIIIK
Катерина,
2) "разное количество столбцов" (хоть вы и написали "ибо заранее неизвестно сколько строк во второй таблице.")
- не бывает такого что для одной строчки одно количество столбцов а для другой другое в одном запросе физически


Почему не бывает? Например, в первой таблице храним идентификаторы персоны, а во второй таблице телефоны сотрудников и их эдектронные адреса (телеыонов и адресов может не быть, может быть один или два а может быть 15 в зависимости от сотудника).


Потому что небывает статического запроса с динамическими колонками.
Если у вас есть сущность "Человек" и "Телефон Человека" (Так же может быть модель "Человек", "телефон", "телефон человека")
То у вас это разные экземпляры сущности. И телефоны это "не колонки", тем более не колонки запроса.
Если у человека может быть 10ть телефонов, один из них основной "основной" другой из них "домашний" (при этом они могут быть одновременно), тогда правильно делать обратную ссылку у "человек" в виде отдельных атрибутов "Основной телефон (обратная ссылка)" и "Домашний телефон (обратная ссылка)", "Рабочий телефон (обратная ссылка)".
Их тогда отдельными джойнами можно выводить как разные телефоны в разных колонках. И делать это лучше именно обратными ссылакми, а не атрибутами на уровне сущности телефон а-ля "активный", "последний" и т. п.
20 июл 12, 17:13    [12894506]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить