SQL Server : 컬럼에서 행으로

SQL Server : 컬럼에서 행으로

열을 행으로 변환할 수 있는 우아한(또는 임의의) 솔루션을 찾고 있습니다.

다음은 예를 제시하겠습니다.다음 스키마를 가진 테이블이 있습니다.

[ID] [EntityID] [Indicator1] [Indicator2] [Indicator3] ... [Indicator150]

그 결과 얻을 수 있는 것은 다음과 같습니다.

[ID] [EntityId] [IndicatorName] [IndicatorValue]

결과 값은 다음과 같습니다.

1 1 'Indicator1' 'Value of Indicator 1 for entity 1'
2 1 'Indicator2' 'Value of Indicator 2 for entity 1'
3 1 'Indicator3' 'Value of Indicator 3 for entity 1'
4 2 'Indicator1' 'Value of Indicator 1 for entity 2'

기타 등등..

이게 말이 되나요?T-SQL에서 검색해야 할 위치와 방법을 제안해 주시겠습니까?

UNPIVOT 함수를 사용하여 열을 행으로 변환할 수 있습니다.

select id, entityId,
  indicatorname,
  indicatorvalue
from yourtable
unpivot
(
  indicatorvalue
  for indicatorname in (Indicator1, Indicator2, Indicator3)
) unpiv;

언파트를 적용하기 전에 데이터 유형을 변환해야 할 수 있으므로 언파트를 적용할 열의 데이터 유형은 같아야 합니다.

,도할 수 .CROSS APPLYUNION ALL입니다.

select id, entityid,
  indicatorname,
  indicatorvalue
from yourtable
cross apply
(
  select 'Indicator1', Indicator1 union all
  select 'Indicator2', Indicator2 union all
  select 'Indicator3', Indicator3 union all
  select 'Indicator4', Indicator4 
) c (indicatorname, indicatorvalue);

SQL Server 버전에 따라 다음과 같은 VALUES 절과 함께 CROSS APPLY를 사용할 수도 있습니다.

select id, entityid,
  indicatorname,
  indicatorvalue
from yourtable
cross apply
(
  values
  ('Indicator1', Indicator1),
  ('Indicator2', Indicator2),
  ('Indicator3', Indicator3),
  ('Indicator4', Indicator4)
) c (indicatorname, indicatorvalue);

마지막으로 150개의 컬럼을 분리하여 전체 쿼리를 하드코드하지 않으려면 동적 SQL을 사용하여 sql 문을 생성할 수 있습니다.

DECLARE @colsUnpivot AS NVARCHAR(MAX),
   @query  AS NVARCHAR(MAX)

select @colsUnpivot 
  = stuff((select ','+quotename(C.column_name)
           from information_schema.columns as C
           where C.table_name = 'yourtable' and
                 C.column_name like 'Indicator%'
           for xml path('')), 1, 1, '')

set @query 
  = 'select id, entityId,
        indicatorname,
        indicatorvalue
     from yourtable
     unpivot
     (
        indicatorvalue
        for indicatorname in ('+ @colsunpivot +')
     ) u'

exec sp_executesql @query;

150개의 컬럼이 있다면 UNPIVOT는 선택사항이 아니라고 생각합니다.xml 트릭을 사용할 수 있습니다.

;with CTE1 as (
    select ID, EntityID, (select t.* for xml raw('row'), type) as Data
    from temp1 as t
), CTE2 as (
    select
         C.id, C.EntityID,
         F.C.value('local-name(.)', 'nvarchar(128)') as IndicatorName,
         F.C.value('.', 'nvarchar(max)') as IndicatorValue
    from CTE1 as c
        outer apply c.Data.nodes('row/@*') as F(C)
)
select * from CTE2 where IndicatorName like 'Indicator%'

sql fiddle demo

동적 SQL을 작성할 수도 있지만, 저는 xml을 더 좋아합니다. 동적 SQL의 경우 테이블에서 직접 데이터를 선택할 수 있는 권한이 있어야 하며, 이것이 항상 가능한 것은 아닙니다.

댓글에 큰 불똥이 튀고 있기 때문에 xml/dynamic SQL의 장단점을 추가하려고 합니다.저는 우아함과 추함은 언급하지 않고 최대한 객관적으로 하려고 노력하겠습니다..

단점

• 동적 SQL만큼 빠르지는 않지만 대략적인 테스트 결과 xml이 동적보다 약 2.5배 느리다는 것을 알 수 있었습니다(최대 250000행 테이블에 대한 쿼리 1개였으므로 이 추정치는 정확하지 않습니다).원하는 경우 직접 비교할 수 있습니다. 예를 들어, 100,000 행의 경우 29s(xml) 대 14s(동적)입니다.
• xpath에 익숙하지 않은 사람은 이해하기 어려울 수 있습니다.

프로

• 다른 문의와 같은 범위이므로 매우 편리할 수 있습니다.몇 가지 예가 생각난다.
  • 할 수 .inserted ★★★★★★★★★★★★★★★★★」deleted트리거 내부의 테이블(동적 방식으로는 전혀 가능하지 않음);
  • 사용자는 테이블에서 직접 선택할 수 있는 권한이 없습니다.즉, 스토어드 프로시저 계층이 있고 사용자에게 SP를 실행할 수 있는 권한은 있지만 테이블을 직접 쿼리할 수 있는 권한은 없는 경우에도 스토어드 프로시저 내에서 이 쿼리를 사용할 수 있습니다.
  • 스코프에 입력한 테이블 변수를 쿼리할 수 있습니다(다이나믹 SQL 내에 전달하려면 대신 임시 테이블을 만들거나 유형을 생성하여 파라미터로 다이내믹 SQL에 전달해야 합니다).
• 이 쿼리는 함수 내에서 실행할 수 있습니다(표준값 또는 테이블 값).함수 내에서 동적 SQL을 사용할 수 없습니다.

새로운 독자를 돕기 위해 UNPIVOT에 대한 @bluefeet의 답변을 더 잘 이해할 수 있는 예를 만들었습니다.

 SELECT id
        ,entityId
        ,indicatorname
        ,indicatorvalue
  FROM (VALUES
        (1, 1, 'Value of Indicator 1 for entity 1', 'Value of Indicator 2 for entity 1', 'Value of Indicator 3 for entity 1'),
        (2, 1, 'Value of Indicator 1 for entity 2', 'Value of Indicator 2 for entity 2', 'Value of Indicator 3 for entity 2'),
        (3, 1, 'Value of Indicator 1 for entity 3', 'Value of Indicator 2 for entity 3', 'Value of Indicator 3 for entity 3'),
        (4, 2, 'Value of Indicator 1 for entity 4', 'Value of Indicator 2 for entity 4', 'Value of Indicator 3 for entity 4')
       ) AS Category(ID, EntityId, Indicator1, Indicator2, Indicator3)
UNPIVOT
(
    indicatorvalue
    FOR indicatorname IN (Indicator1, Indicator2, Indicator3)
) UNPIV;

단지 내가 그것을 언급하지 않았기 때문에.

2016+의 경우 실제로 Dynamic SQL을 사용하지 않고도 데이터를 동적으로 추출할 수 있는 또 다른 옵션이 있습니다.

예

Declare @YourTable Table ([ID] varchar(50),[Col1] varchar(50),[Col2] varchar(50))
Insert Into @YourTable Values 
 (1,'A','B')
,(2,'R','C')
,(3,'X','D')

Select A.[ID]
      ,Item  = B.[Key]
      ,Value = B.[Value]
 From  @YourTable A
 Cross Apply ( Select * 
                From  OpenJson((Select A.* For JSON Path,Without_Array_Wrapper )) 
                Where [Key] not in ('ID','Other','Columns','ToExclude')
             ) B

돌아온다

ID  Item    Value
1   Col1    A
1   Col2    B
2   Col1    R
2   Col2    C
3   Col1    X
3   Col2    D

Microsoft SQL Server에서 컬럼 이름(트리거에 사용됨)도 모르고 동적 SQL(트리거에 사용됨)도 없이 열을 행으로 변환하는 솔루션이 필요했습니다(동적 SQL은 트리거에 사용하기에 너무 느림).

드디어 이 솔루션을 찾았습니다.이 솔루션은 다음과 같습니다.

SELECT
    insRowTbl.PK,
    insRowTbl.Username,
    attr.insRow.value('local-name(.)', 'nvarchar(128)') as FieldName,
    attr.insRow.value('.', 'nvarchar(max)') as FieldValue 
FROM ( Select      
          i.ID as PK,
          i.LastModifiedBy as Username,
          convert(xml, (select i.* for xml raw)) as insRowCol
       FROM inserted as i
     ) as insRowTbl
CROSS APPLY insRowTbl.insRowCol.nodes('/row/@*') as attr(insRow)

보시다시피 행을 XML로 변환합니다(xml raw의 경우 서브쿼리가 i,*를 선택하고 모든 열을 하나의 xml 열로 변환합니다).

그런 다음 이 열의 각 XML 속성에 함수를 크로스 적용하면 속성당 하나의 행을 얻을 수 있습니다.

전체적으로 열 이름을 모르거나 동적 SQL을 사용하지 않고 열을 행으로 변환합니다.그것은 내 목적에 충분히 빠르다.

(편집: 방금 위에서 로만 페카르가 같은 일을 하고 있는 것을 보았습니다.먼저 커서와 함께 다이내믹 SQL 트리거를 사용했는데, 이 솔루션보다 속도가 10배에서 100배 느리지만 다이내믹 SQL이 아닌 커서로 인해 발생한 것일 수 있습니다.어쨌든, 이 솔루션은 매우 심플한 범용 솔루션이기 때문에, 확실히 옵션입니다).

여기에 코멘트를 남깁니다.이것은, 제 투고에서, 완전한 감사 트리거에 관한 설명을 참조하고 싶기 때문입니다.https://stackoverflow.com/a/43800286/4160788 에서 찾을 수 있습니다.

DECLARE @TableName varchar(max)=NULL
SELECT @TableName=COALESCE(@TableName+',','')+t.TABLE_CATALOG+'.'+ t.TABLE_SCHEMA+'.'+o.Name
  FROM sysindexes AS i
  INNER JOIN sysobjects AS o ON i.id = o.id
  INNER JOIN INFORMATION_SCHEMA.TABLES T ON T.TABLE_NAME=o.name
 WHERE i.indid < 2
  AND OBJECTPROPERTY(o.id,'IsMSShipped') = 0
  AND i.rowcnt >350
  AND o.xtype !='TF'
 ORDER BY o.name ASC

 print @tablename

rowcounts가 350 이상인 테이블 목록을 얻을 수 있습니다.테이블의 솔루션목록에는 행으로 표시됩니다.

이와 반대되는 것은 열을 csv로 평탄하게 만드는 것입니다.

STRING_AGG([value], , , )FROM STRING_SPLIT('아키오, 히라쿠, 카즈오', ' , )

언급URL : https://stackoverflow.com/questions/18026236/sql-server-columns-to-rows