SQL Server에서 'Pivot'을 사용하여 행을 열로 변환
MS 피벗 테이블에 있는 내용을 읽었는데, 아직 수정에 문제가 있습니다.
작성 중인 임시 테이블이 있습니다. 1열은 상점 번호, 2열은 주 번호, 마지막으로 3열은 합계가 됩니다.또한 Week 번호는 동적이며 스토어 번호는 정적입니다.
Store Week xCount
------- ---- ------
102 1 96
101 1 138
105 1 37
109 1 59
101 2 282
102 2 212
105 2 78
109 2 97
105 3 60
102 3 123
101 3 220
109 3 87
다음과 같이 피벗 테이블로 나왔으면 합니다.
Store 1 2 3 4 5 6....
-----
101 138 282 220
102 96 212 123
105 37
109
옆면에는 숫자를, 위에는 몇 주씩 저장하세요.
SQL Server 2005+ 를 사용하고 있는 경우는,PIVOT데이터를 행에서 열로 변환하는 함수입니다.
주를 알 수 없는 경우에는 동적 sql을 사용해야 하지만 처음에는 하드 코딩된 버전을 사용하여 올바른 코드를 쉽게 확인할 수 있습니다.
먼저, 사용하기 위한 몇 가지 간단한 테이블 정의와 데이터를 소개합니다.
CREATE TABLE yt
(
[Store] int,
[Week] int,
[xCount] int
);
INSERT INTO yt
(
[Store],
[Week], [xCount]
)
VALUES
(102, 1, 96),
(101, 1, 138),
(105, 1, 37),
(109, 1, 59),
(101, 2, 282),
(102, 2, 212),
(105, 2, 78),
(109, 2, 97),
(105, 3, 60),
(102, 3, 123),
(101, 3, 220),
(109, 3, 87);
값이 알려진 경우 쿼리를 하드코드합니다.
select *
from
(
select store, week, xCount
from yt
) src
pivot
(
sum(xcount)
for week in ([1], [2], [3])
) piv;
주 번호를 동적으로 생성해야 하는 경우 코드는 다음과 같습니다.
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(Week)
from yt
group by Week
order by Week
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT store,' + @cols + ' from
(
select store, week, xCount
from yt
) x
pivot
(
sum(xCount)
for week in (' + @cols + ')
) p '
execute(@query);
다이내믹 버전에서는 다음 리스트가 생성됩니다.week열로 변환해야 하는 숫자입니다.둘 다 같은 결과를 얻을 수 있습니다.
| STORE | 1 | 2 | 3 |
---------------------------
| 101 | 138 | 282 | 220 |
| 102 | 96 | 212 | 123 |
| 105 | 37 | 78 | 60 |
| 109 | 59 | 97 | 87 |
이것은 다이나믹한 주수에 대한 것입니다.
완전한 예는 다음과 같습니다.SQL 동적 피벗
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Week)
FROM (SELECT DISTINCT Week FROM #StoreSales) AS Weeks
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
N'SELECT Store, ' + @ColumnName + '
FROM #StoreSales
PIVOT(SUM(xCount)
FOR Week IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery
저도 서브쿼리를 사용하여 같은 일을 해 본 적이 있습니다.원래 테이블이 StoreCountsBy라고 불리는 경우[ Store IDs ](스토어 ID)를 나열한 별도의 테이블이 있으면 다음과 같이 표시됩니다.
SELECT StoreID,
Week1=(SELECT ISNULL(SUM(xCount),0) FROM StoreCountsByWeek WHERE StoreCountsByWeek.StoreID=Store.StoreID AND Week=1),
Week2=(SELECT ISNULL(SUM(xCount),0) FROM StoreCountsByWeek WHERE StoreCountsByWeek.StoreID=Store.StoreID AND Week=2),
Week3=(SELECT ISNULL(SUM(xCount),0) FROM StoreCountsByWeek WHERE StoreCountsByWeek.StoreID=Store.StoreID AND Week=3)
FROM Store
ORDER BY StoreID
이 방법의 장점 중 하나는 구문이 보다 명확하고 다른 테이블에 쉽게 결합하여 다른 필드도 결과로 가져올 수 있다는 것입니다.
이 쿼리를 수천 행에 걸쳐 1초도 안 되는 시간에 실행한 결과 7개의 서브쿼리가 있었습니다.그러나 코멘트에서 언급했듯이, 이러한 방식으로 실행하는 것은 계산적으로 더 비싸기 때문에 대량의 데이터에서 실행할 것으로 예상될 경우 이 방법을 사용하는 데 주의해야 합니다.
할 수 있는 일은 다음과 같습니다.
SELECT *
FROM yourTable
PIVOT (MAX(xCount)
FOR Week in ([1],[2],[3],[4],[5],[6],[7])) AS pvt
이 목적에 도움이 될 수 있는 sp를 쓰고 있습니다.기본적으로 이 sp는 임의의 테이블을 피벗하여 새로운 테이블을 반환하거나 데이터 세트만 반환합니다.이 방법은 다음과 같습니다.
Exec dbo.rs_pivot_table @schema=dbo,@table=table_name,@column=column_to_pivot,@agg='sum([column_to_agg]),avg([another_column_to_agg]),',
@sel_cols='column_to_select1,column_to_select2,column_to_select1',@new_table=returned_table_pivoted;
파라미터 @parameter의 컬럼명은 다음과 같아야 합니다.'[' 합니다.','
SP
Create Procedure [dbo].[rs_pivot_table]
@schema sysname=dbo,
@table sysname,
@column sysname,
@agg nvarchar(max),
@sel_cols varchar(max),
@new_table sysname,
@add_to_col_name sysname=null
As
--Exec dbo.rs_pivot_table dbo,##TEMPORAL1,tip_liq,'sum([val_liq]),sum([can_liq]),','cod_emp,cod_con,tip_liq',##TEMPORAL1PVT,'hola';
Begin
Declare @query varchar(max)='';
Declare @aggDet varchar(100);
Declare @opp_agg varchar(5);
Declare @col_agg varchar(100);
Declare @pivot_col sysname;
Declare @query_col_pvt varchar(max)='';
Declare @full_query_pivot varchar(max)='';
Declare @ind_tmpTbl int; --Indicador de tabla temporal 1=tabla temporal global 0=Tabla fisica
Create Table #pvt_column(
pivot_col varchar(100)
);
Declare @column_agg table(
opp_agg varchar(5),
col_agg varchar(100)
);
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@table) AND type in (N'U'))
Set @ind_tmpTbl=0;
ELSE IF OBJECT_ID('tempdb..'+ltrim(rtrim(@table))) IS NOT NULL
Set @ind_tmpTbl=1;
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@new_table) AND type in (N'U')) OR
OBJECT_ID('tempdb..'+ltrim(rtrim(@new_table))) IS NOT NULL
Begin
Set @query='DROP TABLE '+@new_table+'';
Exec (@query);
End;
Select @query='Select distinct '+@column+' From '+(case when @ind_tmpTbl=1 then 'tempdb.' else '' end)+@schema+'.'+@table+' where '+@column+' is not null;';
Print @query;
Insert into #pvt_column(pivot_col)
Exec (@query)
While charindex(',',@agg,1)>0
Begin
Select @aggDet=Substring(@agg,1,charindex(',',@agg,1)-1);
Insert Into @column_agg(opp_agg,col_agg)
Values(substring(@aggDet,1,charindex('(',@aggDet,1)-1),ltrim(rtrim(replace(substring(@aggDet,charindex('[',@aggDet,1),charindex(']',@aggDet,1)-4),')',''))));
Set @agg=Substring(@agg,charindex(',',@agg,1)+1,len(@agg))
End
Declare cur_agg cursor read_only forward_only local static for
Select
opp_agg,col_agg
from @column_agg;
Open cur_agg;
Fetch Next From cur_agg
Into @opp_agg,@col_agg;
While @@fetch_status=0
Begin
Declare cur_col cursor read_only forward_only local static for
Select
pivot_col
From #pvt_column;
Open cur_col;
Fetch Next From cur_col
Into @pivot_col;
While @@fetch_status=0
Begin
Select @query_col_pvt='isnull('+@opp_agg+'(case when '+@column+'='+quotename(@pivot_col,char(39))+' then '+@col_agg+
' else null end),0) as ['+lower(Replace(Replace(@opp_agg+'_'+convert(varchar(100),@pivot_col)+'_'+replace(replace(@col_agg,'[',''),']',''),' ',''),'&',''))+
(case when @add_to_col_name is null then space(0) else '_'+isnull(ltrim(rtrim(@add_to_col_name)),'') end)+']'
print @query_col_pvt
Select @full_query_pivot=@full_query_pivot+@query_col_pvt+', '
--print @full_query_pivot
Fetch Next From cur_col
Into @pivot_col;
End
Close cur_col;
Deallocate cur_col;
Fetch Next From cur_agg
Into @opp_agg,@col_agg;
End
Close cur_agg;
Deallocate cur_agg;
Select @full_query_pivot=substring(@full_query_pivot,1,len(@full_query_pivot)-1);
Select @query='Select '+@sel_cols+','+@full_query_pivot+' into '+@new_table+' From '+(case when @ind_tmpTbl=1 then 'tempdb.' else '' end)+
@schema+'.'+@table+' Group by '+@sel_cols+';';
print @query;
Exec (@query);
End;
GO
다음은 실행 예를 제시하겠습니다.
Exec dbo.rs_pivot_table @schema=dbo,@table=##TEMPORAL1,@column=tip_liq,@agg='sum([val_liq]),avg([can_liq]),',@sel_cols='cod_emp,cod_con,tip_liq',@new_table=##TEMPORAL1PVT;
Select * From ##TEMPORAL1PVT★★★★
다음은 조금 더 쉽게 피벗을 이해할 수 있도록 위의 @Tayrn answer 개정판입니다.
이것이 최선의 방법은 아닐 수도 있지만, 이것이 테이블 피벗 방법에 대해 머리를 싸매는 데 도움이 되었습니다.
ID = 피벗할 행
MY_KEY = 피벗할 열 이름이 들어 있는 원래 테이블에서 선택하는 열입니다.
VAL = 각 열 아래에 반환할 값입니다.
MAX(VAL) => 다른 집약 함수로 대체할 수 있습니다.SUM(VAL), MIN(VAL), 기타...
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(MY_KEY)
from yt
group by MY_KEY
order by MY_KEY ASC
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT ID,' + @cols + ' from
(
select ID, MY_KEY, VAL
from yt
) x
pivot
(
sum(VAL)
for MY_KEY in (' + @cols + ')
) p '
execute(@query);
select * from (select name, ID from Empoyee) Visits
pivot(sum(ID) for name
in ([Emp1],
[Emp2],
[Emp3]
) ) as pivottable;
다른 데이터베이스가 이 문제를 어떻게 해결하는지 알려주십시오. DolphinDB에는 피벗 지원도 내장되어 있어 SQL이 훨씬 직관적이고 깔끔해 보입니다. 열(키 열만 하면 .Store 컬럼(), 피벗 컬럼()Week 계산된 메트릭 「」)입니다sum(xCount)를 참조해 주세요.
//prepare a 10-million-row table
n=10000000
t=table(rand(100, n) + 1 as Store, rand(54, n) + 1 as Week, rand(100, n) + 1 as xCount)
//use pivot clause to generate a pivoted table pivot_t
pivot_t = select sum(xCount) from t pivot by Store, Week
DolphinDB는 컬럼형 고성능 데이터베이스입니다.데모의 계산은, 델의 xps 노트북(i7 CPU)에서는 546 밀리초입니다.상세한 것에 대하여는, 온라인의 DolphinDB 메뉴얼을 참조해 주세요.https://www.dolphindb.com/help/index.html?pivotby.html
피벗은 SQL 연산자 중 하나로, 출력에서 하나의 열에 있는 고유한 데이터를 여러 열로 변환하는 데 사용됩니다.이는 행을 열(회전 테이블)로 변환하는 경우에도 마찬가지입니다.이 표를 생각해 봅시다.
각 고객의 제품 유형(스피커, 글라스, 헤드셋)에 따라 이 데이터를 필터링하려면 피벗 연산자를 사용하십시오.
Select CustmerName, Speaker, Glass, Headset
from TblCustomer
Pivot
(
Sum(Price) for Product in ([Speaker],[Glass],[Headset])
) as PivotTable
언급URL : https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server
'source' 카테고리의 다른 글
| SQL SERVER: 두 날짜 사이의 총 일수 가져오기 (0) | 2023.04.07 |
|---|---|
| 사용 시 NOCOUNT ON 설정 (0) | 2023.04.07 |
| SQL Server Management Studio를 사용한 데이터베이스 Import/export (0) | 2023.04.07 |
| SQL Server에서의 사용자와 로그인의 차이 (0) | 2023.04.07 |
| 서버에 연결할 수 없음 - 네트워크 관련 오류 또는 인스턴스별 오류 (0) | 2023.04.07 |