[sql] SQL에서 열과 행을 바꾸는 간단한 방법?

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에서이 시나리오에는 너무 복잡해 보이지만 SQL Server 의 UNPIVOT및PIVOT 함수 를 모두 사용하여 매우 쉽게 적용 할 수 있다고 언급했습니다 .

그러나이 사용하고 복제 할 수있는 그 기능에 액세스 할 수없는 경우 UNION ALL에 UNPIVOTA를 집계 함수 다음과 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);

Union All, Aggregate 및 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 보기

UNION ALL행하는 UNPIVOT열을 변환하여 상기 데이터의 Paul, John, Tim, Eric개별 행에. 그럼 당신은 집계 함수를 적용 sum()과 case각각에 대한 새 열을 얻을 문 color.

Unpivot 및 Pivot 정적 버전 :

SQL 서버 의 UNPIVOT및 PIVOT함수는이 변환을 훨씬 쉽게 만듭니다. 변환하려는 모든 값을 알고있는 경우 해당 값을 정적 버전으로 하드 코딩하여 결과를 얻을 수 있습니다.

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 보기

이있는 내부 쿼리 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, '+@colsPivot+'
      from
      (
        select color, name, value
        from yourtable
        unpivot
        (
          value for name in ('+@colsUnpivot+')
        ) unpiv
      ) src
      pivot
      (
        sum(value)
        for color in ('+@colsPivot+')
      ) piv'

exec(@query)

데모로 SQL Fiddle 보기

동적 버전 모두를 쿼리 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 |

추가 사항 :

테이블 이름이 주어지면 local-name ()을 사용하여 sys.columns 또는 FOR XML 속임수 에서 모든 열 이름을 확인할 수 있습니다 .
FOR XML을 사용하여 고유 한 색상 (또는 한 열의 값) 목록을 작성할 수도 있습니다.
위의 내용을 동적 SQL 일괄 처리로 결합하여 모든 테이블을 처리 할 수 있습니다.

답변

SQL에서 열과 행을 전치하는 몇 가지 솔루션을 더 지적하고 싶습니다.

첫 번째는 CURSOR를 사용하는 것입니다. 전문 커뮤니티의 일반적인 합의는 SQL Server 커서를 멀리하는 것이지만 여전히 커서 사용이 권장되는 경우가 있습니다. 어쨌든 커서는 행을 열로 바꾸는 또 다른 옵션을 제공합니다.

수직 확장

PIVOT과 마찬가지로 커서에는 더 많은 정책 번호를 포함하도록 데이터 세트가 확장 될 때 더 많은 행을 추가 할 수있는 동적 기능이 있습니다.
수평 확장

PIVOT과 달리 커서는 스크립트를 변경하지 않고 새로 추가 된 문서를 포함하도록 확장 할 수있어이 영역에서 탁월합니다.
성능 분석

CURSOR를 사용하여 행을 열로 전치하는 주요 제한은 일반적으로 커서를 사용하는 것과 관련된 단점입니다. 성능이 크게 저하됩니다. 이는 Cursor가 각 FETCH NEXT 작업에 대해 별도의 쿼리를 생성하기 때문입니다.

행을 열로 전치하는 또 다른 솔루션은 XML을 사용하는 것입니다.

행을 열로 전치하는 XML 솔루션은 기본적으로 동적 열 제한을 해결한다는 점에서 PIVOT의 최적 버전입니다.

스크립트의 XML 버전은 XML 경로, 동적 T-SQL 및 일부 내장 함수 (예 : STUFF, QUOTENAME)의 조합을 사용하여 이러한 제한을 해결합니다.

수직 확장

PIVOT 및 Cursor와 유사하게 새로 추가 된 정책은 원래 스크립트를 변경하지 않고도 스크립트의 XML 버전에서 검색 할 수 있습니다.
수평 확장

PIVOT과 달리 새로 추가 된 문서는 스크립트 변경없이 표시 할 수 있습니다.
성능 분석

IO 측면에서 스크립트의 XML 버전 통계는 PIVOT과 거의 유사합니다. 유일한 차이점은 XML에 dtTranspose 테이블의 두 번째 스캔이 있지만 이번에는 논리적 읽기 데이터 캐시에서 가져온 것입니다.

이 문서 ( https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/ )에서 이러한 솔루션 (실제 T-SQL 예제 포함)에 대해 자세히 알아볼 수 있습니다.

답변

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 in 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