SQL에서 열과 행을 쉽게 바꿀 수 있는 방법?
SQL에서 행이 있는 열을 바꾸려면 어떻게 해야 합니까?바꿀 수 있는 간단한 명령어가 있나요?
즉, 이 결과를 설정합니다.
Paul | John | Tim | Eric
Red 1 5 1 3
Green 8 4 3 5
Blue 2 2 9 1
다음과 같이 입력합니다.
Red | Green | Blue
Paul 1 8 2
John 5 4 2
Tim 1 3 9
Eric 3 5 1
PIVOT이 시나리오에서는 너무 복잡한 것 같습니다.
이 데이터를 변환할 수 있는 방법은 여러 가지가 있습니다.게시글에 적혀있었어요.PIVOT이 시나리오에서는 너무 복잡해 보이지만 SQL Server의 와 함수를 모두 사용하여 매우 쉽게 적용할 수 있습니다.
이러한 할 수 , 이, 이, 음, 음, 음, 음을 사용하여 할 수 .UNION ALL로로 합니다.UNPIVOT후, 「」, 「」의 집약 합니다.CASE를 「」로 설정합니다.PIVOT:
테이블 작성:
CREATE TABLE yourTable([color] varchar(5), [Paul] int, [John] int, [Tim] int, [Eric] int);
INSERT INTO yourTable
([color], [Paul], [John], [Tim], [Eric])
VALUES
('Red', 1, 5, 1, 3),
('Green', 8, 4, 3, 5),
('Blue', 2, 2, 9, 1);
Union All, Aggregate 및 CASE 버전:
select name,
sum(case when color = 'Red' then value else 0 end) Red,
sum(case when color = 'Green' then value else 0 end) Green,
sum(case when color = 'Blue' then value else 0 end) Blue
from
(
select color, Paul value, 'Paul' name
from yourTable
union all
select color, John value, 'John' name
from yourTable
union all
select color, Tim value, 'Tim' name
from yourTable
union all
select color, Eric value, 'Eric' name
from yourTable
) src
group by name
SQL Fidle with 데모 참조
UNION ALL합니다.UNPIVOT 합니다.Paul, John, Tim, Eric여러 줄로 나누다.에 '집계함수이다'를 하면 됩니다.sum()case color.
Unpivot 및 Pivot Static 버전:
다UNPIVOT ★★★★★★★★★★★★★★★★★」PIVOTSQL Server에 대해 설명하겠습니다.변환하는 값을 모두 알고 있는 경우는, 그것들을 스태틱버전으로 하드 코드 해 결과를 얻을 수 있습니다.
select name, [Red], [Green], [Blue]
from
(
select color, name, value
from yourtable
unpivot
(
value for name in (Paul, John, Tim, Eric)
) unpiv
) src
pivot
(
sum(value)
for color in ([Red], [Green], [Blue])
) piv
SQL Fidle with 데모 참조
및 " " " :UNPIVOT, 와, performs, performs, performs, performs, performs, performs, performs, performs, performs, performs, performs, performs, performs 등의 을 수행합니다.UNION ALLPIVOT그런 다음 열로 최종 변환을 수행합니다.
동적 피벗 버전:
알 수 없는 ( 「」 「」 「」).Paul, John, Tim, Eric예에서는 알수 없는 하기 위해 할 수 .UNPIVOT 다음에 또 한 번.PIVOT:
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX)
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id('yourtable') and
C.name <> 'color'
for xml path('')), 1, 1, '')
select @colsPivot = STUFF((SELECT ','
+ quotename(color)
from yourtable t
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select name, '+@colsPivot+'
from
(
select color, name, value
from yourtable
unpivot
(
value for name in ('+@colsUnpivot+')
) unpiv
) src
pivot
(
sum(value)
for color in ('+@colsPivot+')
) piv'
exec(@query)
SQL Fidle with 데모 참조
에서는 양쪽 모두 .yourtable에 그음음 and and andsys.columns to generate the table table table table table 。UNPIVOT ★★★★★★★★★★★★★★★★★」PIVOT그런 다음 실행할 쿼리 문자열에 추가됩니다. 포인트는 '바뀌다' 목록이 입니다.colors "/"/"names이치노
3개의 쿼리에서 모두 동일한 결과가 생성됩니다.
| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric | 3 | 5 | 1 |
| John | 5 | 4 | 2 |
| Paul | 1 | 8 | 2 |
| Tim | 1 | 3 | 9 |
일반적으로 모든 열과 행 레이블을 미리 알고 있어야 합니다.아래 쿼리에서 볼 수 있듯이 라벨은 모두 UNPIVOT 및 (re)에 모두 기재되어 있습니다.피벗 조작
MS SQL Server 2012 스키마 셋업:
create table tbl (
color varchar(10), Paul int, John int, Tim int, Eric int);
insert tbl select
'Red' ,1 ,5 ,1 ,3 union all select
'Green' ,8 ,4 ,3 ,5 union all select
'Blue' ,2 ,2 ,9 ,1;
질문 1:
select *
from tbl
unpivot (value for name in ([Paul],[John],[Tim],[Eric])) up
pivot (max(value) for color in ([Red],[Green],[Blue])) p
결과:
| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric | 3 | 5 | 1 |
| John | 5 | 4 | 2 |
| Paul | 1 | 8 | 2 |
| Tim | 1 | 3 | 9 |
기타 주의사항:
- 테이블 이름을 지정하면 local-name()을 사용하여 sys.columns 또는 FOR XML 속임수의 모든 열 이름을 확인할 수 있습니다.
- FOR XML을 사용하여 고유한 색상(또는 한 열의 값) 목록을 작성할 수도 있습니다.
- 위의 항목을 동적 SQL 배치로 결합하여 임의의 테이블을 처리할 수 있습니다.
SQL에서 열과 행을 바꾸는 몇 가지 솔루션을 추가로 지적하고 싶습니다.
첫 번째는 커서 사용입니다.전문가 커뮤니티에서는 SQL Server 커서를 멀리하는 것이 일반적인 의견이지만 여전히 커서를 사용하는 것이 권장되는 경우가 있습니다.어쨌든 커서는 행을 열로 변환할 수 있는 다른 옵션을 제공합니다.
수직 확장
피벗과 마찬가지로 커서는 데이터 세트가 확장되어 더 많은 정책 번호를 포함할 때 더 많은 행을 추가할 수 있는 동적 기능을 갖추고 있습니다.
수평 확장
피벗과 달리 커서는 스크립트를 변경하지 않고 새로 추가된 문서를 포함하도록 확장할 수 있기 때문에 이 영역에서 탁월합니다.
퍼포먼스 내역
CURSOR를 사용하여 행을 열로 전환하는 것의 주요 제한은 일반적으로 커서 사용과 관련된 단점입니다. 이러한 단점에는 상당한 성능 비용을 수반합니다.그 이유는 커서가 각 FETCH NEXT 작업에 대해 개별 쿼리를 생성하기 때문입니다.
행을 열로 바꾸는 또 다른 해결책은 XML을 사용하는 것입니다.
행을 열로 전환하는 XML 솔루션은 기본적으로 동적 열 제한에 대처한다는 점에서 최적의 PIVOT 버전입니다.
스크립트의 XML 버전에서는 XML 경로, 동적 T-SQL 및 일부 내장 함수(SUP, Quotename)의 조합을 사용하여 이 제한에 대처합니다.
수직 확장
피벗 및 커서와 마찬가지로 새로 추가된 정책은 원래 스크립트를 변경하지 않고 스크립트의 XML 버전에서 가져올 수 있습니다.
수평 확장
피벗과 달리 새로 추가된 문서는 스크립트를 변경하지 않고 표시할 수 있습니다.
퍼포먼스 내역
입출력 측면에서 스크립트의 XML 버전 통계는 PIVOT와 거의 비슷합니다.단, XML에 dtTranspose 테이블이 두 번째 스캔되어 있는데 이번에는 논리적인 읽기 데이터 캐시와 차이가 있습니다.
이러한 솔루션에 대한 자세한 내용은 이 기사(실제 T-SQL exmaple 포함)를 참조하십시오.https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/
여기 블루풋에서 이 솔루션을 기반으로 동적 SQL을 사용하여 전치된 테이블을 생성하는 저장 프로시저가 있습니다.변환된 열(결과 테이블의 머리글이 되는 열)을 제외한 모든 필드가 숫자여야 합니다.
/****** Object: StoredProcedure [dbo].[SQLTranspose] Script Date: 11/10/2015 7:08:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Paco Zarate
-- Create date: 2015-11-10
-- Description: SQLTranspose dynamically changes a table to show rows as headers. It needs that all the values are numeric except for the field using for transposing.
-- Parameters: @TableName - Table to transpose
-- @FieldNameTranspose - Column that will be the new headers
-- Usage: exec SQLTranspose <table>, <FieldToTranspose>
-- =============================================
ALTER PROCEDURE [dbo].[SQLTranspose]
-- Add the parameters for the stored procedure here
@TableName NVarchar(MAX) = '',
@FieldNameTranspose NVarchar(MAX) = ''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@queryPivot AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX),
@columnToPivot as NVARCHAR(MAX),
@tableToPivot as NVARCHAR(MAX),
@colsResult as xml
select @tableToPivot = @TableName;
select @columnToPivot = @FieldNameTranspose
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id(@tableToPivot) and
C.name <> @columnToPivot
for xml path('')), 1, 1, '')
set @queryPivot = 'SELECT @colsResult = (SELECT '',''
+ quotename('+@columnToPivot+')
from '+@tableToPivot+' t
where '+@columnToPivot+' <> ''''
FOR XML PATH(''''), TYPE)'
exec sp_executesql @queryPivot, N'@colsResult xml out', @colsResult out
select @colsPivot = STUFF(@colsResult.value('.', 'NVARCHAR(MAX)'),1,1,'')
set @query
= 'select name, rowid, '+@colsPivot+'
from
(
select '+@columnToPivot+' , name, value, ROW_NUMBER() over (partition by '+@columnToPivot+' order by '+@columnToPivot+') as rowid
from '+@tableToPivot+'
unpivot
(
value for name in ('+@colsUnpivot+')
) unpiv
) src
pivot
(
sum(value)
for '+@columnToPivot+' in ('+@colsPivot+')
) piv
order by rowid'
exec(@query)
END
다음 명령어와 함께 제공된 표를 사용하여 테스트할 수 있습니다.
exec SQLTranspose 'yourTable', 'color'
는 ★★★★★★★★★★★★★★★★★★★★★★.UnPivot해 두겠습니다.CTE 을 합니다.CTEPivot★★★★★★ 。
with cte as
(
select 'Paul' as Name, color, Paul as Value
from yourTable
union all
select 'John' as Name, color, John as Value
from yourTable
union all
select 'Tim' as Name, color, Tim as Value
from yourTable
union all
select 'Eric' as Name, color, Eric as Value
from yourTable
)
select Name, [Red], [Green], [Blue]
from
(
select *
from cte
) as src
pivot
(
max(Value)
for color IN ([Red], [Green], [Blue])
) as Dtpivot;
Zarate의 의 컬럼이 의 끝에 , 「@Paco Zarate」는 치환만 .int§:
and C.system_type_id = 56 --56 = type int
다음은 변경되는 전체 쿼리입니다.
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id(@tableToPivot) and
C.name <> @columnToPivot and C.system_type_id = 56 --56 = type int
for xml path('')), 1, 1, '')
것을 system_type_id다음 작업을 수행합니다.
select name, system_type_id from sys.types order by name
이렇게 하면 테이블의 모든 데이터를 파일드(열)에서 기록(행)으로 변환할 수 있습니다.
Declare @TableName [nvarchar](128)
Declare @ExecStr nvarchar(max)
Declare @Where nvarchar(max)
Set @TableName = 'myTableName'
--Enter Filtering If Exists
Set @Where = ''
--Set @ExecStr = N'Select * From '+quotename(@TableName)+@Where
--Exec(@ExecStr)
Drop Table If Exists #tmp_Col2Row
Create Table #tmp_Col2Row
(Field_Name nvarchar(128) Not Null
,Field_Value nvarchar(max) Null
)
Set @ExecStr = N' Insert Into #tmp_Col2Row (Field_Name , Field_Value) '
Select @ExecStr += (Select N'Select '''+C.name+''' ,Convert(nvarchar(max),'+quotename(C.name) + ') From ' + quotename(@TableName)+@Where+Char(10)+' Union All '
from sys.columns as C
where (C.object_id = object_id(@TableName))
for xml path(''))
Select @ExecStr = Left(@ExecStr,Len(@ExecStr)-Len(' Union All '))
--Print @ExecStr
Exec (@ExecStr)
Select * From #tmp_Col2Row
Go
+blueft 답변을 기반으로 분할된 텍스트를 치환하는 데 사용하는 코드를 공유합니다.이 어플리케이션에서는 MS SQL 2005의 절차로 구현되어 있습니다.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: ELD.
-- Create date: May, 5 2016.
-- Description: Transpose from rows to columns the user split function.
-- =============================================
CREATE PROCEDURE TransposeSplit @InputToSplit VARCHAR(8000)
,@Delimeter VARCHAR(8000) = ','
AS
BEGIN
SET NOCOUNT ON;
DECLARE @colsUnpivot AS NVARCHAR(MAX)
,@query AS NVARCHAR(MAX)
,@queryPivot AS NVARCHAR(MAX)
,@colsPivot AS NVARCHAR(MAX)
,@columnToPivot AS NVARCHAR(MAX)
,@tableToPivot AS NVARCHAR(MAX)
,@colsResult AS XML
SELECT @tableToPivot = '#tempSplitedTable'
SELECT @columnToPivot = 'col_number'
CREATE TABLE #tempSplitedTable (
col_number INT
,col_value VARCHAR(8000)
)
INSERT INTO #tempSplitedTable (
col_number
,col_value
)
SELECT ROW_NUMBER() OVER (
ORDER BY (
SELECT 100
)
) AS RowNumber
,item
FROM [DB].[ESCHEME].[fnSplit](@InputToSplit, @Delimeter)
SELECT @colsUnpivot = STUFF((
SELECT ',' + quotename(C.NAME)
FROM [tempdb].sys.columns AS C
WHERE C.object_id = object_id('tempdb..' + @tableToPivot)
AND C.NAME <> @columnToPivot
FOR XML path('')
), 1, 1, '')
SET @queryPivot = 'SELECT @colsResult = (SELECT '',''
+ quotename(' + @columnToPivot + ')
from ' + @tableToPivot + ' t
where ' + @columnToPivot + ' <> ''''
FOR XML PATH(''''), TYPE)'
EXEC sp_executesql @queryPivot
,N'@colsResult xml out'
,@colsResult OUT
SELECT @colsPivot = STUFF(@colsResult.value('.', 'NVARCHAR(MAX)'), 1, 1, '')
SET @query = 'select name, rowid, ' + @colsPivot + '
from
(
select ' + @columnToPivot + ' , name, value, ROW_NUMBER() over (partition by ' + @columnToPivot + ' order by ' + @columnToPivot + ') as rowid
from ' + @tableToPivot + '
unpivot
(
value for name in (' + @colsUnpivot + ')
) unpiv
) src
pivot
(
MAX(value)
for ' + @columnToPivot + ' in (' + @colsPivot + ')
) piv
order by rowid'
EXEC (@query)
DROP TABLE #tempSplitedTable
END
GO
이 솔루션과 (SQLAuthority.com)의 순서 없이 행을 주문하는 방법 및 MSDN(social.msdn.microsoft.com)의 분할 기능에 대한 정보를 혼합하고 있습니다.
프로시저를 실행하는 경우
DECLARE @RC int
DECLARE @InputToSplit varchar(MAX)
DECLARE @Delimeter varchar(1)
set @InputToSplit = 'hello|beautiful|world'
set @Delimeter = '|'
EXECUTE @RC = [TransposeSplit]
@InputToSplit
,@Delimeter
GO
다음 결과를 얻을 수 있습니다.
name rowid 1 2 3
col_value 1 hello beautiful world
나는 Paco Zarate의 솔루션을 사용할 수 있었고 그것은 아름답게 작동한다.한 줄('SET ANSI_WARNINGS ON')을 추가해야 하는데, 사용 방법이나 호출 방법에 따라 다를 수 있습니다.사용방법에 문제가 있어서 누군가 도움을 주셨으면 합니다.
이 솔루션은 실제 SQL 테이블에서만 작동합니다.임시 테이블과 인메모리(선언) 테이블로 시도했지만 작동하지 않습니다.발신자 코드로 SQL 데이터베이스에 테이블을 작성한 후 SQL Transpose를 호출합니다.다시 말하지만, 아주 잘 작동합니다.그게 바로 내가 원하는 거야.제 문제는 이렇습니다.
솔루션 전체가 완전히 동적이 되도록 하기 위해서는 준비된 정보를 일시적으로 SQLTranspose에 저장한 후 SQLTranspose가 호출되면 해당 테이블을 삭제해야 합니다.테이블 삭제는 최종 구현 계획에 문제가 있음을 나타냅니다.이 코드는 최종 사용자 애플리케이션(Microsoft Access 폼/메뉴의 버튼)에서 실행해야 합니다.이 SQL 프로세스(SQL 테이블 작성, SQL Transpose 호출, SQL 테이블 삭제)를 사용하면 최종 사용자 응용 프로그램에 오류가 발생합니다.이는 사용된 SQL 계정에는 테이블을 삭제할 권한이 없기 때문입니다.
몇 가지 해결책이 있습니다.
임시 테이블 또는 선언된 테이블 변수와 함께 SQLTranspose를 작동시키는 방법을 찾습니다.
실제 SQL 테이블을 필요로 하지 않는 행과 열의 전이를 위한 다른 방법을 알아봅니다.
최종사용자가사용하는SQL계정이테이블을드롭할수있는적절한방법을찾습니다.액세스 응용 프로그램에 코드화된 단일 공유 SQL 계정입니다.권한은 부여할 수 없는 dbo 유형의 권한인 것으로 보입니다.
이것들 중 일부는 또 다른 별개의 스레드와 질문을 정당화할 수 있다는 것을 알고 있습니다.단, 하나의 솔루션이 단순히 행과 열의 전위를 하는 다른 방법일 가능성이 있기 때문에 이 스레드에서 첫 번째 투고를 하겠습니다.
편집: 또한 Paco의 제안대로 6번째 줄의 sum(값)을 max(값)로 바꿉니다.
편집:
나한테 맞는 걸 알아냈어이게 최선인지 아닌지 모르겠어요.
스트로핑된 프로시저를 실행하여 데이터베이스에서 보고서 출력을 생성하는 데 사용되는 읽기 전용 사용자 계정이 있습니다.작성한 SQL Transpose 함수는 "합법적인" 테이블(선언된 테이블이나 임시 테이블이 아님)에서만 작동하기 때문에 읽기 전용 사용자 계정이 테이블을 만들고 나중에 삭제하는 방법을 찾아야 했습니다.
제 목적을 위해 사용자 계정에서 테이블을 만들 수 있다고 판단했습니다.그러나 사용자는 여전히 테이블을 삭제할 수 없습니다.저의 해결책은 사용자 계정이 허가된 스키마를 만드는 것이었습니다.그런 다음 테이블을 작성, 사용 또는 삭제할 때마다 지정된 스키마를 사용하여 테이블을 참조합니다.
처음에 'sa' 또는 'sysadmin' 계정에서 CREATE SCHEMA ro Authorization 명령을 발행했습니다.
"tmpoutput" 테이블을 참조할 때는 항상 다음과 같이 지정합니다.
드롭 테이블 ro.tmp 출력
언급URL : https://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql
'programing' 카테고리의 다른 글
| 핫픽스 브랜치를 기능 브랜치로 병합 (0) | 2023.04.16 |
|---|---|
| Excel File vs. read_excel in Panda (0) | 2023.04.16 |
| 파일을 리셋하거나 특정 리비전으로 되돌리려면 어떻게 해야 합니까? (0) | 2023.04.16 |
| django 엑셀 xlwt (0) | 2023.04.16 |
| Dart를 사용하여 문자열을 숫자로 해석하려면 어떻게 해야 합니까? (0) | 2023.04.16 |