SQL 서버 : 행에 열

행으로 변환 열 우아한 (또는) 솔루션을 찾고있다.

다음은 예입니다 : 나는 다음과 같은 스키마와 테이블이 있습니다 :

[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에서 끝내야 모양과 위치에 대한 제안 사항이 있습니까?

해결법

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

1.당신은 행으로 열을 변환 할 UNPIVOT 기능을 사용할 수 있습니다 :

당신은 행으로 열을 변환 할 UNPIVOT 기능을 사용할 수 있습니다 :

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

당신이 UNPIVOT을 적용하기 전에 데이터 유형을 변환해야 할 수도 있으므로 참고하면 unpivoting하는 컬럼의 데이터 유형은 동일해야합니다.

또한 열을 변환 CROSS는 UNION 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을 사용할 수 있습니다 :

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

당신이 UNPIVOT 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;

==============================
2.당신이 150 열이있는 경우 그럼 내가 UNPIVOT은 옵션이 아니다라고 생각합니다. 당신은 XML의 트릭을 사용할 수 있도록

당신이 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 바이올린 데모

당신은 쓰기 동적 SQL도 할 수 있지만, 나는 XML처럼 더 - 동적 SQL에 대한 당신은 테이블에서 데이터를 직접 선택 할 수있는 권한이 있어야하고 항상 옵션이 아니다.

UPDATEAs이 의견에 큰 불꽃, 나는 XML / 동적 SQL의 몇 가지 장점과 단점을 추가 할 것입니다 생각합니다. 나는 내가 할 수있는하지 elegantness 및 uglyness 언급으로 목표로 일하려고합니다. 당신은 다른 장점과 단점, 편집, 주석에서 답 또는 쓰기를 가지고있는 경우

단점

프로

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

3.더 나은 @ bluefeet의 UNPIVOT에 대한 대답을 이해에 그냥 도움 새로운 독자들에게, 나는 예를 만들었습니다.

더 나은 @ bluefeet의 UNPIVOT에 대한 대답을 이해에 그냥 도움 새로운 독자들에게, 나는 예를 만들었습니다.

 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;

==============================
4.나는 콜 럼 이름을 알지 못하고 (트리거에 사용) 및 (동적 SQL 트리거에 사용하기 위해 너무 느립니다) 동적 SQL없이, 마이크로 소프트 SQL 서버의 행을 변환 컬럼에 대한 해결책이 필요했다.

나는 콜 럼 이름을 알지 못하고 (트리거에 사용) 및 (동적 SQL 트리거에 사용하기 위해 너무 느립니다) 동적 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을 원시에 대한 * 하위 쿼리, 난을 선택, 하나의 XML 컬럼에이 변환 모든 열)

그럼 CROSS 내가 속성 당 하나 개의 행을 얻을 그래서,이 열의 각 XML 속성에 함수를 적용합니다.

전반적으로, 컬럼 이름을 모르면 동적 SQL을 사용하지 않고 열로 변환하고,이 열. 그것은 내 목적을 위해 빠른 충분하다.

(편집 : 난 그냥 같은 일을한다 위의 로마 Pekar 답변을 보았다. I이 용액의 10 내지 100 배 더 느렸다 제 커서와 동적 SQL 트리거를 사용하지만, 아마도이 아니라 동적 SQL이 커서에 의해 야기되었다. ) 어쨌든,이 솔루션은 보편적 매우 간단합니다, 그래서 그 결정적 옵션.

나는 전체 감사 트리거에 대한 내 게시물이 설명을 참조 할 때문에 내가 여기 당신이 찾을 수 있습니다,이 장소에서이 댓글을 떠납니다 : https://stackoverflow.com/a/43800286/4160788

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

5.

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

당신은 350> rowcounts를 가지고 테이블의 목록을 얻을 수 있습니다. 당신은 행으로 테이블의 솔루션 목록을 볼 수 있습니다.

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

6.나는 보지 않았다해서 그것을 언급했다.

나는 보지 않았다해서 그것을 언급했다.

2016+, 여기 실제로 동적 SQL을 사용하지 않고 동적으로 UNPIVOT 데이터에 대한 또 다른 옵션은 아직입니다.

예

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

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

'SQL' 카테고리의 다른 글

[SQL] 어떻게 쿼리 PostgreSQL의 '그룹화'의 문자열 필드의 CONCATENATE 문자열에? (0)	2020.03.10
[SQL] 최대 절전 모드는 실제 SQL을 보여 [중복] (0)	2020.03.10
[SQL] 무엇 T-SQL 문에서 접두사 N의 의미는 무엇이고 언제 사용해야합니까? (0)	2020.03.09
[SQL] 어떻게 SQL 서버에서 중복 행을 삭제하려면? (0)	2020.03.09
[SQL] 어떻게 Access에서 서로 다른 상황에서 VBA에서 매개 변수를 사용합니까? (0)	2020.03.09

복붙노트

[SQL] SQL 서버 : 행에 열

SQL 서버 : 행에 열

해결법

1.당신은 행으로 열을 변환 할 UNPIVOT 기능을 사용할 수 있습니다 :

2.당신이 150 열이있는 경우 그럼 내가 UNPIVOT은 옵션이 아니다라고 생각합니다. 당신은 XML의 트릭을 사용할 수 있도록

3.더 나은 @ bluefeet의 UNPIVOT에 대한 대답을 이해에 그냥 도움 새로운 독자들에게, 나는 예를 만들었습니다.

4.나는 콜 럼 이름을 알지 못하고 (트리거에 사용) 및 (동적 SQL 트리거에 사용하기 위해 너무 느립니다) 동적 SQL없이, 마이크로 소프트 SQL 서버의 행을 변환 컬럼에 대한 해결책이 필요했다.

5.

6.나는 보지 않았다해서 그것을 언급했다.

'SQL' 카테고리의 다른 글

티스토리툴바