SQL 서버 동적 PIVOT 쿼리?

나는 다음과 같은 데이터를 변환하는 수단을 세우는 임무를했습니다 :

date        category        amount
1/1/2012    ABC             1000.00
2/1/2012    DEF             500.00
2/1/2012    GHI             800.00
2/10/2012   DEF             700.00
3/1/2012    ABC             1100.00

다음에 :

date        ABC             DEF             GHI
1/1/2012    1000.00
2/1/2012                    500.00
2/1/2012                                    800.00
2/10/2012                   700.00
3/1/2012    1100.00

공백 명소 중 하나 괜찮습니다, 널 (NULL) 또는 공백이 될 수 있으며, 카테고리는 동적해야합니다. 이에 대한 또 다른 가능한주의해야 할 점은 우리가 임시 테이블 밖으로 의미 제한된 용량에서 쿼리를 실행 할 것이라는 점이다. 나는 연구를 시도하고 PIVOT에 착륙했지만 난 내 최선의 노력에도 불구하고 정말 그것을 이해하지 못하는하기 전에 해당 사용한 적이로 알아낼 수 있습니다. 올바른 방향으로 누구 포인트 나 수 있습니까?

해결법

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

1.동적 SQL PIVOT :

동적 SQL PIVOT :

create table temp
(
    date datetime,
    category varchar(3),
    amount money
)

insert into temp values ('1/1/2012', 'ABC', 1000.00)
insert into temp values ('2/1/2012', 'DEF', 500.00)
insert into temp values ('2/1/2012', 'GHI', 800.00)
insert into temp values ('2/10/2012', 'DEF', 700.00)
insert into temp values ('3/1/2012', 'ABC', 1100.00)


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category) 
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT date, ' + @cols + ' from 
            (
                select date
                    , amount
                    , category
                from temp
           ) x
            pivot 
            (
                 max(amount)
                for category in (' + @cols + ')
            ) p '


execute(@query)

drop table temp

결과 :

Date                        ABC         DEF    GHI
2012-01-01 00:00:00.000     1000.00     NULL    NULL
2012-02-01 00:00:00.000     NULL        500.00  800.00
2012-02-10 00:00:00.000     NULL        700.00  NULL
2012-03-01 00:00:00.000     1100.00     NULL    NULL

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

2.열 문자열을 만들기위한 다른 방법

열 문자열을 만들기위한 다른 방법

create table #temp
(
    date datetime,
    category varchar(3),
    amount money
)

insert into #temp values ('1/1/2012', 'ABC', 1000.00)
insert into #temp values ('2/1/2012', 'DEF', 500.00)
insert into #temp values ('2/1/2012', 'GHI', 800.00)
insert into #temp values ('2/10/2012', 'DEF', 700.00)
insert into #temp values ('3/1/2012', 'ABC', 1100.00)

DECLARE @cols  AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';

SELECT @cols = @cols + QUOTENAME(category) + ',' FROM (select distinct category from #temp ) as tmp
select @cols = substring(@cols, 0, len(@cols)) --trim "," at end

set @query = 
'SELECT * from 
(
    select date, amount, category from #temp
) src
pivot 
(
    max(amount) for category in (' + @cols + ')
) piv'

execute(@query)
drop table #temp

결과

date                    ABC     DEF     GHI
2012-01-01 00:00:00.000 1000.00 NULL    NULL
2012-02-01 00:00:00.000 NULL    500.00  800.00
2012-02-10 00:00:00.000 NULL    700.00  NULL
2012-03-01 00:00:00.000 1100.00 NULL    NULL

==============================
3.나는이 질문이 나이가 알고 있지만 나는 답변을 통해 찾고 있었고, 난이 문제 가능성이 도움이 누군가 밖으로의 "동적"부분을 확장 할 수있을 것으로 생각했다.

나는이 질문이 나이가 알고 있지만 나는 답변을 통해 찾고 있었고, 난이 문제 가능성이 도움이 누군가 밖으로의 "동적"부분을 확장 할 수있을 것으로 생각했다.

무엇보다도 나는 동료의 몇 변덕 큰 데이터 세트를 빠르게 선회 할 필요와 함께 가지고 있었던 문제를 해결하기 위해이 솔루션을 만들었습니다.

사용자의 요구가 정지 지금 읽고 만족을 위해 그 문제를 벗어나면, 그래서이 솔루션은 저장 프로 시저의 생성을 필요로한다.

이 절차는 동적으로 변화하는 테이블, 열 이름과 집계에 대한 피벗 문을 만들 피벗 문의 주요 변수에 걸릴 것입니다. 정적 열이 선회 대 / 식별 컬럼으로 그룹으로 사용된다 (이 경우 필요하지 코드 밖으로 제거하지만 피봇 문에서 매우 흔히 원래 문제를 해결하는 것이 필요하다고 할 수있다), 피벗 컬럼 곳인 로부터 생성 될 얻어진 열 이름을 종료하고, 값 열 집합을 적용 할 것입니다. 테이블 매개 변수는 스키마 (은 schema.tablename)이 깨끗하지 않기 때문에 나는 그것이 싶습니다 일부 사랑을 사용할 수있는 코드의이 부분을 포함하는 테이블의 이름입니다. 내 사용이 공개적으로 직면되지 않았기 때문에 그것은 나를 위해 일한 및 SQL 인젝션이 문제가 아니었다. 집계 매개 변수는 표준을 받아 SQL 집계 'AVG', 'SUM', 'MAX'등 MAX에 대한 코드는 기본적으로이 필요하지 않습니다하지만 원래 위해 지어진 관객 피벗을 이해하고 일반적이었다 않았다 집계로 집계로 최대를 사용하여.

저장 프로 시저를 만들 수있는 코드를 시작할 수 있습니다. 이 코드는 SSMS 2005의 모든 버전에서 이상 작동해야하지만 2005 또는 2016 년을 시험하지 않았다 그러나 나는 왜없는 것 일을 볼 수 없습니다.
```
create PROCEDURE [dbo].[USP_DYNAMIC_PIVOT]
    (
        @STATIC_COLUMN VARCHAR(255),
        @PIVOT_COLUMN VARCHAR(255),
        @VALUE_COLUMN VARCHAR(255),
        @TABLE VARCHAR(255),
        @AGGREGATE VARCHAR(20) = null
    )

AS


BEGIN

SET NOCOUNT ON;
declare @AVAIABLE_TO_PIVOT NVARCHAR(MAX),
        @SQLSTRING NVARCHAR(MAX),
        @PIVOT_SQL_STRING NVARCHAR(MAX),
        @TEMPVARCOLUMNS NVARCHAR(MAX),
        @TABLESQL NVARCHAR(MAX)

if isnull(@AGGREGATE,'') = '' 
    begin
        SET @AGGREGATE = 'MAX'
    end


 SET @PIVOT_SQL_STRING =    'SELECT top 1 STUFF((SELECT distinct '', '' + CAST(''[''+CONVERT(VARCHAR,'+ @PIVOT_COLUMN+')+'']''  AS VARCHAR(50)) [text()]
                            FROM '+@TABLE+'
                            WHERE ISNULL('+@PIVOT_COLUMN+','''') <> ''''
                            FOR XML PATH(''''), TYPE)
                            .value(''.'',''NVARCHAR(MAX)''),1,2,'' '') as PIVOT_VALUES
                            from '+@TABLE+' ma
                            ORDER BY ' + @PIVOT_COLUMN + ''

declare @TAB AS TABLE(COL NVARCHAR(MAX) )

INSERT INTO @TAB EXEC SP_EXECUTESQL  @PIVOT_SQL_STRING, @AVAIABLE_TO_PIVOT 

SET @AVAIABLE_TO_PIVOT = (SELECT * FROM @TAB)


SET @TEMPVARCOLUMNS = (SELECT replace(@AVAIABLE_TO_PIVOT,',',' nvarchar(255) null,') + ' nvarchar(255) null')


SET @SQLSTRING = 'DECLARE @RETURN_TABLE TABLE ('+@STATIC_COLUMN+' NVARCHAR(255) NULL,'+@TEMPVARCOLUMNS+')  
                    INSERT INTO @RETURN_TABLE('+@STATIC_COLUMN+','+@AVAIABLE_TO_PIVOT+')

                    select * from (
                    SELECT ' + @STATIC_COLUMN + ' , ' + @PIVOT_COLUMN + ', ' + @VALUE_COLUMN + ' FROM '+@TABLE+' ) a

                    PIVOT
                    (
                    '+@AGGREGATE+'('+@VALUE_COLUMN+')
                    FOR '+@PIVOT_COLUMN+' IN ('+@AVAIABLE_TO_PIVOT+')
                    ) piv

                    SELECT * FROM @RETURN_TABLE'



EXEC SP_EXECUTESQL @SQLSTRING

END
```
우리가 얻을 것이다 다음으로 우리의 데이터는 예를 들어 준비. 나는 집계 변화의 다양한 출력을 표시하는 개념의 증명에 사용되는 데이터 요소의 몇 가지를 추가로 허용 대답에서 데이터의 예를 촬영했다.
```
create table temp
(
    date datetime,
    category varchar(3),
    amount money
)

insert into temp values ('1/1/2012', 'ABC', 1000.00)
insert into temp values ('1/1/2012', 'ABC', 2000.00) -- added
insert into temp values ('2/1/2012', 'DEF', 500.00)
insert into temp values ('2/1/2012', 'DEF', 1500.00) -- added
insert into temp values ('2/1/2012', 'GHI', 800.00)
insert into temp values ('2/10/2012', 'DEF', 700.00)
insert into temp values ('2/10/2012', 'DEF', 800.00) -- addded
insert into temp values ('3/1/2012', 'ABC', 1100.00)
```
하기 실시 예는 단순한 예로서 다양한 응집체를 나타내는 다양한 실행 명령문을 나타낸다. 나는 예를 간단하게 유지하기 위해 정적, 피벗 및 값 열을 변경하는 선택하지 않았다. 당신은 그냥 자신을 엉망으로 시작하는 코드를 복사 및 붙여 넣기 할 수 있어야한다
```
exec [dbo].[USP_DYNAMIC_PIVOT] 'date','category','amount','dbo.temp','sum'
exec [dbo].[USP_DYNAMIC_PIVOT] 'date','category','amount','dbo.temp','max'
exec [dbo].[USP_DYNAMIC_PIVOT] 'date','category','amount','dbo.temp','avg'
exec [dbo].[USP_DYNAMIC_PIVOT] 'date','category','amount','dbo.temp','min'
```
이 실행은 각각 다음과 같은 데이터 세트를 반환합니다.

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

4.SQL 서버 2017에 대한 업데이트 버전 피벗 열 목록을 구성 STRING_AGG 기능을 사용 :

SQL 서버 2017에 대한 업데이트 버전 피벗 열 목록을 구성 STRING_AGG 기능을 사용 :

create table temp
(
    date datetime,
    category varchar(3),
    amount money
);

insert into temp values ('20120101', 'ABC', 1000.00);
insert into temp values ('20120201', 'DEF', 500.00);
insert into temp values ('20120201', 'GHI', 800.00);
insert into temp values ('20120210', 'DEF', 700.00);
insert into temp values ('20120301', 'ABC', 1100.00);


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = (SELECT STRING_AGG(category,',') FROM (SELECT DISTINCT category FROM temp WHERE category IS NOT NULL)t);

set @query = 'SELECT date, ' + @cols + ' from 
            (
                select date
                    , amount
                    , category
                from temp
           ) x
            pivot 
            (
                 max(amount)
                for category in (' + @cols + ')
            ) p ';

execute(@query);

drop table temp;

==============================
5.당신이 사용하여 동적 TSQL을 달성 할 수있다 (피하기 SQL 주입 공격에 QUOTENAME를 사용하는 기억) :

당신이 사용하여 동적 TSQL을 달성 할 수있다 (피하기 SQL 주입 공격에 QUOTENAME를 사용하는 기억) :

SQL Server 2005의 동적 열로 선회

SQL 서버 - 동적 PIVOT 표 - SQL 인젝션

저주 동적 SQL의 축복 필수 기준

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

6.불필요한 널 (null) 값을 청소 내 솔루션이있다

불필요한 널 (null) 값을 청소 내 솔루션이있다

DECLARE @cols AS NVARCHAR(MAX),
@maxcols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(CodigoFormaPago) 
                from PO_FormasPago
                order by CodigoFormaPago
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

select @maxcols = STUFF((SELECT ',MAX(' + QUOTENAME(CodigoFormaPago) + ') as ' + QUOTENAME(CodigoFormaPago)
                from PO_FormasPago
                order by CodigoFormaPago
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'')

set @query = 'SELECT CodigoProducto, DenominacionProducto, ' + @maxcols + '
            FROM
            (
                SELECT 
                CodigoProducto, DenominacionProducto,
                ' + @cols + ' from 
                 (
                    SELECT 
                        p.CodigoProducto as CodigoProducto,
                        p.DenominacionProducto as DenominacionProducto,
                        fpp.CantidadCuotas as CantidadCuotas,
                        fpp.IdFormaPago as IdFormaPago,
                        fp.CodigoFormaPago as CodigoFormaPago
                    FROM
                        PR_Producto p
                        LEFT JOIN PR_FormasPagoProducto fpp
                            ON fpp.IdProducto = p.IdProducto
                        LEFT JOIN PO_FormasPago fp
                            ON fpp.IdFormaPago = fp.IdFormaPago
                ) xp
                pivot 
                (
                    MAX(CantidadCuotas)
                    for CodigoFormaPago in (' + @cols + ')
                ) p 
            )  xx 
            GROUP BY CodigoProducto, DenominacionProducto'

t @query;

execute(@query);

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

7.아래의 코드는 출력 0으로 NULL을 대체하는 결과를 제공합니다.

아래의 코드는 출력 0으로 NULL을 대체하는 결과를 제공합니다.

표 생성과 데이터 삽입 :

create table test_table
 (
 date nvarchar(10),
 category char(3),
 amount money
 )

 insert into test_table values ('1/1/2012','ABC',1000.00)
 insert into test_table values ('2/1/2012','DEF',500.00)
 insert into test_table values ('2/1/2012','GHI',800.00)
 insert into test_table values ('2/10/2012','DEF',700.00)
 insert into test_table values ('3/1/2012','ABC',1100.00)

쿼리는 제로로 NULL을 대체 정확한 결과를 생성합니다 :

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX),
@PivotColumnNames AS NVARCHAR(MAX),
@PivotSelectColumnNames AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column
SELECT @PivotColumnNames= ISNULL(@PivotColumnNames + ',','')
+ QUOTENAME(category)
FROM (SELECT DISTINCT category FROM test_table) AS cat

--Get distinct values of the PIVOT Column with isnull
SELECT @PivotSelectColumnNames 
= ISNULL(@PivotSelectColumnNames + ',','')
+ 'ISNULL(' + QUOTENAME(category) + ', 0) AS '
+ QUOTENAME(category)
FROM (SELECT DISTINCT category FROM test_table) AS cat

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
N'SELECT date, ' + @PivotSelectColumnNames + '
FROM test_table
pivot(sum(amount) for category in (' + @PivotColumnNames + ')) as pvt';

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

OUTPUT :

from https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] MySQL의 계층 적 재귀 쿼리 만드는 방법 (0)	2020.03.04
[SQL] 는 SQL IN 절을 매개 변수화 (0)	2020.03.04
[SQL] 어떻게 SQL 서버에서 하나의 텍스트 문자열에 여러 행의 텍스트를 연결하는? (0)	2020.03.04
[SQL] 각 그룹의 마지막 레코드를 가져 - MySQL의 (0)	2020.03.04
[SQL] 각 GROUP BY 그룹에서 첫 번째 행을 선택? (0)	2020.03.04

복붙노트

[SQL] SQL 서버 동적 PIVOT 쿼리?

SQL 서버 동적 PIVOT 쿼리?

해결법

1.동적 SQL PIVOT :

2.열 문자열을 만들기위한 다른 방법

3.나는이 질문이 나이가 알고 있지만 나는 답변을 통해 찾고 있었고, 난이 문제 가능성이 도움이 누군가 밖으로의 "동적"부분을 확장 할 수있을 것으로 생각했다.

4.SQL 서버 2017에 대한 업데이트 버전 피벗 열 목록을 구성 STRING_AGG 기능을 사용 :

5.당신이 사용하여 동적 TSQL을 달성 할 수있다 (피하기 SQL 주입 공격에 QUOTENAME를 사용하는 기억) :

6.불필요한 널 (null) 값을 청소 내 솔루션이있다

7.아래의 코드는 출력 0으로 NULL을 대체하는 결과를 제공합니다.

'SQL' 카테고리의 다른 글

티스토리툴바