키 - 값 쌍 테이블에 1 행 테이블 평탄화

행의 열 값을 나타내는 키 - 값 쌍의 결과 집합을 얻을 수있는 가장 좋은 방법은 무엇입니까?

단 1 행과 다음 표 A를 감안할 때


Column1 Column2 Column3 ...
Value1  Value2  Value3

나는 그것을 쿼리와 다른 테이블 B에 삽입 할 :


Key                  Value
Column1              Value1
Column2              Value2
Column3              Value3

테이블 A의 열 세트는 사전에 알려져 있지 않다.

참고 : 나는 XML에 대한보고 있었다 및 PIVOT은 다음과 같이 뭔가를 할 수있는 동적 SQL뿐만 아니라 기능 :


    DECLARE @sql nvarchar(max)
    SET @sql = (SELECT STUFF((SELECT ',' + column_name 
                              FROM INFORMATION_SCHEMA.COLUMNS 
                              WHERE table_name='TableA' 
                              ORDER BY column_name FOR XML PATH('')), 1, 1, ''))
    SET @sql = 'SELECT ' + @sql + ' FROM TableA'
    EXEC(@sql)

해결법

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

1.반군 동적가없는 버전. 당신이 열 이름이있는 경우이 실패합니다 XML에서 요소 이름으로 사용하는 것이 유효하다.

반군 동적가없는 버전. 당신이 열 이름이있는 경우이 실패합니다 XML에서 요소 이름으로 사용하는 것이 유효하다.

select T2.N.value('local-name(.)', 'nvarchar(128)') as [Key],
       T2.N.value('text()[1]', 'nvarchar(max)') as Value
from (select *
      from TableA
      for xml path(''), type) as T1(X)
  cross apply T1.X.nodes('/*') as T2(N)

실제로 동작하는 샘플 :

declare @T table
(
  Column1 varchar(10), 
  Column2 varchar(10), 
  Column3 varchar(10)
)

insert into @T values('V1','V2','V3')

select T2.N.value('local-name(.)', 'nvarchar(128)') as [Key],
       T2.N.value('text()[1]', 'nvarchar(max)') as Value
from (select *
      from @T
      for xml path(''), type) as T1(X)
  cross apply T1.X.nodes('/*') as T2(N)

결과:

Key                  Value
-------------------- -----
Column1              V1
Column2              V2
Column3              V3

최신 정보

하나 개 이상의 테이블 쿼리를 들어 당신은 XML에 테이블 이름을 얻기 위해 XML을 자동으로 사용할 수 있습니다. 쿼리에서 테이블 이름에 별칭을 사용하는 경우 참고 별칭을 대신 받게됩니다.

select X2.N.value('local-name(..)', 'nvarchar(128)') as TableName,
       X2.N.value('local-name(.)', 'nvarchar(128)') as [Key],
       X2.N.value('text()[1]', 'nvarchar(max)') as Value
from (
     -- Your query starts here
     select T1.T1ID,
            T1.T1Col,
            T2.T2ID,
            T2.T2Col
     from T1
       inner join T2
         on T1.T1ID = T2.T1ID
     -- Your query ends here
     for xml auto, elements, type     
     ) as X1(X)
  cross apply X1.X.nodes('//*[text()]') as X2(N)

SQL 바이올린

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

2.난 당신이 중간에있을 것 같아요. 마틴 권장 그냥 UNPIVOT 및 동적 SQL을 사용합니다 :

난 당신이 중간에있을 것 같아요. 마틴 권장 그냥 UNPIVOT 및 동적 SQL을 사용합니다 :

CREATE TABLE TableA (
  Code VARCHAR(10),
  Name VARCHAR(10),
  Details VARCHAR(10)
) 

INSERT TableA VALUES ('Foo', 'Bar', 'Baz') 
GO

DECLARE @sql nvarchar(max)
SET @sql = (SELECT STUFF((SELECT ',' + column_name 
                          FROM INFORMATION_SCHEMA.COLUMNS 
                          WHERE table_name='TableA' 
                          ORDER BY ordinal_position FOR XML PATH('')), 1, 1, ''))

SET @sql = N'SELECT [Key], Val FROM (SELECT ' + @sql + ' FROM TableA) x '
+ 'UNPIVOT ( Val FOR [Key] IN (' + @sql + ')) AS unpiv'
EXEC (@sql)

결과 :

Key          Val
------------ ------------
Code         Foo
Name         Bar
Details      Baz

유의할 점은 물론있다. 귀하의 모든 열은 일에 위의 코드에 대해 동일한 데이터 유형이어야해야합니다. 그렇지 않은 경우,이 오류가 발생합니다 :

Msg 8167, Level 16, State 1, Line 1
The type of column "Col" conflicts with the type of 
other columns specified in the UNPIVOT list.

이 주위에 취득하기 위하여는, 당신은 두 개의 열 문자열 문을 만들어야합니다. 하나는 열을 얻으려면 하나는 당신의 발 컬럼에 대한 데이터 유형으로 모두를 캐스팅.

여러 열 유형의 경우 :

CREATE TABLE TableA (
  Code INT,
  Name VARCHAR(10),
  Details VARCHAR(10)
) 

INSERT TableA VALUES (1, 'Foo', 'Baf') 
GO

DECLARE 
  @sql nvarchar(max),
  @cols nvarchar(max),
  @conv nvarchar(max) 

SET @cols = (SELECT STUFF((SELECT ',' + column_name 
                          FROM INFORMATION_SCHEMA.COLUMNS 
                          WHERE table_name='TableA' 
                          ORDER BY ordinal_position FOR XML PATH('')), 1, 1, ''))

SET @conv = (SELECT STUFF((SELECT ', CONVERT(VARCHAR(50), ' 
                          + column_name + ') AS ' + column_name
                          FROM INFORMATION_SCHEMA.COLUMNS 
                          WHERE table_name='TableA' 
                          ORDER BY ordinal_position FOR XML PATH('')), 1, 1, ''))


SET @sql = N'SELECT [Key], Val FROM (SELECT ' + @conv + ' FROM TableA) x '
+ 'UNPIVOT ( Val FOR [Key] IN (' + @cols + ')) AS unpiv'
EXEC (@sql)

==============================
3.아마 당신은 필요 이상으로이 더욱 복잡하게 만들고있어. 내가 PIVOT / UNPIVOT / 어떤 조합의 수와 동적 SQL "붉은 바다"주위에 내 작은 머리를 감싸 없습니다 부분적으로 있기 때문에이 떨어져 당겨해야 할 것입니다. 당신이 알고 있기 때문에 테이블은 UNION을 쿼리의 집합의 부분으로 하위 쿼리 될 수있는 각 열에 대한 값을 당기는 정확히 하나의 행이 있습니다.

아마 당신은 필요 이상으로이 더욱 복잡하게 만들고있어. 내가 PIVOT / UNPIVOT / 어떤 조합의 수와 동적 SQL "붉은 바다"주위에 내 작은 머리를 감싸 없습니다 부분적으로 있기 때문에이 떨어져 당겨해야 할 것입니다. 당신이 알고 있기 때문에 테이블은 UNION을 쿼리의 집합의 부분으로 하위 쿼리 될 수있는 각 열에 대한 값을 당기는 정확히 하나의 행이 있습니다.
```
DECLARE @sql NVARCHAR(MAX) = N'INSERT dbo.B([Key], Value) '

SELECT @sql += CHAR(13) + CHAR(10) 
        + ' SELECT [Key] = ''' + REPLACE(name, '''', '''''') + ''', 
        Value = (SELECT ' + QUOTENAME(name) + ' FROM dbo.A) UNION ALL'
FROM sys.columns 
WHERE [object_id] = OBJECT_ID('dbo.A');

SET @sql = LEFT(@sql, LEN(@sql)-9) + ';';

PRINT @sql;
-- EXEC sp_executesql @sql;
```
(I는 4 열을 생성하지만,이 숫자 위해 일 것이다) 결과 :
```
INSERT dbo.B([Key], Value)
 SELECT [Key] = 'Column1', 
        Value = (SELECT [Column1] FROM dbo.A) UNION ALL
 SELECT [Key] = 'Column2', 
        Value = (SELECT [Column2] FROM dbo.A) UNION ALL
 SELECT [Key] = 'Column3', 
        Value = (SELECT [Column3] FROM dbo.A) UNION ALL
 SELECT [Key] = 'Column4', 
        Value = (SELECT [Column4] FROM dbo.A);
```
세계에서 가장 효율적인 것은? 가능성이 없습니다. 그러나 다시, 희망 한 행 테이블과의 일회성 작업, 나는 그것이 잘 작동합니다 생각합니다. 그냥 당신이 당신의 가게에서 그 일을 허용하는 경우, 아포스트로피를 포함하는 열 이름에 대한 조심 ...

편집 미안, 그런 식으로 떠날 수 없었다. 지금은 열 이름과 다른 차선의 명명 선택에 아포스트로피를 처리합니다.

from https://stackoverflow.com/questions/7341143/flattening-of-a-1-row-table-into-a-key-value-pair-table by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] MySQL은 0과 널 (null)을 교체 (0)	2020.06.06
[SQL] 날짜에서 MySQL의 스트립 시간 성분 (0)	2020.06.06
[SQL] 어떻게 SQL에서 결과의 페이지를 반환? (0)	2020.06.06
[SQL] SQL 데이터베이스를 설계하는 것은 OO 클래스 계층 구조를 나타내는 (0)	2020.06.06
[SQL] 다른 테이블의 열이 업데이트 될 때 하나 개의 테이블에 열을 업데이트하는 트리거를 생성 (0)	2020.06.06

복붙노트

[SQL] 키 - 값 쌍 테이블에 1 행 테이블 평탄화

키 - 값 쌍 테이블에 1 행 테이블 평탄화

해결법

1.반군 동적가없는 버전. 당신이 열 이름이있는 경우이 실패합니다 XML에서 요소 이름으로 사용하는 것이 유효하다.

2.난 당신이 중간에있을 것 같아요. 마틴 권장 그냥 UNPIVOT 및 동적 SQL을 사용합니다 :

'SQL' 카테고리의 다른 글

티스토리툴바