有什么好的方法可以将SQL表中的数据从行列转换为列行? [英] What are some good ways to transpose data in a SQL table from row-columns to column-rows?
问题描述
将SQL表中的数据从行列转换为列行的一些好方法是什么?
What are some good ways to transpose data in a SQL table from row-columns to column-rows?
还允许在初始查询中应用过滤器/条件.
Also allowing filters/where conditions to be applied on the initial query.
使用SQL Server 2008.
Using SQL Server 2008.
现有表具有以下列: AID(nvarchar唯一) ASID(nvarchar唯一) 里程碑(M1,M2,M3 ... M100) 里程碑日期(日期时间)
Existing table has following Columns: AID (nvarchar unique) ASID (nvarchar unique) Milestone (M1, M2, M3...M100) MilestoneDate (datetime)
转置数据如下: AID,ASID,M1日期,M2日期,M3日期,M5日期
Transposed data as follows: AID, ASID, M1 Date, M2 Date, M3 Date, M5 Date
推荐答案
如果您使用的是SQL Server 2005+,则可以使用一些选项来转置数据.您可以实现类似于以下内容的PIVOT
函数:
If you are using SQL Server 2005+, then you have a few options to transpose the data. You can implement the PIVOT
function similar to this:
select AID, ASID,
M1 as M1_Date, M2 as M2_Date,
M3 as M3_Date, M4 as M4_Date, M5 as M5_Date
from
(
select AID, ASID, Milestone,
MilestoneDate
from yourtable
where AID = whatever -- other filters here
) src
pivot
(
max(milestonedate)
for milestone in (M1, M2, M3, M4, M5...)
) piv
或者您可以在CASE
语句中使用聚合函数:
Or you can use an aggregate function with a CASE
statement:
select aid,
asid,
max(case when milestone = 'M1' then milestonedate else null end) M1_Date,
max(case when milestone = 'M2' then milestonedate else null end) M2_Date,
max(case when milestone = 'M3' then milestonedate else null end) M3_Date,
max(case when milestone = 'M4' then milestonedate else null end) M4_Date
from yourtable
where AID = whatever -- other filters here
group by aid, asid
如果您具有已知数量的milestone
值,则上述两个查询非常有用.但是,如果没有,则可以实现动态sql来转置数据.动态版本将与此类似:
The above two queries work great if you have a known number of milestone
values. But if not, then you can implement dynamic sql to transpose the data. The dynamic version would be similar to this:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@colNames AS NVARCHAR(MAX),
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Milestone)
from yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @colNames = STUFF((SELECT distinct ',' + QUOTENAME(Milestone+'_Date')
from yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT AID, ASID,' + @colNames + ' from
(
select AID, ASID, Milestone,
MilestoneDate
from yourtable
where AID = whatever -- other filters here
) x
pivot
(
max(MilestoneDate)
for Milestone in (' + @cols + ')
) p '
execute(@query)
这篇关于有什么好的方法可以将SQL表中的数据从行列转换为列行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!