Bagaimana saya hanya beralih kolom dengan baris di SQL? Apakah ada perintah sederhana untuk merefleksikan?
yaitu mengubah hasil ini:
Paul | John | Tim | Eric
Red 1 5 1 3
Green 8 4 3 5
Blue 2 2 9 1
menjadi seperti ini:
Red | Green | Blue
Paul 1 8 2
John 5 4 2
Tim 1 3 9
Eric 3 5 1
PIVOT
tampaknya terlalu kompleks untuk skenario ini.
Ada beberapa cara bahwa anda dapat mengubah data ini. Dalam posting asli anda, anda menyatakan bahwa PIVOT
tampaknya terlalu kompleks untuk skenario ini, tetapi hal ini dapat diterapkan dengan sangat mudah menggunakan kedua UNPIVOT
dan PIVOT
fungsi dalam SQL Server.
Namun, jika anda tidak memiliki akses ke fungsi-fungsi ini dapat direplikasi menggunakan UNION ALL
untuk UNPIVOT
dan kemudian fungsi agregat dengan KASUS
pernyataan PIVOT
:
Membuat Tabel:
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, Agregat dan KASUS Version:
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
Lihat SQL Biola dengan Demo
The UNION ALL
melakukan UNPIVOT
data dengan mengubah kolom Paulus, Yohanes, Tim, Eric
ke baris yang terpisah. Kemudian anda menerapkan fungsi agregat jumlah ' ()
dengan kasus
pernyataan untuk mendapatkan kolom baru untuk masing-masing warna
.
Unpivot dan Pivot Versi Statis:
Kedua UNPIVOT
dan PIVOT
fungsi dalam SQL server membuat transformasi ini jauh lebih mudah. Jika anda tahu semua nilai-nilai yang anda ingin mengubah, anda dapat keras-kode mereka ke versi statis untuk mendapatkan hasil:
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
Lihat SQL Biola dengan Demo
Inner query dengan UNPIVOT
melakukan fungsi yang sama sebagai UNION ALL
. Dibutuhkan daftar kolom dan mengubahnya menjadi baris, PIVOT
kemudian melakukan transformasi akhir ke dalam kolom.
Dynamic Pivot Versi:
Jika anda memiliki jumlah yang tidak diketahui dari kolom (Paulus, Yohanes, Tim, Eric
dalam contoh anda) dan kemudian jumlah yang tidak diketahui dari warna untuk mengubah anda dapat menggunakan dynamic sql untuk menghasilkan daftar untuk UNPIVOT
dan kemudian 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)
Lihat SQL Biola dengan Demo
Dinamis versi query kedua yourtable
dan kemudian sys.kolom
meja untuk menghasilkan daftar item untuk UNPIVOT
dan PIVOT
. Hal ini kemudian ditambahkan ke dalam string query yang akan dieksekusi. Plus dinamis versi ini jika anda telah mengubah daftar dari warna
dan/atau nama
ini akan menghasilkan daftar pada saat run-time.
Semua tiga pertanyaan akan menghasilkan hasil yang sama:
| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric | 3 | 5 | 1 |
| John | 5 | 4 | 2 |
| Paul | 1 | 8 | 2 |
| Tim | 1 | 3 | 9 |
Ini biasanya mengharuskan anda untuk mengetahui SEMUA kolom DAN label baris sebelumnya. Seperti yang anda lihat dalam query di bawah ini, label adalah semua yang tercantum dalam mereka sepenuhnya di kedua UNPIVOT dan (re)PIVOT operasi.
MS SQL Server 2012 Skema Pengaturan:
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;
Pertanyaan 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 |
I'd ingin menunjukkan beberapa solusi untuk menukar kolom dan baris dalam SQL.
Yang pertama adalah menggunakan KURSOR. Meskipun konsensus umum di masyarakat profesional adalah untuk tinggal jauh dari SQL Server Kursor, masih ada kasus dimana penggunaan kursor dianjurkan. Pokoknya, Kursor menyajikan kita dengan pilihan lain untuk mengubah baris menjadi kolom.
Serupa dengan PIVOT, kursor memiliki dinamis kemampuan untuk menambahkan lebih baris anda dataset memperluas untuk mencakup lebih banyak kebijakan angka.
Tidak seperti PIVOT, kursor unggul di daerah ini karena mampu memperluas untuk mencakup dokumen baru ditambahkan, tanpa mengubah script.
Keterbatasan utama dari menukar baris ke kolom dengan menggunakan KURSOR adalah kerugian yang terkait untuk menggunakan kursor di umum – mereka datang pada kinerja yang signifikan biaya. Hal ini karena Kursor menghasilkan permintaan yang terpisah untuk masing-masing MENGAMBIL operasi BERIKUTNYA.
Solusi lain dari menukar baris ke kolom adalah dengan menggunakan XML.
XML solusi untuk mengubah baris menjadi kolom pada dasarnya adalah sebuah versi yang optimal dari PIVOT yang membahas dinamis kolom batasan.
XML versi script alamat keterbatasan ini dengan menggunakan kombinasi XML Jalan, dinamis T-SQL dan beberapa fungsi built-in (yaitu hal-HAL, QUOTENAME).
Serupa dengan PIVOT dan Kursor, baru ditambahkan kebijakan yang dapat diambil dalam versi XML script tanpa mengubah naskah asli.
Tidak seperti PIVOT, dokumen baru ditambahkan dapat ditampilkan tanpa mengubah script.
Dalam hal IO, statistik dari versi XML dari script ini hampir sama dengan PIVOT – satu-satunya perbedaan adalah bahwa XML memiliki kedua scan dtTranspose meja tapi kali ini dari logis baca – data cache.
Anda dapat menemukan lebih banyak tentang solusi ini (termasuk beberapa yang sebenarnya T-SQL exmaples) di artikel ini: https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/
Berdasarkan ini solusi dari bluefeet berikut ini adalah stored procedure yang menggunakan dynamic sql untuk menghasilkan dialihkan meja. Hal ini membutuhkan bahwa semua bidang numerik kecuali untuk dialihkan kolom (kolom yang akan header dalam tabel yang dihasilkan):
/****** 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
Anda dapat mengujinya dengan tabel yang disediakan dengan perintah ini:
exec SQLTranspose 'yourTable', 'color'
I'm melakukan UnPivot
pertama dan menyimpan hasil di CTE
dan menggunakan CTE
di Pivot
operasi.
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;
Menambahkan @Paco Zarate's hebat jawaban di atas, jika anda ingin mengubah suatu tabel yang memiliki beberapa jenis kolom, kemudian tambahkan pada akhir baris 39, sehingga hanya transposes int
kolom:
and C.system_type_id = 56 --56 = type int
Berikut ini adalah query yang sedang berubah:
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, '')
Untuk menemukan lain system_type_id
's, jalankan ini:
select name, system_type_id from sys.types order by name
Saya ingin berbagi kode i'm menggunakan untuk merefleksikan sebuah splited teks berdasarkan +bluefeet menjawab. Dalam hal ini aproach i'm diimplementasikan sebagai prosedur dalam 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
I'm pencampuran larutan ini dengan informasi tentang howto rangka baris tanpa order by (SQLAuthority.com) dan fungsi split di MSDN (social.msdn.microsoft.com)
Ketika anda menjalankan prodecure
DECLARE @RC int
DECLARE @InputToSplit varchar(MAX)
DECLARE @Delimeter varchar(1)
set @InputToSplit = 'hello|beautiful|world'
set @Delimeter = '|'
EXECUTE @RC = [TransposeSplit]
@InputToSplit
,@Delimeter
GO
anda obtaint hasil selanjutnya
name rowid 1 2 3
col_value 1 hello beautiful world
Ini cara Mengkonversi semua Data Dari Filelds(Kolom) Pada Tabel Untuk Record (Baris).
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
Saya bisa menggunakan Paco Zarate's solusi dan bekerja indah. Saya harus menambahkan satu baris ("SET ANSI_WARNINGS PADA"), tapi itu mungkin sesuatu yang unik untuk cara saya digunakan atau yang disebut itu. Ada masalah dengan penggunaan saya dan saya berharap seseorang dapat membantu saya dengan ini:
Solusi ini hanya bekerja dengan yang sebenarnya tabel SQL. Saya mencoba dengan tabel sementara dan juga aplikasi yang di memori (menyatakan) tabel tapi itu doesn't bekerja dengan orang-orang. Jadi saya memanggil kode yang saya buat tabel pada database my SQL dan kemudian memanggil SQLTranspose. Sekali lagi, karya besar. It's hanya apa yang saya inginkan. Berikut ini's masalah saya:
Dalam rangka untuk keseluruhan solusi untuk benar-benar dinamis yang saya butuhkan untuk membuat tabel tersebut di mana saya menyimpan sementara disiapkan informasi yang saya'm mengirim ke SQLTranspose "on the fly", dan kemudian menghapus tabel setelah SQLTranspose disebut. Tabel penghapusan adalah menyajikan masalah utama saya rencana implementasi. Kode yang perlu dijalankan dari aplikasi pengguna akhir (tombol pada Microsoft Access form/menu). Ketika saya menggunakan SQL ini proses (membuat tabel SQL, panggilan SQLTranspose, menghapus tabel SQL) pengguna akhir aplikasi adalah sebuah kesalahan karena SQL akun yang digunakan tidak memiliki hak untuk menjatuhkan meja.
Jadi saya pikir ada beberapa solusi yang mungkin:
Menemukan cara untuk membuat SQLTranspose bekerja dengan tabel sementara atau menyatakan variabel tabel.
Mengetahui metode lain untuk transposisi dari baris dan kolom yang doesn't membutuhkan aktual tabel SQL.
Mengetahui metode yang tepat yang memungkinkan SQL account digunakan oleh pengguna akhir untuk drop tabel. It's tunggal bersama SQL akun dikodekan ke saya Akses aplikasi. Tampak bahwa izin adalah dbo-jenis hak yang tidak dapat diberikan.
Saya menyadari bahwa beberapa dari ini mungkin perintah lain, thread terpisah dan pertanyaan. Namun, karena ada kemungkinan bahwa salah satu solusi yang mungkin hanya cara yang berbeda untuk melakukan pertukaran baris dan kolom I'll membuat posting pertama saya di sini, di thread ini.
EDIT: saya juga tidak mengganti sum(nilai) dengan max(nilai) dalam 6 baris dari akhir, seperti Paco disarankan.
EDIT:
Saya menemukan sesuatu yang bekerja untuk saya. Saya don't tahu apakah itu's terbaik menjawab atau tidak.
Saya memiliki baca-hanya user account yang digunakan untuk mengeksekusi strored prosedur dan oleh karena itu menghasilkan pelaporan output dari database. Sejak SQLTranspose fungsi yang saya buat hanya akan bekerja dengan "yang sah" tabel (tidak dinyatakan meja dan tabel sementara) saya harus mencari cara untuk membaca-hanya akun pengguna untuk membuat (dan kemudian menghapus) sebuah tabel.
Aku beralasan bahwa untuk tujuan saya itu's baik-baik saja untuk account pengguna harus diizinkan untuk membuat tabel. Pengguna masih tidak bisa menghapus tabel sekalipun. Solusi saya adalah untuk menciptakan sebuah skema di mana pengguna akun resmi. Maka setiap kali aku membuat, menggunakan, atau menghapus tabel yang merujuk dengan skema yang ditentukan.
Saya pertama kali mengeluarkan perintah ini dari a 'sa' atau 'sysadmin' akun:
MEMBUAT SKEMA ro OTORISASI
Bila ada waktu saya merujuk ke "tmpoutput" meja saya tentukan seperti contoh ini:
drop table ro.tmpoutput