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

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

Lorem ipsum dolor

Транспонирование таблицы 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».

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

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

Simple way to transpose columns and rows in SQL?

How do I simply switch columns with rows in SQL? Is there any simple command to transpose?

ie turn this result:

PIVOT seems too complex for this scenario.

9 Answers 9

There are several ways that you can transform this data. In your original post, you stated that PIVOT seems too complex for this scenario, but it can be applied very easily using both the UNPIVOT and PIVOT functions in SQL Server.

However, if you do not have access to those functions this can be replicated using UNION ALL to UNPIVOT and then an aggregate function with a CASE statement to PIVOT :

Create Table:

Union All, Aggregate and CASE Version:

The UNION ALL performs the UNPIVOT of the data by transforming the columns Paul, John, Tim, Eric into separate rows. Then you apply the aggregate function sum() with the case statement to get the new columns for each color .

Unpivot and Pivot Static Version:

Both the UNPIVOT and PIVOT functions in SQL server make this transformation much easier. If you know all of the values that you want to transform, you can hard-code them into a static version to get the result:

The inner query with the UNPIVOT performs the same function as the UNION ALL . It takes the list of columns and turns it into rows, the PIVOT then performs the final transformation into columns.

Dynamic Pivot Version:

If you have an unknown number of columns ( Paul, John, Tim, Eric in your example) and then an unknown number of colors to transform you can use dynamic sql to generate the list to UNPIVOT and then PIVOT :

The dynamic version queries both yourtable and then the sys.columns table to generate the list of items to UNPIVOT and PIVOT . This is then added to a query string to be executed. The plus of the dynamic version is if you have a changing list of colors and/or names this will generate the list at run-time.

All three queries will produce the same result:

This normally requires you to know ALL the column AND row labels beforehand. As you can see in the query below, the labels are all listed in their entirely in both the UNPIVOT and the (re)PIVOT operations.

MS SQL Server 2012 Schema Setup:

Query 1:

Additional Notes:

Given a table name, you can determine all the column names from sys.columns or FOR XML trickery using local-name().
You can also build up the list of distinct colors (or values for one column) using FOR XML.
The above can be combined into a dynamic sql batch to handle any table.

I’d like to point out few more solutions to transposing columns and rows in SQL.

The first one is — using CURSOR. Although the general consensus in the professional community is to stay away from SQL Server Cursors, there are still instances whereby the use of cursors is recommended. Anyway, Cursors present us with another option to transpose rows into columns.

Vertical expansion

Similar to the PIVOT, the cursor has the dynamic capability to append more rows as your dataset expands to include more policy numbers.

Horizontal expansion

Unlike the PIVOT, the cursor excels in this area as it is able to expand to include newly added document, without altering the script.

Performance breakdown

The major limitation of transposing rows into columns using CURSOR is a disadvantage that is linked to using cursors in general – they come at significant performance cost. This is because the Cursor generates a separate query for each FETCH NEXT operation.

Another solution of transposing rows into columns is by using XML.

The XML solution to transposing rows into columns is basically an optimal version of the PIVOT in that it addresses the dynamic column limitation.

The XML version of the script addresses this limitation by using a combination of XML Path, dynamic T-SQL and some built-in functions (i.e. STUFF, QUOTENAME).

Vertical expansion

Similar to the PIVOT and the Cursor, newly added policies are able to be retrieved in the XML version of the script without altering the original script.

Horizontal expansion

Unlike the PIVOT, newly added documents can be displayed without altering the script.

Performance breakdown

In terms of IO, the statistics of the XML version of the script is almost similar to the PIVOT – the only difference is that the XML has a second scan of dtTranspose table but this time from a logical read – data cache.

You can find some more about these solutions (including some actual T-SQL exmaples) in this article: https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/

Based on this solution from bluefeet here is a stored procedure that uses dynamic sql to generate the transposed table. It requires that all the fields are numeric except for the transposed column (the column that will be the header in the resulting table):

You can test it with the table provided with this command:

I’m doing UnPivot first and storing the results in CTE and using the CTE in Pivot operation.

Adding to @Paco Zarate’s terrific answer above, if you want to transpose a table which has multiple types of columns, then add this to the end of line 39, so it only transposes int columns:

Here is the full query that is being changed:

To find other system_type_id ‘s, run this:

This way Convert all Data From Filelds(Columns) In Table To Record (Row).

I like to share the code i’m using to transpose a splited text based on +bluefeet answer. In this aproach i’m implemented as a procedure in MS SQL 2005

I’m mixing this solution with the information about howto order rows without order by (SQLAuthority.com) and the split function on MSDN (social.msdn.microsoft.com)

When you execute the prodecure

you obtaint the next result

I was able to use Paco Zarate’s solution and it works beautifully. I did have to add one line («SET ANSI_WARNINGS ON»), but that may be something unique to the way I used it or called it. There is a problem with my usage and I hope someone can help me with it:

The solution works only with an actual SQL table. I tried it with a temporary table and also an in-memory (declared) table but it doesn’t work with those. So in my calling code I create a table on my SQL database and then call SQLTranspose. Again, it works great. It’s just what I want. Here’s my problem:

In order for the overall solution to be truly dynamic I need to create that table where I temporarily store the prepared information that I’m sending to SQLTranspose «on the fly», and then delete that table once SQLTranspose is called. The table deletion is presenting a problem with my ultimate implementation plan. The code needs to run from an end-user application (a button on a Microsoft Access form/menu). When I use this SQL process (create a SQL table, call SQLTranspose, delete SQL table) the end user application hits an error because the SQL account used does not have the rights to drop a table.

So I figure there are a few possible solutions:

Find a way to make SQLTranspose work with a temporary table or a declared table variable.

Figure out another method for the transposition of rows and columns that doesn’t require an actual SQL table.

Figure out an appropriate method of allowing the SQL account used by my end users to drop a table. It’s a single shared SQL account coded into my Access application. It appears that permission is a dbo-type privilege that cannot be granted.

I recognize that some of this may warrant another, separate thread and question. However, since there is a possibility that one solution may be simply a different way to do the transposing of rows and columns I’ll make my first post here in this thread.

EDIT: I also did replace sum(value) with max(value) in the 6th line from the end, as Paco suggested.

I figured out something that works for me. I don’t know if it’s the best answer or not.

I have a read-only user account that is used to execute strored procedures and therefore generate reporting output from a database. Since the SQLTranspose function I created will only work with a «legitimate» table (not a declared table and not a temporary table) I had to figure out a way for a read-only user account to create (and then later delete) a table.

I reasoned that for my purposes it’s okay for the user account to be allowed to create a table. The user still could not delete the table though. My solution was to create a schema where the user account is authorized. Then whenever I create, use, or delete that table refer it with the schema specified.

I first issued this command from a ‘sa’ or ‘sysadmin’ account: CREATE SCHEMA ro AUTHORIZATION

When any time I refer to my «tmpoutput» table I specify it like this example:

Оператор PIVOT

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

Обратите внимание, что в этой таблице на одного сотрудника приходится одна строка на каждый год. Кроме того, сотрудники 2 и 3 имеют данные о продажах только за два года из трех. Теперь предположим, что мы хотим показать эти данные в табличном виде с одной строкой на каждого сотрудника и данными о продажах за все три года в этой строке. Мы можем очень легко добиться этого, используя оператор преобразования PIVOT:

Я не буду углубляться в синтаксис PIVOT, который хорошо описан в электронной документации. Достаточно сказать, что использование этого оператора позволяет суммировать продажи по каждому сотруднику за каждый год, перечисленный в списке, и представить результат в виде одной строки для каждого сотрудника. Ниже представлена результирующая выборка:

Обратите внимание, что SQL Server выводит NULL для отсутствующих данных о продажах сотрудников 2 и 3.

Ключевое слово SUM (или либо другой агрегат) является обязательным. Если таблица «Sales» содержит для сотрудника за какой-то год несколько строк, PIVOT в результате объединяет их (в данном случае путем суммирования) в одну строку данных. Разумеется, поскольку в этом примере запись в каждой «ячейке» выборки является результатом суммирования одной строки, мы также легко могли бы использовать и другой агрегат, например, MIN или MAX. Я использовал SUM, поскольку он более интуитивно понятен.

Этот пример с PIVOT является обратимым. Информацию из выборки можно использовать для восстановления исходной таблицы с помощью оператора UNPIVOT (о котором я расскажу в следующей статье). Однако не все операции с PIVOT являются обратимыми. Чтобы быть обратимой, операция с PIVOT должна соответствовать следующим критериям:

Все входные данные должны подпадать под преобразование. Если будет использоваться какой-либо фильтр, в том числе предложение IN, некоторые данные могут быть исключены из результата PIVOT. Например, если бы мы в приведенном выше примере выбирали данные о продажах только за 2006 и 2007 годы, очевидно, что мы не смогли бы восстановить из выборки данные о продажах за 2005 год.

Каждая ячейка результирующей таблицы должна быть получена из одной строки таблицы на входе. Если в одну ячейку будут объединены несколько строк таблицы на входе, восстановить исходные входные строки будет невозможно.

Агрегатная функция должна быть реверсивной (при использовании одной строки на входе). SUM, MIN, MAX и AVG возвращают одно, полученное из таблицы на входе значение без изменений и, таким образом, могут быть реверсированы. COUNT не возвращает свое входное значение без изменений и, следовательно, не может быть обратимо.

Ниже представлен пример необратимой операции PIVOT. В нём рассчитывается общий объем продаж для всех сотрудников за все три года, но результат не детализируется по сотруднику.

Результат исполнения запроса показан ниже. Каждая ячейка представляет собой сумму двух или трех строк таблицы на входе.