在SQL Server视图中有效地将行转换为列 [英] Efficiently convert rows to columns in SQL Server view
问题描述
我有3个表格: testpackage
, testpackageReport
, testpackagereportdetail
有这样的结构:
使用此查询
SELECT
dbo。 TestPackages.PackageNumber,dbo.TestPackages.Size,
dbo.TestPackages.Code,dbo.TestPackageReports.ReportNumber,
dbo.TestPackageReportDetails.Step,dbo.TestPackageReportDetails.Status,
dbo.TestPackageReports。 SubmitDateTime,
dbo.TestPackageReportDetails.Id AS ReportDetailId
FROM
dbo.TestPackages
INNER JOIN
dbo.TestPackageReportDetails ON dbo.TestPackages.Id = dbo.TestPackageReportDetails.TestPackageId
INNER JOIN
dbo.TestPackageReports ON dbo.TestPackageReportDetails.TestPackageReportId = dbo.TestPackageReports.Id
结果是这样的:
PackageNumber大小代码ReportNumber步骤状态SubmitDateTime ReportDetailId
1000220-G-02-TR 1.31 143 LC-0431 LineCheck拒绝2010-12-12 218
1000220-G- 02-TR 1.31 143 LC-0131 LineCheck接受2011-12-12 220
1000220-G-02-TR 1.31 143 PT-0248测试接受2012-12-12 513
1000220-G-02- TR 1.31 143 DR-0202干燥接受2013-12-12 625
1000220-G-02-TR 1.31 143 AFP-AG-FL-0030冲洗接受2015-12-12 745
$ c
但是我需要像这样只显示一行数据:
PackageNumber大小代码LineCheckReportNumber LineCheckStep LineCheckStatus linecheckSubmitDateTime ReportDetailId tesReportNumber testCheckStep testStatus testSubmitDateTime ReportDetailId
1000220-G-02-TR 1.31 143 LC-0431 LineCheck Acc ept 2011-12-12 220 PT-0248测试接受2012-12-12 513
对于嘈杂的数据在预期的结果,我删除干燥和冲洗柱。正如你所看到的,我需要将所有这些记录显示为一行,另一个重要的是具有最大值 ReportDetailId
的数据,它接受 因为每个 testpackage
可以有多个 linecheck
或 test code>或 flsuhing
或烘干
报告
样本数据:
TestpackageTable
$ b
TestpackageReport
Testpackagereportdetail
我使用实体框架编写查询,就像你看到的那样,但速度很慢:
from _ctx.TestPackages
join testpack reportdet在i.Id的_ctx.TestPackageReportDetails等于
testpackreportdet.TestPackageId
将testPackageRepo加入_ctx.TestPackageReports on testpackreportdet.TestPackageReportId将testPackageRepo.Id加入g1
from y1 in g1.DefaultIfEmpty()
group new {y1,testpackreportdet} by new {i}
分组
让MaxLinecheck = grouping.Select(item => item.testpackreportdet)
.Where(item => item!= null&& item.Step ==LineCheck)
.OrderByDescending(item => item.Id)$ b $ (item => item!= null&& item.Step ==Clean)$ b (item => item.Id)
让MaxTest = grouping.Select null&& item.Step ==Test)
.OrderByDescending(item => item.Id)
让MaxFlush = grouping.Select(item => item。 (item => item.Id)
$(其中item => item!= null&& item.Step ==Flushing)
.OrderByDescending (item => item!= null&& item.Step ==Drying)
.OrderByDescending(item => item.Id)
let MaxReins = grouping.Select(item => ite m.testpackreportdet)
.Where(item => item!= null&& item.Step ==Reinstatment)
.OrderByDescending(item => item.Id)
让MaxMono = grouping.Select(item => item.testpackreportdet)
.Where(item => item!= null&& item.Step ==Mono)
.OrderByDescending(item => item.Id)
让MaxPAD = iteming.Select(item => item.testpackreportdet)
.Where(item => item!= null&& item.Step ==PADTest)
.OrderByDescending(item = > item.Id)
让MaxVariation = grouping.Select(item => item.testpackreportdet)
.Where(item => item!= null&& item)。 Step ==Variation)
.OrderByDescending(item => item.Id)
选择新的ViewDomainClass.TechnicalOffice.ViewTestPackageState()
{
Id = grouping.Key。 i.Id,
PackageNumber = grouping.Key.i.PackageNumber,
Size = grouping.Key.i.Size.ToString(),
Code = grouping.Key.i.Code,
TestPackageOrder = grouping.Key.i.TestPackageOrder,
LineCheckState = MaxLinecheck.FirstOrDefault()。Status,
LineCheckSubmitDateTime =
grouping.Where(
i => i.y1.Id == MaxLinecheck.FirstOrDefault()。TestPackageReportId)
.FirstOrDefault()
.y1.SubmitDateTime.ToString(),
LineCheckReportNumber =
grouping.Where(
i => i.y1.Id == MaxLinecheck.FirstOrDefault()。TestPackageReportId)
.FirstOrDefault()
.y1.ReportNumber,
CleaningState = MaxClean.FirstOrDefault() .Status,
CleanSubmitDateTime =
grouping.Where(i => i.y1.Id == MaxClean.FirstOrDefault()。TestPackageReportId)
.FirstOrDefault()
.y1 .SubmitDateTime.ToString(),
CleanReportNumber =
grouping.Where(i => i.y1.Id == MaxClean.FirstOrDefault()。TestPackageReportId)
.FirstOrDefault()
.y1.ReportNumber,
TestState = MaxTest.FirstOrDefault()。Status,
TestSubmitDateTime =
gro其中(i => i.y1.Id == MaxTest.FirstOrDefault()。TestPackageReportId)
.FirstOrDefault()
.y1.SubmitDateTime.ToString(),
TestReportNumber =
grouping.Where( i => i.y1.Id == MaxTest.FirstOrDefault()。TestPackageReportId)
.FirstOrDefault()
.y1.ReportNumber,
Drying = MaxDrying.FirstOrDefault( ).Status,
DryingSubmitDateTime =
grouping.Where(i => i.y1.Id == MaxDrying.FirstOrDefault()。TestPackageReportId)
.FirstOrDefault()
。 y1.SubmitDateTime.ToString(),
DryingReportNumber =
grouping.Where(i => i.y1.Id == MaxDrying.FirstOrDefault()。TestPackageReportId)
.FirstOrDefault()
.y1.ReportNumber,
Flushing = MaxFlush.FirstOrDefault()。Status,
FlushingSubmitDateTime =
grouping.Where(i => i.y1.Id = = MaxFlush.FirstOrDefault()。TestPackageReportId)
.FirstOrDefault()
.y1.SubmitDateTime.ToString(),
FlushingReportNumber =
grouping.Where(i => i.y1.Id == MaxFlush.FirstOrDefault()。TestPackageReportId)
.FirstOrDefault()
.y1.ReportNumber,
ReInstatement = MaxReins.FirstOrDefault()。Status,
ReInstatementSubmitDateTime =
grouping.Where(i => i.y1.Id == MaxReins.FirstOrDefault()。TestPackageReportId)
.FirstOrDefault()
.y1.SubmitDateTime.ToString(),
ReInstatementReportNumber =
grouping.Where(i => i.y1.Id == MaxReins.FirstOrDefault()。TestPackageReportId)
.FirstOrDefault()
.y1.ReportNumber,
Mono = MaxMono.FirstOrDefault()。Status,
MonoSubmitDateTime =
grouping.Where(i => i.y1.Id == MaxMono.FirstOrDefault()。TestPackageReportId)
.FirstOrDefault()
.y1.SubmitDateTime.ToString(),
MonoReportNumber =
grouping.Where(i => i.y1。 Id == MaxMono.FirstOrDefault()。TestPackageReportId)
.FirstOrDefault()
.y1.ReportNumber,
Variation = MaxVariation.FirstOrDefault()。Status,
VariationSubmitDateTime =
grouping.Where(i => i.y1.Id == MaxVariation.FirstOrDefault()。TestPackageReportId)
.FirstOrDefault()
.y1.SubmitDateTime.ToString(),
VariationReportNumber =
grouping.Where( i => i.y1.Id == MaxVariation.FirstOrDefault()。TestPackageReportId)
.FirstOrDefault()
.y1.ReportNumber,
PAD = MaxPAD.FirstOrDefault()。Status,
PADSubmitDateTime =
grouping.Where(i => i.y1.Id == MaxPAD.FirstOrDefault()。TestPackageReportId)
.FirstOrDefault()
.y1.SubmitDateTime。 ToString(),
PADReportNumber =
grouping.Where(i => i.y1.Id == MaxPAD.FirstOrDefault()。TestPackageReportId)
.FirstOrDefault()
。 y1.ReportNumber
})。ToList();
解决方案您需要pivoting和动态SQL。我建议把你的查询输出放到临时表中,然后使用它:
USE YourDatabase - 使用你的数据库
SELECT TP.PackageNumber,
TP.Size,
TP.Code,
R.ReportNumber,
RD.Step,
RD。 [Status],
R.SubmitDateTime,
RD.Id AS ReportDetailId
INTO #temporary - 它将自动创建带结果的临时表
FROM dbo.TestPackages TP
INNER JOIN dbo.TestPackageReportDetails RD
ON TP.Id = RD.TestPackageId
INNER JOIN dbo.TestPackageReports R
ON RD.TestPackageReportId = R.Id
使用tempdb - 切换到tempdb
使用TIES *选择顶部1 #temp
从#temporary
ORDER BY ROW_NUMBER()OVER(PARTITION BY PackageNumber,Size,Code,步骤ORDER BY ReportDetailId DESC)
- 在这里我们只得到了包含PackageNumber,Size,Code,Step的最大ReportDetailID的行
DROP TABLE #temporary - 删除#temporary,现在我们使用#temp
DECLARE @pvt_columns nvarchar(max), - 存储用于摆动的列
@unpvt_columns nvarchar(max), - - 存储将被转换为1数据类型以便转换的列
@columns nvarchar(max), - 列逗号分隔
@sql nvarchar(max) - 存储查询以运行
SELECT @pvt_columns = COALESCE(@pvt_columns,'')+','+ QUOTENAME(Step + name)
FROM(
SELECT name
FROM sys.columns
WHERE object_id = OBJECT_ID(N'#temp')
并且名称不在('PackageNumber','Size','Code','Step')
)名称
CROSS JOIN(
SELECT DISTINCT步骤
FROM #temp
)步骤
SELECT @unpvt_columns = COALESCE(@unpvt_columns,'')+',CAST('+ QUOTENAME(name)+'as nvarchar(max))'+ QUOTENAME(name),
@columns = COALESCE(@columns,'')+','+ QUOTENAME(name)
FROM sys。列
W (''PackageNumber','Size','Code','Step')
SELECT @sql = N'
SELECT *
FROM(
SELECT PackageNumber,
Size,
Code,
Step + [Columns] as [Columns],
[Values ]
FROM(
)选择PackageNumber,
大小,
Code,
Step'+ @ unpvt_columns +'
FROM #temp)t
UNPIVOT (
[Values] FOR [Columns] IN('+ STUFF(@ columns,1,1,'')+')
)unpvt
)p
PIVOT($ ('+ STUFF(@ pvt_columns,1,1,'')+')
)pvt'
中的[Columns]的b $ b MAX([Values])EXEC sp_executesql @ sql
DROP TABLE #temp
输出:
PackageNumber大小代码DryingReportDetailId DryingReportNumber DryingStatus DryingSubmitDateTime FlushingReportD etailId FlushingReportNumber FlushingStatus FlushingSubmitDateTime LineCheckReportDetailId LineCheckReportNumber LineCheckStatus LineCheckSubmitDateTime TestReportDetailId TestReportNumber TestStatus TestSubmitDateTime
1000220-G-02-TR 1,31 143 625 DR-0202接受2013-12-12 745 AFP-AG-FL-0030接受2015-12- 12 220 LC-0131接受2011-12-12 513 PT-0248接受2012-12-12
您可以使用 SELECT
变量查看存储在其中的内容, PRINT @sql
可查看查询的全文。
I have 3 tables: testpackage
, testpackageReport
, testpackagereportdetail
with this structure:
With this query
SELECT
dbo.TestPackages.PackageNumber, dbo.TestPackages.Size,
dbo.TestPackages.Code, dbo.TestPackageReports.ReportNumber,
dbo.TestPackageReportDetails.Step, dbo.TestPackageReportDetails.Status,
dbo.TestPackageReports.SubmitDateTime,
dbo.TestPackageReportDetails.Id AS ReportDetailId
FROM
dbo.TestPackages
INNER JOIN
dbo.TestPackageReportDetails ON dbo.TestPackages.Id = dbo.TestPackageReportDetails.TestPackageId
INNER JOIN
dbo.TestPackageReports ON dbo.TestPackageReportDetails.TestPackageReportId = dbo.TestPackageReports.Id
The result is this:
PackageNumber Size Code ReportNumber Step Status SubmitDateTime ReportDetailId
1000220-G-02-TR 1.31 143 LC-0431 LineCheck Reject 2010-12-12 218
1000220-G-02-TR 1.31 143 LC-0131 LineCheck Accept 2011-12-12 220
1000220-G-02-TR 1.31 143 PT-0248 Test Accept 2012-12-12 513
1000220-G-02-TR 1.31 143 DR-0202 Drying Accept 2013-12-12 625
1000220-G-02-TR 1.31 143 AFP-AG-FL-0030 Flushing Accept 2015-12-12 745
But I need to show this data just in one row like this:
PackageNumber Size Code LineCheckReportNumber LineCheckStep LineCheckStatus linecheckSubmitDateTime ReportDetailId tesReportNumber testCheckStep testStatus testSubmitDateTime ReportDetailId
1000220-G-02-TR 1.31 143 LC-0431 LineCheck Accept 2011-12-12 220 PT-0248 Test Accept 2012-12-12 513
For noisy data in the expected result I remove drying and flushing columns. As you can see I need all this records to be shown as a one row, another this that is important is the data with maximum ReportDetailId
that is accepted
because every testpackage
can have multi linecheck
or test
or flsuhing
or drying
reports
Sample data:
TestpackageTable
TestpackageReport
Testpackagereportdetail
I write the query using entity framework as you can see but it is very slow :
from i in _ctx.TestPackages
join testpackreportdet in _ctx.TestPackageReportDetails on i.Id equals
testpackreportdet.TestPackageId
join testPackageRepo in _ctx.TestPackageReports on testpackreportdet.TestPackageReportId equals testPackageRepo.Id into g1
from y1 in g1.DefaultIfEmpty()
group new { y1, testpackreportdet } by new { i }
into grouping
let MaxLinecheck = grouping.Select(item => item.testpackreportdet)
.Where(item => item != null && item.Step == "LineCheck")
.OrderByDescending(item => item.Id)
let MaxClean = grouping.Select(item => item.testpackreportdet)
.Where(item => item != null && item.Step == "Clean")
.OrderByDescending(item => item.Id)
let MaxTest = grouping.Select(item => item.testpackreportdet)
.Where(item => item != null && item.Step == "Test")
.OrderByDescending(item => item.Id)
let MaxFlush = grouping.Select(item => item.testpackreportdet)
.Where(item => item != null && item.Step == "Flushing")
.OrderByDescending(item => item.Id)
let MaxDrying = grouping.Select(item => item.testpackreportdet)
.Where(item => item != null && item.Step == "Drying")
.OrderByDescending(item => item.Id)
let MaxReins = grouping.Select(item => item.testpackreportdet)
.Where(item => item != null && item.Step == "Reinstatment")
.OrderByDescending(item => item.Id)
let MaxMono = grouping.Select(item => item.testpackreportdet)
.Where(item => item != null && item.Step == "Mono")
.OrderByDescending(item => item.Id)
let MaxPAD = grouping.Select(item => item.testpackreportdet)
.Where(item => item != null && item.Step == "PADTest")
.OrderByDescending(item => item.Id)
let MaxVariation = grouping.Select(item => item.testpackreportdet)
.Where(item => item != null && item.Step == "Variation")
.OrderByDescending(item => item.Id)
select new ViewDomainClass.TechnicalOffice.ViewTestPackageState()
{
Id = grouping.Key.i.Id,
PackageNumber = grouping.Key.i.PackageNumber,
Size = grouping.Key.i.Size.ToString(),
Code = grouping.Key.i.Code,
TestPackageOrder = grouping.Key.i.TestPackageOrder,
LineCheckState = MaxLinecheck.FirstOrDefault().Status,
LineCheckSubmitDateTime =
grouping.Where(
i => i.y1.Id == MaxLinecheck.FirstOrDefault().TestPackageReportId)
.FirstOrDefault()
.y1.SubmitDateTime.ToString(),
LineCheckReportNumber =
grouping.Where(
i => i.y1.Id == MaxLinecheck.FirstOrDefault().TestPackageReportId)
.FirstOrDefault()
.y1.ReportNumber,
CleaningState = MaxClean.FirstOrDefault().Status,
CleanSubmitDateTime =
grouping.Where(i => i.y1.Id == MaxClean.FirstOrDefault().TestPackageReportId)
.FirstOrDefault()
.y1.SubmitDateTime.ToString(),
CleanReportNumber =
grouping.Where(i => i.y1.Id == MaxClean.FirstOrDefault().TestPackageReportId)
.FirstOrDefault()
.y1.ReportNumber,
TestState = MaxTest.FirstOrDefault().Status,
TestSubmitDateTime =
grouping.Where(i => i.y1.Id == MaxTest.FirstOrDefault().TestPackageReportId)
.FirstOrDefault()
.y1.SubmitDateTime.ToString(),
TestReportNumber =
grouping.Where(i => i.y1.Id == MaxTest.FirstOrDefault().TestPackageReportId)
.FirstOrDefault()
.y1.ReportNumber,
Drying = MaxDrying.FirstOrDefault().Status,
DryingSubmitDateTime =
grouping.Where(i => i.y1.Id == MaxDrying.FirstOrDefault().TestPackageReportId)
.FirstOrDefault()
.y1.SubmitDateTime.ToString(),
DryingReportNumber =
grouping.Where(i => i.y1.Id == MaxDrying.FirstOrDefault().TestPackageReportId)
.FirstOrDefault()
.y1.ReportNumber,
Flushing = MaxFlush.FirstOrDefault().Status,
FlushingSubmitDateTime =
grouping.Where(i => i.y1.Id == MaxFlush.FirstOrDefault().TestPackageReportId)
.FirstOrDefault()
.y1.SubmitDateTime.ToString(),
FlushingReportNumber =
grouping.Where(i => i.y1.Id == MaxFlush.FirstOrDefault().TestPackageReportId)
.FirstOrDefault()
.y1.ReportNumber,
ReInstatement = MaxReins.FirstOrDefault().Status,
ReInstatementSubmitDateTime =
grouping.Where(i => i.y1.Id == MaxReins.FirstOrDefault().TestPackageReportId)
.FirstOrDefault()
.y1.SubmitDateTime.ToString(),
ReInstatementReportNumber =
grouping.Where(i => i.y1.Id == MaxReins.FirstOrDefault().TestPackageReportId)
.FirstOrDefault()
.y1.ReportNumber,
Mono = MaxMono.FirstOrDefault().Status,
MonoSubmitDateTime =
grouping.Where(i => i.y1.Id == MaxMono.FirstOrDefault().TestPackageReportId)
.FirstOrDefault()
.y1.SubmitDateTime.ToString(),
MonoReportNumber =
grouping.Where(i => i.y1.Id == MaxMono.FirstOrDefault().TestPackageReportId)
.FirstOrDefault()
.y1.ReportNumber,
Variation = MaxVariation.FirstOrDefault().Status,
VariationSubmitDateTime =
grouping.Where(i => i.y1.Id == MaxVariation.FirstOrDefault().TestPackageReportId)
.FirstOrDefault()
.y1.SubmitDateTime.ToString(),
VariationReportNumber =
grouping.Where(i => i.y1.Id == MaxVariation.FirstOrDefault().TestPackageReportId)
.FirstOrDefault()
.y1.ReportNumber,
PAD = MaxPAD.FirstOrDefault().Status,
PADSubmitDateTime =
grouping.Where(i => i.y1.Id == MaxPAD.FirstOrDefault().TestPackageReportId)
.FirstOrDefault()
.y1.SubmitDateTime.ToString(),
PADReportNumber =
grouping.Where(i => i.y1.Id == MaxPAD.FirstOrDefault().TestPackageReportId)
.FirstOrDefault()
.y1.ReportNumber
}).ToList();
解决方案 You need pivoting and dynamic SQL. I would suggest to put output of your query into temporary table and then work with it:
USE YourDatabase --Use your database
SELECT TP.PackageNumber,
TP.Size,
TP.Code,
R.ReportNumber,
RD.Step,
RD.[Status],
R.SubmitDateTime,
RD.Id AS ReportDetailId
INTO #temporary --it will automatically create #temporary table with results
FROM dbo.TestPackages TP
INNER JOIN dbo.TestPackageReportDetails RD
ON TP.Id = RD.TestPackageId
INNER JOIN dbo.TestPackageReports R
ON RD.TestPackageReportId = R.Id
USE tempdb --switch to tempdb
SELECT TOP 1 WITH TIES * INTO #temp
FROM #temporary
ORDER BY ROW_NUMBER() OVER (PARTITION BY PackageNumber, Size, Code, Step ORDER BY ReportDetailId DESC)
--Here we get only rows with maximum ReportDetailID over PackageNumber, Size, Code, Step
DROP TABLE #temporary --get rid of #temporary, now we use #temp
DECLARE @pvt_columns nvarchar(max), --to store columns for pivoting
@unpvt_columns nvarchar(max), --to store columns that will be converted into 1 datatype for unpivoting
@columns nvarchar(max), -- columns comma separated
@sql nvarchar(max) --store query to run
SELECT @pvt_columns = COALESCE(@pvt_columns,'') + ','+QUOTENAME(Step+name)
FROM (
SELECT name
FROM sys.columns
WHERE object_id = OBJECT_ID(N'#temp')
AND name not in ('PackageNumber','Size','Code','Step')
) names
CROSS JOIN (
SELECT DISTINCT Step
FROM #temp
) steps
SELECT @unpvt_columns = COALESCE(@unpvt_columns,'')+',CAST('+QUOTENAME(name)+' as nvarchar(max)) '+QUOTENAME(name),
@columns = COALESCE(@columns,'') + ','+QUOTENAME(name)
FROM sys.columns
WHERE object_id = OBJECT_ID(N'#temp')
AND name not in ('PackageNumber','Size','Code','Step')
SELECT @sql = N'
SELECT *
FROM (
SELECT PackageNumber,
Size,
Code,
Step+[Columns] as [Columns],
[Values]
FROM (
SELECT PackageNumber,
Size,
Code,
Step'+@unpvt_columns+'
FROM #temp) t
UNPIVOT (
[Values] FOR [Columns] IN ('+STUFF(@columns,1,1,'')+')
) unpvt
) p
PIVOT (
MAX([Values]) FOR [Columns] in ('+STUFF(@pvt_columns,1,1,'')+')
) pvt'
EXEC sp_executesql @sql
DROP TABLE #temp
Output:
PackageNumber Size Code DryingReportDetailId DryingReportNumber DryingStatus DryingSubmitDateTime FlushingReportDetailId FlushingReportNumber FlushingStatus FlushingSubmitDateTime LineCheckReportDetailId LineCheckReportNumber LineCheckStatus LineCheckSubmitDateTime TestReportDetailId TestReportNumber TestStatus TestSubmitDateTime
1000220-G-02-TR 1,31 143 625 DR-0202 Accept 2013-12-12 745 AFP-AG-FL-0030 Accept 2015-12-12 220 LC-0131 Accept 2011-12-12 513 PT-0248 Accept 2012-12-12
You can SELECT
variables to see what is stored in them, and PRINT @sql
to see the full text of query.
这篇关于在SQL Server视图中有效地将行转换为列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!