Как перевернуть строки в столбцы sql

Как перевернуть строки в столбцы sql

Разворот строк в столбцы — довольно часто возникающая на практике задача. Когда мне пришлось выполнить ее трижды за один день на разных структурах данных — мое терпение лопнуло и решил написать этот топик. У меня получился некий сквозной примерчки — в этом топике я покажу работу с рекордсетом на уровне данных, а в топике Этюды на ASP.NET. Вложенный (nested) Datalist я покажу как отобразить на формах ASP.NET эти рекордсеты.

Ведение счетов пользователей — одна из самых распространенных задачек, которую мне приходилось решать — вот пара скринов с одного моего интернет-банка — процедуры, пользовательский интерфейс.

В этом проекте есть тип счета и курсы валют. Это в принципе вырожденный случай разворота, но он позволяет понять как можно домножить таблу саму на себя и вообще сориентироваться в интернет-банкинге.

Обратите внимание, на точность курсов валют — в этом банке они намеренно поставлены неточные. Для погпужения в вопросы точности округления прочитайте мой топик от 2004-го года — Банковское округление.

И вот простейший вырожденный случай разворота:

 1: CREATE View [dbo].[CursValut]

 2: as

 3: select 

 4: FromVal.i as FromVal_i, FromVal.Name as FromVal_Name, FromVal.OrderBy as FromVal_OrderBy,

 5: ToVal.i as ToVal_i, ToVal.Name as ToVal_Name,

 6: Kurs.i as Kurs_i, Kurs.Kurs as Kurs, Kurs.Date as Kurs_Date

 7: from PriceType as FromVal join Kurs on FromVal.i=Kurs.FromValuta

 8: join PriceType as ToVal on Kurs.Valuta=ToVal.i

 9: GO

Теперь посмотрим более сложный случай разворота. Мне надо было докрутить платежную систему к моему сайту http://arenda.votpusk.ru/. Этот маленький фрагмент для ведения счетов вы видите на рисунке слева. Смысл таблиц понятен из названий: Account — это счета юзеров, Membership и Users — это собтсвенно таблицы юзеров, AccountType — это тип счета пользователя (на момент проектирования я внес туда три типа валюты — Рубль, Яндекс-деньги, WebMoney — понятно что со временем их там может быть и сотня. ActionType — это типы платных действий (на момент проектирования я внес туда два типа «Поднять вверх в общем списке» и «Разместить на входной странице портала». Опять же понятно, что потом тут появятся «Разместить в левом ухе входной странички», «Разместить в подвале второй странички» и так далее до бесконечности. ActionCost — это стоимость каждой такой операции в конкретной валюте. Нужны фирме сейчас Яндекс-деньги — ставим чуть-чуть выгоднее операции по Яндекс-деньгам. AccountLog — журнал операций по счетам и ActionLog — журнал выполненных действий.

Первое же действие по очеловечиванию этой высокореляционной формы — это вьюшка, которая покажет все счета всех юзеров:

 1: ALTER view [dbo].[AllAccount]

 2: as

 3: select Account.*,AccountType.Name,dbo.aspnet_Users.UserName from Account

 4: join dbo.AccountType on dbo.Account.ToValueType=dbo.AccountType.i

 5: join dbo.aspnet_Users on aspnet_Users.UserId=Account.ToUser

 6: GO

и все открытые счета всех юзеров

 1: ALTER View [dbo].[AllOpenAccount]

 2: as

 3: select dbo.aspnet_Users.UserId, dbo.aspnet_Users.UserName,

 4: dbo.AllAccount.Value, dbo.AllAccount.ToValueType,dbo.AllAccount.Name,dbo.AllAccount.SetValueDate

 5: from dbo.aspnet_Users

 6: left join AllAccount on aspnet_Users.UserId=AllAccount.ToUser

 7: GO

В данном случае вот такой процедурой

 1: CREATE Procedure [dbo].[AccountIni]

 2: @ToUser uniqueidentifier,

 3: @AccountType int

 4: as

 5: declare @AccountNumber int

 6: Insert [Account] (ToUser,Value,ToValueType,SetValueDate)

 7: select @ToUser,0,@AccountType,GETDATE()

 8: where not exists (select * from [Account] where ToUser=@ToUser and ToValueType=@AccountType)

 9: select @AccountNumber=SCOPE_IDENTITY()

 10: select [Account].i as AccountNumber, Value, ToValueType,AccountType.Name from [Account]

 11: join dbo.AccountType on [Account].ToValueType=dbo.AccountType.i

 12: where ToUser=@ToUser and ToValueType=@AccountType

я открыл три тестовых счета одному и тому же тестовому юзеру:

Однако на формах так показать данные нельзя — через полгода тут будет 50 тысяч юзеров в 20-ти валютах — что делать? Разумно было бы развернуть эту табличку и сделать вьюшку — которая покажет по одной строке на юзера — а каждый открытый счет в каждой валюте будет выводится в своем столбце.

Таким образом ровно через минуту проектирования системы — упираемся в типичную задачку разворота строк в столбцы.

В MS SQL ее можно решать двумя путями — добавляя столбцы с валютами конструкцией CASE или заджойнив дополнительные таблы и показав что-то из них. В данном случае выписывать гирлянды CASE мне было лень, и я сформулировал разворот так:

 1: select AllRows.UserID, AllRows.UserName,

 2: ValType1.value as Value1, ValType1.ToValueType as ValueType1, ValType1.Name as Name1, ValType1.SetValueDate as SetValueDate1,

 3: ValType2.value as Value2, ValType2.ToValueType as ValueType2, ValType2.Name as Name2, ValType2.SetValueDate as SetValueDate2,

 4: ValType3.value as Value3, ValType3.ToValueType as ValueType3, ValType3.Name as Name3, ValType3.SetValueDate as SetValueDate3

 5: from (select distinct UserID,UserName from AllOpenAccount) as AllRows

 6: left join AllOpenAccount as ValType1 on AllRows.UserID=ValType1.UserID and ValType1.ToValueType=1

 7: left join AllOpenAccount as ValType2 on AllRows.UserID=ValType2.UserID and ValType2.ToValueType=2

 8: left join AllOpenAccount as ValType3 on AllRows.UserID=ValType3.UserID and ValType3.ToValueType=3

Вот и весь принцип разворота в MS SQL. Далее надо все это обернуть в динамику Exec(String) — если количество волют будет меняться — чтобы процедуру не переписывать. Или если с количеством валют админы сайта определятся — то можно просто сделать на этом вьюшку без всякой динамики.

Теперь посмотрим на удивительное по красоте решение в PostgreSQL. Здесь тоже есть данные в высокореляционной форме для типичной скучнейшей складской задачки:

Здесь есть список товаров:

И значения свойств товара:

Понятно что для любых отборов и отображений товаров надо поставить все свойства товаров в одну строку. Посмотрите на изумительное по красоте решение в PostgreSQL.

Сначала я сделал маленькую промежуточную вьюшечку, чтобы утопить джойн по гуидам свойств и получить правильные названия столбцов:

 1: CREATE OR REPLACE VIEW "GET_СвойстваТовара" AS 

 2: SELECT "ЗначенияСвойствТовара".totovar, "СвойстваТовара"."Наименование", "ЗначенияСвойствТовара"."Значение"

 3: FROM "СвойстваТовара"

 4: JOIN "ЗначенияСвойствТовара" ON "СвойстваТовара"."Ид"::text = "ЗначенияСвойствТовара"."Ид"::text;

И вот оно чудо — вьюшка, полностью развернувшая строки в столбцы:

 1: CREATE OR REPLACE VIEW "GET_ВсеСвойстваТовара"

 2: AS 

 3: SELECT * FROM crosstab(

 4: 'select * from "GET_СвойстваТовара"'::text,

 5: 'select distinct "Наименование" from "GET_СвойстваТовара" order by "Наименование"'::text

 7: as

 9: totovar integer, "Вылет - ET (мм)" character varying,

 10: "Индекс нагрузки шины" character varying,

 11: "Индекс скорости шины" character varying,

 12: "Посадочный диаметр шины (дюйм)" character varying,

 13: "Размер диска" character varying,

 14: "Размер шины" character varying,

 15: "Сверловка" character varying,

 16: "Сезонность шины" character varying,

 17: "Тип шины" character varying,

 18: "Цвет" character varying,

 19: "Центрально отверстие - DIA (мм)" character varying,

 20: "Шина повышенной проходимости (M+S)" character varying,

 21: "Шина усиленная (C)" character varying,

 22: "Шипованная шина" character varying

При попытке повторить это чудо — учтите что функция crosstab не устанавливается автоматически при инсталляции. В виндузне для установки надо найти каталог contrib (C:\Program Files (x86)\PostgresPlus\8.4SS\share\contrib) и кликнуть там по tablefunc.sql

Транспонирование таблицы SQL: какой запрос может в этом помочь

Транспонирование таблицы SQL — это специфическое преобразование таблицы, после которого столбцы становятся строками, а строки — столбцами. Для такой манипуляции существуют специальные конструкции запросов, где используются операторы PIVOT и UNPIVOT. Использование этих операторов имеет довольно специфический синтаксис , н о с точки зрения эффективност и о ни отлично справляются с возложенной на них задачей.

Эти операторы хороши тем, что могут транспонировать небольшие данные, например , только одну строку или столбец таблицы. Но они также могут транспонировать всю таблицу целиком, то есть все столбцы со всеми строками.

Можно ли транспонировать таблицу SQL другими способами? Да, можно, но это будут сложные самодельные конструкции. Нет смысла заострять на них свое внимание, если для транспонирования присутствуют специальные операторы.

Транспонирование таблицы SQL

Чтобы лучше понять, как происходит транспонирование таблицы SQL, давайте разберем это действие на практике. Представим, что у вас есть некая SQL-таблица вот такого вида:

Дормидонт	Платон	Тимати	Аристарх
Красный	3	7	3	5
Зеленый	10	6	5	7
Голубой	4	4	11	3

Вам нужно транспонировать эту таблицу SQL таким образом, чтобы из нее получилась такая:

Красный	Зеленый	Голубой
Дормидонт	3	10	4
Платон	7	6	4
Тимати	3	5	11
Аристарх	5	7	3

Когда вы создаете такую таблицу в SQL, код будет следующий:

CREATE TABLE transTable([color] varchar(5), [Дормидонт] int, [Платон] int, [Тимати] int, [Аристарх] int);

INSERT INTO transTable

([color], [Дормидонт], [Платон], [Тимати], [Аристарх])

VALUES

(‘Красный’, 3, 7, 3, 5),

(‘Зеленый’, 10, 6, 5, 7),

(‘Голубой’, 4, 4, 11, 3);

Транспонируем нашу SQL-таблицу с помощью специальной конструкции с применением операторов «pivot» и «unpiv o t». Код будет следующим:

select name, [Красный], [Зеленый], [Голубой]

from

(

select color, name, value

from transtable

unpivot

(

value for name in (Дормидонт, Платон, Тимати, Аристарх)

) unpiv

) src

pivot

(

sum(value)

for color in ([Красный], [Зеленый], [Голубой])

) piv

Заключение

Транспонирование таблицы SQL требуется достаточно редко. Транспонирование можно описать как процесс перевода столбцов в строки. Сегодня мы показали простейший, но эффективный способ, как транспонировать SQL-таблицу при помощи специальных операторов «pivot» и «unpivot».

Мы будем очень благодарны

если под понравившемся материалом Вы нажмёте одну из кнопок социальных сетей и поделитесь с друзьями.

Как «перевернуть» вывод, т.е. строки сделать столбцами?

При условии, что нам известны все возможные варианты Items , например ‘Keyboard’, ‘Mouse’ и ‘Printer’:

select sum(case Item when 'Keyboard' then 1 else 0 end) as Keyboard, sum(case Item when 'Mouse' then 1 else 0 end) as Mouse, sum(case Item when 'Printer' then 1 else 0 end) as Printer from [table] 

UPD1. Чуток почесав голову становится ясно, как повторить то же самое для произвольного неизвестного нам набора item-ов (но тип-то хотя бы знать надо).

declare @query varchar(MAX), @item varchar(50) declare cur cursor for select distinct item from [table] open cur set @query = 'select ' fetch cur into @item while @@fetch_status = 0 begin set @query = @query + 'sum(case Item when ''' + @item + ''' then 1 else 0 end) as ' + @item + ',' fetch cur into @item end set @query = LEFT(@query, LEN(@query) - 1) + ' from [table]' execute(@query) close cur deallocate cur 

UPD2. @minamoto дело говорит, жульничать плохо. Более достоверный вариант.

declare @query1 varchar(MAX), @query2 varchar(MAX), @item varchar(50), @i int declare cur cursor for select distinct item from [table] open cur set @query1 = 'select ' set @query2 = 'select ' set @i = 0 fetch cur into @item while @@fetch_status = 0 begin set @query1 = @query1 + '''' + @item + ''' as col' + Cast(@i as varchar(50)) + ',' set @query2 = @query2 + 'cast(sum(case Item when ''' + @item + ''' then 1 else 0 end) as varchar(50)),' set @i = @i + 1 fetch cur into @item end set @query1 = LEFT(@query1, LEN(@query1) - 1) set @query2 = LEFT(@query2, LEN(@query2) - 1) + ' from [table]' execute(@query1 + ' UNION ' + @query2 + ' order by col1 desc') close cur deallocate cur 

ЗЫ. написано на T-SQL, если что. проверено на MSSQL 2005

Смена таблицы в SQL Server

В этой статье описывается, как повернуть таблицу в SQL Server.

Оригинальная версия продукта: SQL Server
Исходный номер базы знаний: 175574

Сводка

В этой статье описывается, как повернуть таблицу SQL Server. Предположим, что у вас есть таблица с именем QTRSALES . Таблица содержит столбцы YEAR , QUARTER и AMOUNT с данными в следующем формате.

За четвертый квартал 1996 года нет строки:

Год	Квартал	Amount
1995	1	125,000.90
1995	2	136,000.75
1995	3	212,000.34
1995	4	328,000.82
1996	3	728,000.35
1996	2	422,000.13
1996	1	328,000.82

Теперь предположим, что вы хотите повернуть таблицу, чтобы увидеть данные в следующем формате:

YEAR	Q1	В 2-й квартал	В3	Вопрос 4
1995	125,000.90	136,000.75	212,000.34	328,000.82
1996	328,000.82	422,000.13	728,000.35	0.00

Запрос, который будет использоваться для смены таблицы, приведен в следующем разделе этой статьи.

Пример запроса для смены таблицы

Ниже приведен запрос, используемый для смены таблицы:

SELECT YEAR, Q1= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 1 AND YEAR = Q.YEAR),0), Q2= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 2 AND YEAR = Q.YEAR),0), Q3= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 3 AND YEAR = Q.YEAR),0), Q4= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 4 AND YEAR = Q.YEAR),0) FROM QTRSALES Q GROUP BY YEAR 

Запрос больших таблиц

Для больших таблиц этот запрос будет выполняться быстрее:

SELECT YEAR, SUM(CASE quarter WHEN 1 THEN amount ELSE 0 END) AS Q1, SUM(CASE quarter WHEN 2 THEN amount ELSE 0 END) AS Q2, SUM(CASE quarter WHEN 3 THEN amount ELSE 0 END) AS Q3, SUM(CASE quarter WHEN 4 THEN amount ELSE 0 END) AS Q4 FROM qtrsales q GROUP BY YEAR