SQL 서버 : 컬럼 트랜스 행

그렇다 나는 열에 각 행을 바꾸어해야하는 경우, 다른 대안을 커서를 작성하는 각 행을 읽고 열로 채우기에서?

TimeSeconds TagID Value
1378700244  A1    3.75
1378700245  A1    30
1378700304  A1    1.2
1378700305  A2    56
1378700344  A2    11
1378700345  A3    0.53
1378700364  A1    4
1378700365  A1    14.5
1378700384  A1    144
1378700384  A4    10

열 수는 고정되어 있지 않습니다.

출력 : 난 그냥 / n를 할당하는 교차로에서 어떤 데이터를위한 자리로.

TimeSec     A1    A2    A3    A4
1378700244  3.75  n/a   n/a   n/a
1378700245  30    n/a   n/a   n/a
1378700304  1.2   n/a   n/a   n/a
1378700305  n/a   56    n/a   n/a
1378700344  n/a   11    n/a   n/a
1378700345  n/a   n/a   0.53  n/a
1378700364  n/a   n/a   n/a   4
1378700365  14.5  n/a   n/a   n/a
1378700384  144   n/a   n/a   10

당신이 나와 함께 몇 가지 팁을 공유 할 수 있기를 바랍니다. 감사.

해결법

==============================

1.한 가지 방법은 그것을 할 tagID 값은 선행 조건부 집계를 사용하는 것입니다 알려진 경우

한 가지 방법은 그것을 할 tagID 값은 선행 조건부 집계를 사용하는 것입니다 알려진 경우

SELECT TimeSeconds,
       COALESCE(MAX(CASE WHEN TagID = 'A1' THEN Value END), 'n/a') A1,
       COALESCE(MAX(CASE WHEN TagID = 'A2' THEN Value END), 'n/a') A2,
       COALESCE(MAX(CASE WHEN TagID = 'A3' THEN Value END), 'n/a') A3,
       COALESCE(MAX(CASE WHEN TagID = 'A4' THEN Value END), 'n/a') A4
  FROM table1
 GROUP BY TimeSeconds

또는 경우 대신 NULL 값이 정말 괜찮아요 'N / A'

SELECT TimeSeconds,
       MAX(CASE WHEN TagID = 'A1' THEN Value END) A1,
       MAX(CASE WHEN TagID = 'A2' THEN Value END) A2,
       MAX(CASE WHEN TagID = 'A3' THEN Value END) A3,
       MAX(CASE WHEN TagID = 'A4' THEN Value END) A4
  FROM table1
 GROUP BY TimeSeconds

또는 PIVOT과

SELECT TimeSeconds, A1, A2, A3, A4
  FROM
(
  SELECT TimeSeconds, TagID, Value
    FROM table1
) s
PIVOT
(
  MAX(Value) FOR TagID IN (A1, A2, A3, A4)
) p

(null로) 출력 :

TimeSeconds A1      A2     A3    A4
----------- ------- ------ ----- -----
1378700244  3.75    NULL   NULL  NULL
1378700245  30.00   NULL   NULL  NULL
1378700304  1.20    NULL   NULL  NULL
1378700305  NULL    56.00  NULL  NULL
1378700344  NULL    11.00  NULL  NULL
1378700345  NULL    NULL   0.53  NULL
1378700364  4.00    NULL   NULL  NULL
1378700365  14.50   NULL   NULL  NULL
1378700384  144.00  NULL   NULL  10.00

당신이 그림에있는 경우 TagID 동적, 동적 SQL을 사용하여 제한 값

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX)

SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(TagID)
            FROM Table1
            ORDER BY 1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),1,1,'')

SET @sql = 'SELECT TimeSeconds, ' + @cols + '
              FROM
            (
              SELECT TimeSeconds, TagID, Value
                FROM table1
            ) s
            PIVOT
            (
              MAX(Value) FOR TagID IN (' + @cols + ')
            ) p'

EXECUTE(@sql)

==============================

2.SQL 서버는 당신이 찾고있는 무엇을 할 수있는 PIVOT 명령이 있습니다.

SQL 서버는 당신이 찾고있는 무엇을 할 수있는 PIVOT 명령이 있습니다.

select * from Tag
pivot (MAX(Value) for TagID in ([A1],[A2],[A3],[A4])) as TagTime;

열이 일정하지 않은 경우, 당신은 몇 가지 동적 SQL과이 결합해야합니다.

DECLARE @columns AS VARCHAR(MAX);
DECLARE @sql AS VARCHAR(MAX);

select @columns = substring((Select DISTINCT ',' + QUOTENAME(TagID) FROM Tag FOR XML PATH ('')),2, 1000);

SELECT @sql =

'SELECT *
FROM TAG
PIVOT 
(
  MAX(Value) 
  FOR TagID IN( ' + @columns + ' )) as TagTime;';

 execute(@sql);

==============================
3.이 상황에서 적합 할 수있는 또 다른 옵션은 XML을 사용하고

이 상황에서 적합 할 수있는 또 다른 옵션은 XML을 사용하고

행을 열로 전치에 XML 옵션은 기본적으로는 동적 열 한계를 해결하는 것을 피봇의 최적 버전이다.

스크립트 어드레스의 XML 버전 XML 경로의 조합을 사용하여이 제한 동적 T-SQL 일부 내장 함수 (즉 STUFF, QUOTENAME).

수직 확장

피벗 및 커서와 마찬가지로, 새로 추가 된 정책은 원래의 스크립트를 변경하지 않고 스크립트의 XML 버전을 검색 할 수 있습니다.

수평 확장

피벗과는 달리, 새로 추가 된 문서는 스크립트를 변경하지 않고 표시 할 수 있습니다.

성능 분석

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

이 문서에서 (일부 실제 T-SQL의 exmaples 포함)이 솔루션에 대한 좀 더 찾을 수 있습니다 : https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/

==============================

4.bluefeet로부터 솔루션을 기반으로 여기 전치 테이블을 생성하기 위해 동적 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>
--        table and FIeldToTranspose should be written using single quotes
-- =============================================
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

==============================

5.나는 선택적으로 열을 행으로 바꾸어했다 이에 약간 다른 요구 사항을했다.

나는 선택적으로 열을 행으로 바꾸어했다 이에 약간 다른 요구 사항을했다.

테이블은 열을했다 :

create table tbl (ID, PreviousX, PreviousY, CurrentX, CurrentY)

내가 정적 테이블에서 발생하는 X와 Y 카티 제품에 대해 이전과 현재에 대한 열 및 행을 필요로 멋지게 일, 예를 들면 :

select 
    ID,
    max(case when metric='X' then PreviousX
        case when metric='Y' then PreviousY end) as Previous,
    max(case when metric='X' then CurrentX
        case when metric='Y' then CurrentY end) as Current
from tbl inner join
    /* Cartesian product - transpose by repeating row and 
    picking appropriate metric column for period */
    ( VALUES (1, 'X'), (2, 'Y')) AS x (sort, metric) ON 1=1
group by ID
order by ID, sort

from https://stackoverflow.com/questions/20111418/sql-server-transpose-rows-to-columns by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 왜 널은 널 false로 동일하지 않다 (0)	2020.04.04
[SQL] 열에 피벗 행 집계하지 않고 (0)	2020.04.04
[SQL] 데이터베이스 ID 필드의 고유 식별자 대 INT (0)	2020.04.04
[SQL] NOT이있는 대 LEFT OUTER에 SQL 성능 가입 (0)	2020.04.04
[SQL] 어떻게 SQL 변수에 간부 인 결과를 할당? (0)	2020.04.04

복붙노트

[SQL] SQL 서버 : 컬럼 트랜스 행

SQL 서버 : 컬럼 트랜스 행

해결법

1.한 가지 방법은 그것을 할 tagID 값은 선행 조건부 집계를 사용하는 것입니다 알려진 경우

2.SQL 서버는 당신이 찾고있는 무엇을 할 수있는 PIVOT 명령이 있습니다.

3.이 상황에서 적합 할 수있는 또 다른 옵션은 XML을 사용하고

4.bluefeet로부터 솔루션을 기반으로 여기 전치 테이블을 생성하기 위해 동적 SQL을 사용하여 저장된 프로 시저이다. 그것은 모든 필드가 전치 열 (결과 테이블의 헤더가 될 것이다 열)을 제외한 수치입니다해야합니다 :

5.나는 선택적으로 열을 행으로 바꾸어했다 이에 약간 다른 요구 사항을했다.

'SQL' 카테고리의 다른 글

티스토리툴바