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

Откуда: Feorina "Fury" 161
Сообщений: 4374
Select Top (10) Name, level From MyColumn

нужно записать в таблицу
CREATE TABLE MyRows(
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[Level01] [nvarchar](100) NOT NULL,
	[Level02] [nvarchar](100) NOT NULL,
	[Level03] [nvarchar](100) NOT NULL,
	[Level04] [nvarchar](100) NOT NULL,
	[Level05] [nvarchar](100) NOT NULL,
	[Level06] [nvarchar](100) NOT NULL,
	[Level07] [nvarchar](100) NOT NULL,
	[Level08] [nvarchar](100) NOT NULL,
	[Level09] [nvarchar](100) NOT NULL,
	[Level10] [nvarchar](100) NOT NULL
) ON [PRIMARY]

но в случае, если первый запрос выдаст меньше 10 элементов, то остальные поля заполнить содержимом последнего элемента.

Пример:
Гриб
Топор
Корабль
Штопор
Подоконник

Результат
id Level01 Level02 Level03 Level04 Level05 Level06 Level07 Level08 Level09 Level10
17ГрибТопорКорабльШтопорПодоконникПодоконникПодоконникПодоконникПодоконникПодоконник


+ Нахрена?!
Это всего лишь маленькая подзадачка полной задачи, для решения которой мне пришлось сделать курсор, а внутри курсора организовать рекурсию.. которая мне и выдаёт тот запрос, с которого я открыл эту тему. Дело в том, что если представить дерево в этом виде, то только тогда OLAP распознает его как дерево. Поэтому приходится париться)
10 апр 14, 19:11    [15864462]     Ответить | Цитировать Сообщить модератору
 Re: Как транспонировать результат запроса? столбец в строку  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Charles Weyland, PIVOT.
10 апр 14, 19:44    [15864643]     Ответить | Цитировать Сообщить модератору
 Re: Как транспонировать результат запроса? столбец в строку  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4478
Думать лень. Вот решение тупое, "в лоб".
declare @table table ([name] nvarchar(100),[level] nvarchar(100));

insert into  @table
values('Гриб','Level01'),
('Топор','Level02'),
('Корабль','Level03'),
('Штопор','Level04'),
('Подоконник','Level05');

select 
	max([Level01]) [Level01],
	isnull(max([Level02]),max([Level01])) [Level02],
	coalesce(max([Level03]),max([Level02]),max([Level01])) [Level03],
	coalesce(max([Level04]),max([Level03]),max([Level02]),max([Level01])) [Level04],
	coalesce(max([Level05]),max([Level04]),max([Level03]),max([Level02]),max([Level01])) [Level05],
	coalesce(max([Level06]),max([Level05]),max([Level04]),max([Level03]),max([Level02]),max([Level01])) [Level06],
	coalesce(max([Level07]),max([Level06]),max([Level05]),max([Level04]),max([Level03]),max([Level02]),max([Level01])) [Level07],
	coalesce(max([Level08]),max([Level07]),max([Level06]),max([Level05]),max([Level04]),max([Level03]),max([Level02]),max([Level01])) [Level08],
	coalesce(max([Level09]),max([Level08]),max([Level07]),max([Level06]),max([Level05]),max([Level04]),max([Level03]),max([Level02]),max([Level01])) [Level09],
	coalesce(max([Level10]),max([Level09]),max([Level08]),max([Level07]),max([Level06]),max([Level05]),max([Level04]),max([Level03]),max([Level02]),max([Level01])) [Level10]
from(
select 
	case when [level] = 'Level01' then [name] else null end [Level01],
	case when [level] = 'Level02' then [name] else null end [Level02],
	case when [level] = 'Level03' then [name] else null end [Level03],
	case when [level] = 'Level04' then [name] else null end [Level04],
	case when [level] = 'Level05' then [name] else null end [Level05],
	case when [level] = 'Level06' then [name] else null end [Level06],
	case when [level] = 'Level07' then [name] else null end [Level07],
	case when [level] = 'Level08' then [name] else null end [Level08],
	case when [level] = 'Level09' then [name] else null end [Level09],
	case when [level] = 'Level10' then [name] else null end [Level10]
from @table) t
10 апр 14, 20:00    [15864707]     Ответить | Цитировать Сообщить модератору
 Re: Как транспонировать результат запроса? столбец в строку  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4478
Как справедливо было замечено можно "запилить PIVOT"
select [Level01],[Level02],[Level03],[Level04],[Level05],[Level06],[Level07],[Level08],[Level09],[Level10]
from 
(select [name],[level] from @table ) as t
pivot (max([name]) for [level] in ([Level01],[Level02],[Level03],[Level04],[Level05],[Level06],[Level07],[Level08],[Level09],[Level10])) as p;

Но с подстановкой последнего значения вместо пустого все равно придется поизгаляться.
10 апр 14, 20:14    [15864764]     Ответить | Цитировать Сообщить модератору
 Re: Как транспонировать результат запроса? столбец в строку  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4478
Вот вариант с подзапросом, использующим PIVOT

select 
	max([Level01]) [Level01],
	isnull(max([Level02]),max([Level01])) [Level02],
	coalesce(max([Level03]),max([Level02]),max([Level01])) [Level03],
	coalesce(max([Level04]),max([Level03]),max([Level02]),max([Level01])) [Level04],
	coalesce(max([Level05]),max([Level04]),max([Level03]),max([Level02]),max([Level01])) [Level05],
	coalesce(max([Level06]),max([Level05]),max([Level04]),max([Level03]),max([Level02]),max([Level01])) [Level06],
	coalesce(max([Level07]),max([Level06]),max([Level05]),max([Level04]),max([Level03]),max([Level02]),max([Level01])) [Level07],
	coalesce(max([Level08]),max([Level07]),max([Level06]),max([Level05]),max([Level04]),max([Level03]),max([Level02]),max([Level01])) [Level08],
	coalesce(max([Level09]),max([Level08]),max([Level07]),max([Level06]),max([Level05]),max([Level04]),max([Level03]),max([Level02]),max([Level01])) [Level09],
	coalesce(max([Level10]),max([Level09]),max([Level08]),max([Level07]),max([Level06]),max([Level05]),max([Level04]),max([Level03]),max([Level02]),max([Level01])) [Level10]
from(
select [Level01],[Level02],[Level03],[Level04],[Level05],[Level06],[Level07],[Level08],[Level09],[Level10]
from 
(select [name],[level] from @table ) as t
pivot (max([name]) for [level] in ([Level01],[Level02],[Level03],[Level04],[Level05],[Level06],[Level07],[Level08],[Level09],[Level10])) as p
) tt;
10 апр 14, 20:17    [15864780]     Ответить | Цитировать Сообщить модератору
 Re: Как транспонировать результат запроса? столбец в строку  [new]
Charles Weyland
Member

Откуда: Feorina "Fury" 161
Сообщений: 4374
хотя бы такое..

я, кстати, только что столкнулся с другой трудностью.
Как запихать в какую-нибудь переменную или во временную таблицу результат рекурсии, созданной с помощью With?
    With DirectReports (id,ParentID,  level)
    as
    (
        Select id, Parent_ID,  0 level from dict_tables
        where id = @id
        union all
        Select dict_tables.id, dict_tables.Parent_ID,  level + 1 from dict_tables
        Inner Join DirectReports on dict_tables.ID = DirectReports.ParentID
        where (level <= 50) --ограничитель числа итераций на случай зацикливания
        and dict_CFRs.ID != DirectReports.ID --если узел сам на себя ссылается, то
                                             --отображаться может не более 1 раза
    )
     select ID, level from DirectReports

а то я с этой таблицей-то сделать ничего не могу! только на экран вывести...
10 апр 14, 20:17    [15864781]     Ответить | Цитировать Сообщить модератору
 Re: Как транспонировать результат запроса? столбец в строку  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4478
А что? Запрос такой не работает:
select ID, level 
into #DirectReports
from DirectReports
10 апр 14, 20:20    [15864789]     Ответить | Цитировать Сообщить модератору
 Re: Как транспонировать результат запроса? столбец в строку  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Charles Weyland
хотя бы такое..

я, кстати, только что столкнулся с другой трудностью.
Как запихать в какую-нибудь переменную или во временную таблицу результат рекурсии, созданной с помощью With?
    With DirectReports (id,ParentID,  level)
    as
    (
        Select id, Parent_ID,  0 level from dict_tables
        where id = @id
        union all
        Select dict_tables.id, dict_tables.Parent_ID,  level + 1 from dict_tables
        Inner Join DirectReports on dict_tables.ID = DirectReports.ParentID
        where (level <= 50) --ограничитель числа итераций на случай зацикливания
        and dict_CFRs.ID != DirectReports.ID --если узел сам на себя ссылается, то
                                             --отображаться может не более 1 раза
    )
     select ID, level from DirectReports

а то я с этой таблицей-то сделать ничего не могу! только на экран вывести...


create table #testtable (ID int, level int);

    With DirectReports (id,ParentID,  level)
    as
    (
        Select id, Parent_ID,  0 level from dict_tables
        where id = @id
        union all
        Select dict_tables.id, dict_tables.Parent_ID,  level + 1 from dict_tables
        Inner Join DirectReports on dict_tables.ID = DirectReports.ParentID
        where (level <= 50) --ограничитель числа итераций на случай зацикливания
        and dict_CFRs.ID != DirectReports.ID --если узел сам на себя ссылается, то
                                             --отображаться может не более 1 раза
    )
insert into #testtable (ID, level)
     select ID, level from DirectReports
10 апр 14, 20:24    [15864805]     Ответить | Цитировать Сообщить модератору
 Re: Как транспонировать результат запроса? столбец в строку  [new]
qwrqwr
Member

Откуда: Msk
Сообщений: 1684
SQL2008
Но с подстановкой последнего значения вместо пустого все равно придется поизгаляться.

coalesce(f10,f9,f8,f7,f6,f5,f4,f3,f2,f1)
?
10 апр 14, 20:30    [15864828]     Ответить | Цитировать Сообщить модератору
 Re: Как транспонировать результат запроса? столбец в строку  [new]
Charles Weyland
Member

Откуда: Feorina "Fury" 161
Сообщений: 4374
SQL2008
А что? Запрос такой не работает:
select ID, level 
into #DirectReports
from DirectReports

если честно, не знал, что так можно, проглядел, спасибо.
qwrqwr
coalesce(f10,f9,f8,f7,f6,f5,f4,f3,f2,f1)

да скорее всего, так и придётся... хоть и говнокод, конечно :-(
11 апр 14, 11:20    [15866798]     Ответить | Цитировать Сообщить модератору
 Re: Как транспонировать результат запроса? столбец в строку  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4478
Charles Weyland
да скорее всего, так и придётся... хоть и говнокод, конечно :-(

100% говнокод, никто не спорит.
Но для говнозадач это самое то!
11 апр 14, 15:53    [15868967]     Ответить | Цитировать Сообщить модератору
 Re: Как транспонировать результат запроса? столбец в строку  [new]
Glory
Member

Откуда:
Сообщений: 104751
declare @table table ([name] nvarchar(100),[level] nvarchar(100));

insert into  @table
values(N'Гриб',N'Level01'),
(N'Топор',N'Level02'),
(N'Корабль',N'Level03'),
(N'Штопор',N'Level04'),
(N'Подоконник',N'Level05');

declare @table2 table ([level] nvarchar(100));
insert into  @table2
values(N'Level01'),
(N'Level02'),
(N'Level03'),
(N'Level04'),
(N'Level05'),
(N'Level06'),
(N'Level07'),
(N'Level08'),
(N'Level09'),
(N'Level10');


select [Level01],[Level02],[Level03],[Level04],[Level05],[Level06],[Level07],[Level08],[Level09],[Level10]
from 
(select (select top 1 b.[name] from @table b where b.[level] <= a.[level] order by b.[level] desc) as [name], a.[level] from @table2 a) as t
pivot (max([name]) for [level] in ([Level01],[Level02],[Level03],[Level04],[Level05],[Level06],[Level07],[Level08],[Level09],[Level10])) as p
11 апр 14, 16:21    [15869206]     Ответить | Цитировать Сообщить модератору
 Re: Как транспонировать результат запроса? столбец в строку  [new]
Charles Weyland
Member

Откуда: Feorina "Fury" 161
Сообщений: 4374
SQL2008
Но для говнозадач это самое то!

ах сколько сарказма))



спасибо:

SQL2008, Minamoto, qwrqwr, Glory за сообщения, все ваши ответы были по делу и помогли в решении задачи!
11 апр 14, 16:26    [15869237]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить