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

Откуда:
Сообщений: 1197
Есть такой результат select

Position  Age  Name
1                    10   AA
2                    20   BB
3                    30   CC



можно ли его развернуть в такой вид

Age1 Name1 Age2 Name2 Age3 Name3
10      AA      20     BB       30     CC


задача: вывести в отчет данные как в нижней таблице.
если в скл нельзя, может посоветуете более эффективное средство?

з.ы. кол-во столбцов в репорте фиксированное, но результат селекта может содержать как меньше так и больше 3 записей
25 июл 12, 13:42    [12913742]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT n-раз  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
with cte as
(
select 1 position, 10 as age,  'AA' as name, 100 as pkEntity
union all
select 2 position, 20 as age,  'BB' as name, 100 as pkEntity
union all
select 3 position, 30 as age,  'CC' as name, 100 as pkEntity
union all
select 1 position, 11 as age,  'AA1' as name, 101 as pkEntity
union all
select 2 position, 21 as age,  'BB1' as name, 101 as pkEntity
union all
select 3 position, 31 as age,  'CC1' as name, 101 as pkEntity
)

select c.pkEntity,
       max(case position when 1 then  age end) as age1,
       max(case position when 1 then  name end) as name1,
       max(case position when 2 then  age end) as age2,
       max(case position when 2 then  name end) as name2,
       max(case position when 3 then  age end) as age3,
       max(case position when 3 then  name end) as name3   
  from cte c
 group
    by c.pkEntity


Лучше заниматься этим не в SQL.

Если необходимо - можно формировать динамически кусок с агрератными функции.


Есть ещё вариант делать несколько джойнов.
25 июл 12, 14:05    [12913895]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT n-раз  [new]
NIIIK
Member

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

with cte as
(
select 1 position, 10 as age,  'AA' as name, 100 as pkEntity
union all
select 2 position, 20 as age,  'BB' as name, 100 as pkEntity
union all
select 3 position, 30 as age,  'CC' as name, 100 as pkEntity
union all
select 1 position, 11 as age,  'AA1' as name, 101 as pkEntity
union all
select 2 position, 21 as age,  'BB1' as name, 101 as pkEntity
union all
select 3 position, 31 as age,  'CC1' as name, 101 as pkEntity
)
,
cteEntity as
(
select distinct
       c.pkEntity       
  from cte c
 group
    by c.pkEntity
)
select e.pkEntity,
       c1.age as age1,
       c1.name as name1,
       c2.age as age2,
       c2.name as name2,
       c3.age as age3,
       c3.name as name3
       
  from cteEntity e
  left
  join cte c1
    on e.pkEntity = c1.pkEntity
   and c1.position = 1
  left
  join cte c2
    on e.pkEntity = c2.pkEntity
   and c2.position = 2
  left
  join cte c3
    on e.pkEntity = c3.pkEntity
   and c3.position = 3
25 июл 12, 14:07    [12913905]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT n-раз  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
На фронтэнде эту логику лучше реализовывать как в приаттаченом примере формируется ХТМЛка

К сообщению приложен файл (016_buildFormExample.sql - 2Kb) cкачать
25 июл 12, 14:10    [12913925]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT n-раз  [new]
Yasha123
Member

Откуда:
Сообщений: 1837
ну а если результат селекта это 4 строки,
а в отчете фиксированное число колонок(6),
то как будет выглядеть результат для таких данных?

Position  Age  Name
1                    10   AA
2                    20   BB
3                    30   CC
4                    40   DD
25 июл 12, 14:23    [12914007]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT n-раз  [new]
relief
Member

Откуда:
Сообщений: 1197
Yasha123
ну а если результат селекта это 4 строки,
а в отчете фиксированное число колонок(6),
то как будет выглядеть результат для таких данных?

Position  Age  Name
1                    10   AA
2                    20   BB
3                    30   CC
4                    40   DD



последние 2 колонки будут пустыми
25 июл 12, 14:32    [12914071]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT n-раз  [new]
relief
Member

Откуда:
Сообщений: 1197
NIIIK,


Спасибо!
25 июл 12, 14:38    [12914112]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT n-раз  [new]
NIIIK
Member

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

Главное тут в ответе что "так лучше не делать", архитектура - штка важная.
25 июл 12, 14:56    [12914237]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT n-раз  [new]
Yasha123
Member

Откуда:
Сообщений: 1837
тогда, модифицируя решение NIIIK,

with cte as
(
select 1 position, 10 as age,  'AA' as name
union all
select 2 position, 20 as age,  'BB' as name
union all
select 3 position, 30 as age,  'CC' as name
union all
select 4 position, 40 as age,  'DD' as name
union all
select 5 position, 50 as age,  'EE' as name
),

cte1 as
( select *, 
         (position + 2) / 3 as gr,
         (position - 1) %3 + 1 as pos
  from cte        
)

select 
       max(case pos when 1 then  age end) as age1,
       max(case pos when 1 then  name end) as name1,
       max(case pos when 2 then  age end) as age2,
       max(case pos when 2 then  name end) as name2,
       sum(case pos when 3 then  age end) as age3,
       max(case pos when 3 then  name end) as name3   
  from cte1 
 group by gr



не надо джойнов и дистинкт, все в 1 скан
25 июл 12, 15:09    [12914349]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT n-раз  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Yasha123
тогда, модифицируя решение NIIIK,

with cte as
(
select 1 position, 10 as age,  'AA' as name
union all
select 2 position, 20 as age,  'BB' as name
union all
select 3 position, 30 as age,  'CC' as name
union all
select 4 position, 40 as age,  'DD' as name
union all
select 5 position, 50 as age,  'EE' as name
),

cte1 as
( select *, 
         (position + 2) / 3 as gr,
         (position - 1) %3 + 1 as pos
  from cte        
)

select 
       max(case pos when 1 then  age end) as age1,
       max(case pos when 1 then  name end) as name1,
       max(case pos when 2 then  age end) as age2,
       max(case pos when 2 then  name end) as name2,
       sum(case pos when 3 then  age end) as age3,
       max(case pos when 3 then  name end) as name3   
  from cte1 
 group by gr



не надо джойнов и дистинкт, все в 1 скан


Не преувеличивайте достоинства этого метода.
Если есть поиск с условиями по выходным колонкам, то никакие индексы не сработают.
Джойны не так страшны, если они правильные.
Подзапрос с distinct у меня для примера. В базе должна быть сущность содеражащая экземплры которые я получал дистинктом по данным.
25 июл 12, 17:09    [12915268]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT n-раз  [new]
Yasha123
Member

Откуда:
Сообщений: 1837
какой поиск по каким колонкам?
вроде ж автор попросил весь его "результат селекта развернуть" (в отчет залить)...
вариант с 3мя соединениями это 3 скана,
а разбрасывание по кейсам 1 скан,
что преувеличено?
25 июл 12, 17:26    [12915395]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT n-раз  [new]
NIIIK
Member

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

преувеличено достоинства метода.

Если у меня есть список "людей" и их текущих "паспортных данных", в виде

100, Фамилия, Иванов
100, Имя, Пётр
100, Отчество Иванович

И необходимой выполнить поиск человека по значениею его паспортных данных,
то метод с джойнами я применяю.
25 июл 12, 18:17    [12915774]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT n-раз  [new]
Yasha123
Member

Откуда:
Сообщений: 1837
NIIIK,

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

например, позиция -- это номер смены.
всего 3 смены. и надо ВСЕ полученное добро развернуть,
чтоб было: товарищ1смена, товарищ2смена, товарищ3смена.
никакого поиска не надо, а 1 скан обеспечен по-любому
25 июл 12, 18:38    [12915913]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить