Как просто переключать столбцы с строками в SQL? Есть ли простая команда для транспонирования?
т.е. повернуть этот результат:
Paul | John | Tim | Eric
Red 1 5 1 3
Green 8 4 3 5
Blue 2 2 9 1
в это:
Red | Green | Blue
Paul 1 8 2
John 5 4 2
Tim 1 3 9
Eric 3 5 1
PIVOT кажется слишком сложным для этого сценария.
Существует несколько способов преобразования этих данных. В вашем первоначальном посте вы указали, что PIVOT кажется слишком сложным для этого сценария, но его можно применять очень легко, используя UNPIVOT и PIVOT в SQL Server.
Однако, если у вас нет доступа к этим функциям, это можно реплицировать с помощью UNION ALL в UNPIVOT, а затем агрегированную функцию с оператором CASE на PIVOT:
Создать таблицу:
CREATE TABLE yourTable([color] varchar(5), [Paul] int, [John] int, [Tim] int, [Eric] int);
INSERT INTO yourTable
([color], [Paul], [John], [Tim], [Eric])
VALUES
('Red', 1, 5, 1, 3),
('Green', 8, 4, 3, 5),
('Blue', 2, 2, 9, 1);
Союз Все, Агрегат и версия CASE:
select name,
sum(case when color = 'Red' then value else 0 end) Red,
sum(case when color = 'Green' then value else 0 end) Green,
sum(case when color = 'Blue' then value else 0 end) Blue
from
(
select color, Paul value, 'Paul' name
from yourTable
union all
select color, John value, 'John' name
from yourTable
union all
select color, Tim value, 'Tim' name
from yourTable
union all
select color, Eric value, 'Eric' name
from yourTable
) src
group by name
Смотрите SQL Fiddle with Demo
UNION ALL выполняет UNPIVOT данных, преобразовывая столбцы Paul, John, Tim, Eric в отдельные строки. Затем вы применяете агрегированную функцию sum() к оператору CASE, чтобы получить новые столбцы для каждого color.
Unpivot и Pivot Static Version:
Обе функции UNPIVOT и PIVOT на сервере SQL делают это преобразование намного проще. Если вы знаете все значения, которые вы хотите преобразовать, вы можете жестко записать их в статическую версию, чтобы получить результат:
select name, [Red], [Green], [Blue]
from
(
select color, name, value
from yourtable
unpivot
(
value for name in (Paul, John, Tim, Eric)
) unpiv
) src
pivot
(
sum(value)
for color in ([Red], [Green], [Blue])
) piv
Смотрите SQL Fiddle with Demo
Внутренний запрос с помощью UNPIVOT выполняет ту же функцию, что и UNION ALL. Он принимает список столбцов и превращает его в строки, затем PIVOT выполняет окончательное преобразование в столбцы.
Динамическая сводная версия:
Если у вас есть неизвестное количество столбцов (Paul, John, Tim, Eric в вашем примере), а затем неизвестное количество цветов для преобразования, вы можете использовать динамический sql для генерации списка UNPIVOT, а затем PIVOT:
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX)
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id('yourtable') and
C.name <> 'color'
for xml path('')), 1, 1, '')
select @colsPivot = STUFF((SELECT ','
+ quotename(color)
from yourtable t
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select name, '[email protected]+'
from
(
select color, name, value
from yourtable
unpivot
(
value for name in ('[email protected]+')
) unpiv
) src
pivot
(
sum(value)
for color in ('[email protected]+')
) piv'
exec(@query)
Динамическая версия запрашивает как yourtable, так и таблицу sys.columns, чтобы сгенерировать список элементов UNPIVOT и PIVOT. Затем он добавляется к строке запроса, которая должна быть выполнена. Плюс динамической версии - если у вас есть список изменений colors и/или names, это приведет к созданию списка во время выполнения.
Все три запроса приведут к такому же результату:
| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric | 3 | 5 | 1 |
| John | 5 | 4 | 2 |
| Paul | 1 | 8 | 2 |
| Tim | 1 | 3 | 9 |
Это обычно требует, чтобы вы знали ВСЕ метки столбца И строки заранее. Как вы можете видеть в следующем запросе, все эти ярлыки перечислены полностью в операциях UNPIVOT и (re) PIVOT.
Настройка схемы MS SQL Server 2012:
create table tbl (
color varchar(10), Paul int, John int, Tim int, Eric int);
insert tbl select
'Red' ,1 ,5 ,1 ,3 union all select
'Green' ,8 ,4 ,3 ,5 union all select
'Blue' ,2 ,2 ,9 ,1;
Запрос 1:
select *
from tbl
unpivot (value for name in ([Paul],[John],[Tim],[Eric])) up
pivot (max(value) for color in ([Red],[Green],[Blue])) p
| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric | 3 | 5 | 1 |
| John | 5 | 4 | 2 |
| Paul | 1 | 8 | 2 |
| Tim | 1 | 3 | 9 |
Я хотел бы указать еще несколько решений на перенос столбцов и строк в SQL.
Первый - это использование CURSOR. Хотя общий консенсус в профессиональном сообществе заключается в том, чтобы держаться подальше от SQL Server Cursors, все еще есть случаи, когда рекомендуется использовать курсоры. В любом случае, курсоры представляют нам еще один вариант переноса строк в столбцы.
Вертикальное расширение
Подобно PIVOT, курсор имеет динамическую возможность добавлять больше строк, поскольку ваш набор данных расширяется, чтобы включить больше номеров политик.
Горизонтальное расширение
В отличие от PIVOT, курсор выделяется в этой области, поскольку он может расширяться, чтобы включать недавно добавленный документ, не изменяя script.
Прорыв производительности
Основным ограничением переноса строк в столбцы с использованием CURSOR является недостаток, связанный с использованием курсоров в целом - они имеют значительную стоимость. Это связано с тем, что курсор генерирует отдельный запрос для каждой операции FETCH NEXT.
Другим решением переноса строк в столбцы является использование XML.
XML-решение для переноса строк в столбцы в основном является оптимальной версией PIVOT, поскольку оно обращается к ограничению динамического столбца.
XML-версия script адресует это ограничение, используя комбинацию XML Path, динамического T-SQL и некоторых встроенных функций (например, STUFF, QUOTENAME).
Вертикальное расширение
Подобно PIVOT и курсору, вновь добавленные политики могут быть восстановлены в XML-версии script без изменения исходного script.
Горизонтальное расширение
В отличие от PIVOT, новые добавленные документы могут отображаться без изменения script.
Прорыв производительности
В терминах IO статистика XML-версии script почти похожа на PIVOT - единственное различие заключается в том, что XML имеет второе сканирование таблицы dtTranspose, но на этот раз из кеша логического кэша чтения.
В этой статье вы можете найти более подробную информацию об этих решениях (включая некоторые фактические примеры T-SQL): https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/
На основе этого от bluefeet здесь хранится процедура, которая использует динамический sql для генерации транспонированная таблица. Для этого требуется, чтобы все поля были числовыми, за исключением транспонированного столбца (столбец, который будет заголовком в результирующей таблице):
/****** Object: StoredProcedure [dbo].[SQLTranspose] Script Date: 11/10/2015 7:08:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Paco Zarate
-- Create date: 2015-11-10
-- Description: SQLTranspose dynamically changes a table to show rows as headers. It needs that all the values are numeric except for the field using for transposing.
-- Parameters: @TableName - Table to transpose
-- @FieldNameTranspose - Column that will be the new headers
-- Usage: exec SQLTranspose <table>, <FieldToTranspose>
-- =============================================
ALTER PROCEDURE [dbo].[SQLTranspose]
-- Add the parameters for the stored procedure here
@TableName NVarchar(MAX) = '',
@FieldNameTranspose NVarchar(MAX) = ''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@queryPivot AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX),
@columnToPivot as NVARCHAR(MAX),
@tableToPivot as NVARCHAR(MAX),
@colsResult as xml
select @tableToPivot = @TableName;
select @columnToPivot = @FieldNameTranspose
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id(@tableToPivot) and
C.name <> @columnToPivot
for xml path('')), 1, 1, '')
set @queryPivot = 'SELECT @colsResult = (SELECT '',''
+ quotename('[email protected]+')
from '[email protected]+' t
where '[email protected]+' <> ''''
FOR XML PATH(''''), TYPE)'
exec sp_executesql @queryPivot, N'@colsResult xml out', @colsResult out
select @colsPivot = STUFF(@colsResult.value('.', 'NVARCHAR(MAX)'),1,1,'')
set @query
= 'select name, rowid, '[email protected]+'
from
(
select '[email protected]+' , name, value, ROW_NUMBER() over (partition by '[email protected]+' order by '[email protected]+') as rowid
from '[email protected]+'
unpivot
(
value for name in ('[email protected]+')
) unpiv
) src
pivot
(
sum(value)
for '[email protected]+' in ('[email protected]+')
) piv
order by rowid'
exec(@query)
END
Вы можете проверить его с помощью таблицы, указанной в этой команде:
exec SQLTranspose 'yourTable', 'color'
Сначала я делаю UnPivot и сохраняю результаты в CTE и используя операцию CTE в Pivot.
with cte as
(
select 'Paul' as Name, color, Paul as Value
from yourTable
union all
select 'John' as Name, color, John as Value
from yourTable
union all
select 'Tim' as Name, color, Tim as Value
from yourTable
union all
select 'Eric' as Name, color, Eric as Value
from yourTable
)
select Name, [Red], [Green], [Blue]
from
(
select *
from cte
) as src
pivot
(
max(Value)
for color IN ([Red], [Green], [Blue])
) as Dtpivot;
Добавляя к @Paco Zarate потрясающий ответ выше, если вы хотите перенести таблицу с несколькими типами столбцов, добавьте ее в конец строки 39, поэтому она переносит только int столбцы:
and C.system_type_id = 56 --56 = type int
Вот полный запрос, который изменяется:
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id(@tableToPivot) and
C.name <> @columnToPivot and C.system_type_id = 56 --56 = type int
for xml path('')), 1, 1, '')
Чтобы найти другие system_type_id, запустите это:
select name, system_type_id from sys.types order by name
Мне нравится делиться кодом, который я использую, чтобы транспонировать расщепленный текст на основе ответа + bluefeet. В этом aproach я реализован как процедура в MS SQL 2005
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: ELD.
-- Create date: May, 5 2016.
-- Description: Transpose from rows to columns the user split function.
-- =============================================
CREATE PROCEDURE TransposeSplit @InputToSplit VARCHAR(8000)
,@Delimeter VARCHAR(8000) = ','
AS
BEGIN
SET NOCOUNT ON;
DECLARE @colsUnpivot AS NVARCHAR(MAX)
,@query AS NVARCHAR(MAX)
,@queryPivot AS NVARCHAR(MAX)
,@colsPivot AS NVARCHAR(MAX)
,@columnToPivot AS NVARCHAR(MAX)
,@tableToPivot AS NVARCHAR(MAX)
,@colsResult AS XML
SELECT @tableToPivot = '#tempSplitedTable'
SELECT @columnToPivot = 'col_number'
CREATE TABLE #tempSplitedTable (
col_number INT
,col_value VARCHAR(8000)
)
INSERT INTO #tempSplitedTable (
col_number
,col_value
)
SELECT ROW_NUMBER() OVER (
ORDER BY (
SELECT 100
)
) AS RowNumber
,item
FROM [DB].[ESCHEME].[fnSplit](@InputToSplit, @Delimeter)
SELECT @colsUnpivot = STUFF((
SELECT ',' + quotename(C.NAME)
FROM [tempdb].sys.columns AS C
WHERE C.object_id = object_id('tempdb..' + @tableToPivot)
AND C.NAME <> @columnToPivot
FOR XML path('')
), 1, 1, '')
SET @queryPivot = 'SELECT @colsResult = (SELECT '',''
+ quotename(' + @columnToPivot + ')
from ' + @tableToPivot + ' t
where ' + @columnToPivot + ' <> ''''
FOR XML PATH(''''), TYPE)'
EXEC sp_executesql @queryPivot
,N'@colsResult xml out'
,@colsResult OUT
SELECT @colsPivot = STUFF(@colsResult.value('.', 'NVARCHAR(MAX)'), 1, 1, '')
SET @query = 'select name, rowid, ' + @colsPivot + '
from
(
select ' + @columnToPivot + ' , name, value, ROW_NUMBER() over (partition by ' + @columnToPivot + ' order by ' + @columnToPivot + ') as rowid
from ' + @tableToPivot + '
unpivot
(
value for name in (' + @colsUnpivot + ')
) unpiv
) src
pivot
(
MAX(value)
for ' + @columnToPivot + ' in (' + @colsPivot + ')
) piv
order by rowid'
EXEC (@query)
DROP TABLE #tempSplitedTable
END
GO
Я смешиваю это решение с информацией о том, как упорядочить строки без порядка (SQLAuthority.com) и функция расщепления на MSDN (social.msdn.microsoft.com)
Когда вы выполняете prodecure
DECLARE @RC int
DECLARE @InputToSplit varchar(MAX)
DECLARE @Delimeter varchar(1)
set @InputToSplit = 'hello|beautiful|world'
set @Delimeter = '|'
EXECUTE @RC = [TransposeSplit]
@InputToSplit
,@Delimeter
GO
вы получите следующий результат
name rowid 1 2 3
col_value 1 hello beautiful world
Таким образом, преобразуйте все данные из файловых серверов (столбцы) в таблицу для записи (строка).
Declare @TableName [nvarchar](128)
Declare @ExecStr nvarchar(max)
Declare @Where nvarchar(max)
Set @TableName = 'myTableName'
--Enter Filtering If Exists
Set @Where = ''
--Set @ExecStr = N'Select * From '+quotename(@TableName)[email protected]
--Exec(@ExecStr)
Drop Table If Exists #tmp_Col2Row
Create Table #tmp_Col2Row
(Field_Name nvarchar(128) Not Null
,Field_Value nvarchar(max) Null
)
Set @ExecStr = N' Insert Into #tmp_Col2Row (Field_Name , Field_Value) '
Select @ExecStr += (Select N'Select '''+C.name+''' ,Convert(nvarchar(max),'+quotename(C.name) + ') From ' + quotename(@TableName)[email protected]+Char(10)+' Union All '
from sys.columns as C
where (C.object_id = object_id(@TableName))
for xml path(''))
Select @ExecStr = Left(@ExecStr,Len(@ExecStr)-Len(' Union All '))
--Print @ExecStr
Exec (@ExecStr)
Select * From #tmp_Col2Row
Go
Мне удалось использовать решение Paco Zarate, и оно прекрасно работает. Мне нужно было добавить одну строку ("SET ANSI_WARNINGS ON"), но это может быть чем-то уникальным для того, как я его использовал или назвал. Существует проблема с моим использованием, и я надеюсь, что кто-то может помочь мне с этим:
Решение работает только с реальной таблицей SQL. Я попытался сделать это с временной таблицей, а также с таблицей в памяти (объявленной), но она не работает с ними. Поэтому в своем коде вызова я создаю таблицу в базе данных SQL, а затем вызываю SQLTranspose. Опять же, это прекрасно работает. Это как раз то, что я хочу. Вот моя проблема:
Для того чтобы общее решение было действительно динамичным, мне нужно создать эту таблицу, в которой я временно храню подготовленную информацию, которую я отправляю в SQLTranspose "на лету", а затем удаляю эту таблицу после вызова SQLTranspose. Удаление таблицы представляет проблему с моим окончательным планом реализации. Код должен запускаться из приложения конечного пользователя (кнопка в форме/меню Microsoft Access). Когда я использую этот процесс SQL (создайте таблицу SQL, вызовите SQLTranspose, удалите таблицу SQL), приложение конечного пользователя обнаружит ошибку, поскольку используемая учетная запись SQL не имеет прав на удаление таблицы.
Поэтому я полагаю, что есть несколько возможных решений:
Найдите способ заставить SQLTranspose работать с временной таблицей или объявленной табличной переменной.
Выясните другой метод для транспонирования строк и столбцов, который не требует реальной таблицы SQL.
Найдите подходящий метод, позволяющий учетной записи SQL, используемой моими конечными пользователями, удалять таблицу. Это одна общая учетная запись SQL, закодированная в моем приложении Access. Похоже, что разрешение является привилегией типа dbo, которая не может быть предоставлена.
Я признаю, что кое-что из этого может потребовать другой, отдельной темы и вопроса. Однако, поскольку существует вероятность того, что одно решение может быть просто другим способом сделать транспонирование строк и столбцов, я сделаю мой первый пост здесь в этой теме.
РЕДАКТИРОВАТЬ: Я также заменил сумму (значение) с макс (значение) в 6-й строке от конца, как предложил Пако.
РЕДАКТИРОВАТЬ:
Я понял то, что работает для меня. Я не знаю, лучший ответ или нет.
У меня есть учетная запись пользователя только для чтения, которая используется для выполнения потоковых процедур и, следовательно, для создания отчетов из базы данных. Поскольку созданная мной функция SQLTranspose будет работать только с "легитимной" таблицей (не объявленной и не временной), я должен был найти способ для учетной записи пользователя, доступной только для чтения, создать (а затем удалить) таблицу,
Я полагал, что для моих целей разрешено создавать учетную запись для учетной записи пользователя. Пользователь по-прежнему не может удалить таблицу. Моим решением было создать схему, в которой учетная запись пользователя будет авторизована. Затем всякий раз, когда я создаю, использую или удаляю эту таблицу, ссылаюсь на нее с указанной схемой.
Сначала я выполнил эту команду из учетной записи 'sa' или 'sysadmin': CREATE SCHEMA ro AUTHORIZATION
Когда я в любое время обращаюсь к своей таблице "tmpoutput", я указываю ее, как в следующем примере:
Drop Table ro.tmpoutput