Простой способ транспонирования столбцов и строк в Sql?

обычно это требует, чтобы вы заранее знали все метки столбцов и строк. Как вы можете видеть в приведенном ниже запросе, все метки перечислены в их полностью в операциях 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 |

Дополнительная Информация:

1. дана таблица имя, вы можете определить все имена столбцов из sys.колонки или для XML обмана с помощью local-name ().
2. вы также можете создать список различных цветов (или значений для одного столбца), используя для XML.
3. вышеизложенное может быть объединено в динамический пакет sql для обработки любой таблицы.

Я хотел бы указать еще несколько решений для транспонирования столбцов и строк в SQL.

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

• вертикальное расширение

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

• горизонтальное расширение

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

• расстройства

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

другое решение транспонирования строк в столбцы - это использование XML.

XML-решение для транспонирования строк в столбцы в основном является оптимальной версией PIVOT в том, что оно обращается к ограничению динамического столбца.

версия XML сценарий устраняет это ограничение с помощью комбинации XML-пути, динамического T-SQL и некоторых встроенных функций (т. е. STUFF, QUOTENAME).

• вертикальное расширение

  подобно PIVOT и Курсору, недавно добавленные политики могут быть получены в XML-версии сценария без изменения исходного сценария.

• горизонтальное расширение

  В отличие от оси, недавно добавленные документы могут отображаться без изменения сценария.

• расстройства

  с точки зрения ввода – вывода статистика XML – версии скрипта почти аналогична сводной-единственное отличие заключается в том, что 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('+@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
        (
          sum(value)
          for '+@columnToPivot+' in ('+@colsPivot+')
        ) 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 ' s, запустите это:

select name, system_type_id from sys.types order by name

мне нравится делиться кодом, который я использую для транспонирования разделенного текста на основе ответа + bluefeet. В этом приближении я реализован как процедура в 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)+@Where
--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)+@Where+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