SQL Server 동적 피벗 쿼리?

programing

SQL Server 동적 피벗 쿼리?

mbctv 2023. 4. 6. 23:04

SQL Server 동적 피벗 쿼리?

저는 다음과 같은 데이터를 번역하는 방법을 생각해 내는 임무를 맡았습니다.

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그걸 처음 써보니까 잘 모르겠어요. 아무리 알아봐도.누가 나를 올바른 방향으로 인도해 줄 수 있나요?

동적 SQL 피벗:

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

동적 SQL 피벗

열 문자열 작성에 대한 다른 방법

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

이 질문이 더 오래된 것은 알지만, 저는 답을 찾고 있었고, 문제의 "동적인" 부분을 확장하고 누군가를 도울 수 있을지도 모른다고 생각했습니다.

무엇보다도 저는 이 솔루션을 구축했습니다.동료 몇 명이 안고 있는 문제를 해결하기 위해서입니다.불변하고 큰 데이터 세트를 신속하게 피벗해야 합니다.

이 솔루션에서는 스토어드 프로시저를 작성해야 합니다.따라서 이 프로시저가 필요 없는 경우에는 지금 바로 읽기를 중지하십시오.

이 절차에서는 피벗 문의 주요 변수를 사용하여 다양한 테이블, 열 이름 및 집계를 위한 피벗 문을 동적으로 만듭니다.[ Static ]컬럼은 피벗의 그룹 by / identity 컬럼으로 사용됩니다(필요하지 않은 경우 피벗 스테이트먼트에서 매우 일반적이며 원래 문제를 해결하기 위해 필요함).피벗 컬럼은 최종 결과 컬럼 이름이 생성되는 곳입니다.값 컬럼은 집약이 적용되는 대상입니다.Table 파라미터는 스키마(schema.tablename)를 포함한 테이블의 이름입니다.이 부분은 내가 원하는 만큼 깨끗하지 않기 때문에 약간의 사랑을 사용할 수 있습니다.제 사용법이 공개되지 않았고 SQL 주입이 문제가 아니었기 때문에 효과가 있었습니다.Aggregate 파라미터는 표준 SQL Aggregate 'AVG', 'SUM', 'MAX' 등을 모두 받아들입니다.코드는 집약으로서도 디폴트로 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'

이 실행은 각각 다음 데이터 세트를 반환합니다.

STRING_AGG 함수를 사용하여 SQL Server 2017 버전을 업데이트하여 피벗 열 목록을 구성했습니다.

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;

불필요한 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);

다음 코드는 출력에서 NULL을 0으로 대체하는 결과를 제공합니다.

테이블 생성 및 데이터 삽입:

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을 0으로 대체하는 정확한 결과를 생성하기 위한 쿼리:

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

출력:

퍼포먼스 향상에 관한 Taryn의 답변 버전:

데이터.

CREATE TABLE dbo.Temp
(
    [date] datetime NOT NULL,
    category nchar(3) NOT NULL,
    amount money NOT NULL,

    INDEX [CX dbo.Temp date] CLUSTERED ([date]),
    INDEX [IX dbo.Temp category] NONCLUSTERED (category)
);

INSERT dbo.Temp
    ([date], category, amount)
VALUES
    ({D '2012-01-01'}, N'ABC', $1000.00),
    ({D '2012-01-02'}, N'DEF', $500.00),
    ({D '2012-01-02'}, N'GHI', $800.00),
    ({D '2012-02-10'}, N'DEF', $700.00),
    ({D '2012-03-01'}, N'ABC', $1100.00);

동적 피벗

DECLARE 
    @Delimiter nvarchar(4000) = N',',
    @DelimiterLength bigint,
    @Columns nvarchar(max),
    @Query nvarchar(max);

SET @DelimiterLength = LEN(REPLACE(@Delimiter, SPACE(1), N'#'));

-- Before SQL Server 2017
SET @Columns =
    STUFF
    (
        (
            SELECT 
                [text()] = @Delimiter,
                [text()] = QUOTENAME(T.category)
            FROM dbo.Temp AS T
            WHERE T.category IS NOT NULL
            GROUP BY T.category
            ORDER BY T.category
            FOR XML PATH (''), TYPE
        )
        .value(N'text()[1]', N'nvarchar(max)'),
        1, @DelimiterLength, SPACE(0)
    );

-- Alternative for SQL Server 2017+ and database compatibility level 110+
SELECT @Columns = 
    STRING_AGG(CONVERT(nvarchar(max), QUOTENAME(T.category)), N',')
        WITHIN GROUP (ORDER BY T.category)
FROM 
(
    SELECT T2.category
    FROM dbo.Temp AS T2
    WHERE T2.category IS NOT NULL
    GROUP BY T2.category
) AS T;

IF @Columns IS NOT NULL
BEGIN
    SET @Query = 
        N'SELECT [date], ' + 
        @Columns + 
        N' 
        FROM
        (
            SELECT [date], amount, category
            FROM dbo.Temp
        ) AS S
        PIVOT
        (
            MAX(amount)
            FOR category IN (' +
            @Columns +
            N')
        ) AS P;';

    EXECUTE sys.sp_executesql @Query;
END;

실행 계획

결과.

날짜.	ABC	데프	GHI
2012-01-01 00:00:00.000	1000.00	특수한 순서	특수한 순서
2012-01-02 00:00:00.000	특수한 순서	500.00	800.00
2012-02-10 00:00:00.000	특수한 순서	700.00	특수한 순서
2012-03-01 00:00:00.000	1100.00	특수한 순서	특수한 순서

CREATE TABLE #PivotExample(
   [ID]      [nvarchar](50)   NULL,       
   [Description]   [nvarchar](50)   NULL,
   [ClientId]   [smallint] NOT NULL,
)
GO


INSERT   #PivotExample  ([ID],[Description],   [ClientId])   VALUES ('ACI1','ACI1Desc1',1008)
INSERT   #PivotExample  ([ID],[Description],   [ClientId])   VALUES ('ACI1','ACI1Desc2',2000)
INSERT   #PivotExample  ([ID],[Description],   [ClientId])   VALUES ('ACI1','ACI1Desc3',3000)
INSERT   #PivotExample  ([ID],[Description],   [ClientId])   VALUES ('ACI1','ACI1Desc4',4000)
INSERT   #PivotExample  ([ID],[Description],   [ClientId])   VALUES ('ACI2','ACI2Desc1',5000)
INSERT   #PivotExample  ([ID],[Description],   [ClientId])   VALUES ('ACI2','ACI2Desc2',6000)
INSERT   #PivotExample  ([ID],[Description],   [ClientId])   VALUES ('ACI2','ACI2Desc3', 7000)

SELECT * FROM #PivotExample




--Declare necessary variables
DECLARE   @SQLQuery AS NVARCHAR(MAX)
DECLARE   @PivotColumns AS NVARCHAR(MAX)
 
--Get unique values of pivot column  
SELECT   @PivotColumns= COALESCE(@PivotColumns + ',','') + QUOTENAME([Description])
FROM (SELECT DISTINCT [Description] FROM [dbo].#PivotExample) AS PivotExample
 
--SELECT   @PivotColumns
 
--Create the dynamic query with all the values for 
--pivot column at runtime
SET   @SQLQuery = 
    N' -- Your pivoted result comes here
    SELECT ID, ' + @PivotColumns + '
    FROM 
    (
        -- Source table should in a inner query
        SELECT ID,[Description],[ClientId]
        FROM #PivotExample
    )AS P
    PIVOT
    (     
          -- Select the values from derived table P
          SUM(ClientId) 
          FOR [Description] IN (' + @PivotColumns + ') 
    )AS PVTTable'
 
--SELECT   @SQLQuery
--Execute dynamic query
EXEC sp_executesql @SQLQuery


Drop table #PivotExample

기존과 다른 MS SQL 환경(Azure Synapse Analytics Serverless SQL Pool 등)에서 동작하는 완전 범용적인 방법 - SPROC에 포함되어 있지만 이를 사용할 필요는 없습니다.

-- DROP PROCEDURE IF EXISTS
if object_id('dbo.usp_generic_pivot') is not null 
     DROP PROCEDURE dbo.usp_generic_pivot
GO;

CREATE PROCEDURE dbo.usp_generic_pivot (
    @source NVARCHAR (100), -- table or view object name
   @pivotCol NVARCHAR (100), -- the column to pivot
   @pivotAggCol NVARCHAR (100), -- the column with the values for the pivot
   @pivotAggFunc NVARCHAR (20), -- the aggregate function to apply to those values
   @leadCols NVARCHAR (100) -- comma seprated list of other columns to keep and order by
)
AS
BEGIN
   DECLARE @pivotedColumns NVARCHAR(MAX)
   DECLARE @tsql NVARCHAR(MAX)

   SET @tsql = CONCAT('SELECT @pivotedColumns = STRING_AGG(qname, '','') FROM (SELECT DISTINCT QUOTENAME(', @pivotCol,') AS qname FROM ',@source, ') AS qnames')
   EXEC sp_executesql @tsql, N'@pivotedColumns nvarchar(max) out', @pivotedColumns out

   SET @tsql = CONCAT ( 'SELECT ', @leadCols,   ',', @pivotedColumns,' FROM ',' ( SELECT ',@leadCols,',',
         @pivotAggCol,',',   @pivotCol,   ' FROM ',   @source,   ') as t ',
         ' PIVOT (', @pivotAggFunc,   '(', @pivotAggCol,   ')',' FOR ',   @pivotCol,
         '   IN (', @pivotedColumns,')) as pvt ',' ORDER BY ',   @leadCols)

   EXEC (@tsql)

END
GO;

-- TEST EXAMPLE
EXEC dbo.usp_generic_pivot  
    @source  = '[your_db].[dbo].[form_answers]',
   @pivotCol  = 'question', 
   @pivotAggCol   = 'answer',
   @pivotAggFunc  = 'MAX', 
   @leadCols    = 'candidate_id, candidate_name' 
GO;

언급URL : https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query

'programing' 카테고리의 다른 글

테이블(아직 존재하지 않는 경우)에 열을 추가합니다. (0)	2023.04.06
T-SQL을 사용하여 외부 키 제약을 일시적으로 해제하려면 어떻게 해야 합니까? (0)	2023.04.06
SQL Server Management Studio에서 컴포지트 키를 만드는 방법 (0)	2023.04.06
날짜/시간을 삽입하는 동안 문자열에서 날짜 및/또는 시간을 변환할 때 변환에 실패했습니다. (0)	2023.04.06
SQL Server DB의 모든 인덱스 및 인덱스 열 목록 (0)	2023.04.06

현재글SQL Server 동적 피벗 쿼리?

각종 프로그래밍 정보를 다루는 블로그입니다.

reactjs, WPF, oracle, SWiFT, Excel, spring-boot, azure, string, WordPress, sql-server, MongoDB, GIT, TypeScript, Windows, Objective-C, Ajax, Spring, bash, JSON, AngularJS,

Today :
Yesterday :

mbctv

SQL Server 동적 피벗 쿼리?

SQL Server 동적 피벗 쿼리?

동적 SQL 피벗

데이터.

동적 피벗

실행 계획

결과.

'programing' 카테고리의 다른 글

'programing'의 다른글

티스토리툴바

« 2026/04 »
일	월	화	수	목	금	토
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30

SQL Server 동적 피벗 쿼리?

SQL Server 동적 피벗 쿼리?

동적 SQL 피벗

데이터.

동적 피벗

실행 계획

결과.

'programing' 카테고리의 다른 글

'programing'의 다른글

관련글

티스토리툴바