我不知道如何在标题中得到我想要做的事情,任何帮助都会很感激。
问题来了:
我有一个大的数据集,目前超过108k行5列。我试图以一种特殊的方式显示它,使它看起来类似于它在Excel中当前数据透视表中的样子。我已经将数据导入到MSSQL中,这里有一个示例:
State Project ClassOfPlant Description ProjApprovalDate
FL 4139904 TR 2016 CO161 OA341 SPECIAL SERVICES BLANKET 2016-10-11
FL 4144128 TR WSSD RWB M6 GPON CARD ADDITION TO SUPPORT GROWTH 2016-10-11
FL 4145813 OP BRND-RBB-FTTP-GFLD-CROSSINGS-FISHHAWK RANCH W PH4B 2016-10-11
FL 4146018 OP LKLDN-TMF-GFLD 56 SFU DONOVAN RD ESTATES DESIGN 2016-10-11
当我返回结果时,我希望它看起来是这样的:
State ClassOfPlant Project Description ProjApprovalDate
FL
FL TR
FL 4139904 2016 CO161 OA341 SPECIAL SERVICES BLANKET 10/11/2016
FL 4144128 WSSD RWB M6 GPON CARD ADDITION TO SUPPORT GROWTH 10/11/2016
FL OP
FL 4145813 BRND-RBB-FTTP-GFLD-CROSSINGS-FISHHAWK RANCH W PH4B 10/11/2016
FL 4146018 LKLDN-TMF-GFLD 56 SFU DONOVAN RD ESTATES DESIGN 10/11/2016
会有额外的格式,比如每列和每行周围的线条以及日期的不同格式,但现在这并不重要。我只需要找出如何提取数据,使其看起来正确。
编辑
我不能使用SSRS,我把它放在我创建的一个报告网站上。我已经能够通过PHP获得我需要的东西,但它需要很长时间才能加载,而且我无法限制使用分页的行数。我的希望是,我可以使用单个查询,然后使用分页制作多个页面,并加快页面的加载速度。
创建嵌套集的一种非常直接的方法是XML:
——Mockup-table
DECLARE @tbl TABLE([State] VARCHAR(100), Project INT, ClassOfPlant VARCHAR(100),[Description] VARCHAR(100),ProjApprovalDate DATE);
INSERT INTO @tbl VALUES
('FL',4139904,'TR','2016 CO161 OA341 SPECIAL SERVICES BLANKET',{d'2016-10-11'})
,('FL',4144128,'TR','WSSD RWB M6 GPON CARD ADDITION TO SUPPORT GROWTH',{d'2016-10-11'})
,('FL',4145813,'OP','BRND-RBB-FTTP-GFLD-CROSSINGS-FISHHAWK RANCH W PH4B',{d'2016-10-11'})
,('FL',4146018,'OP','LKLDN-TMF-GFLD 56 SFU DONOVAN RD ESTATES DESIGN',{d'2016-10-11'});
——查询
SELECT t1.[State] AS [@value]
,(
SELECT t2.ClassOfPlant AS [@value]
,(
SELECT t3.Project AS [@value]
,t3.[Description] AS [@desription]
,t3.ProjApprovalDate AS [@apporval_date]
FROM @tbl AS t3
WHERE t3.[State]=t1.[State] AND t3.ClassOfPlant=t2.ClassOfPlant
FOR XML PATH('project'),TYPE
)
FROM @tbl AS t2
WHERE t1.[State]=t2.[State]
GROUP BY t2.ClassOfPlant
FOR XML PATH('plant'),TYPE
)
FROM @tbl AS t1
GROUP BY t1.[State]
FOR XML PATH('state'),ROOT('root')
结果<root>
<state value="FL">
<plant value="OP">
<project value="4145813" desription="BRND-RBB-FTTP-GFLD-CROSSINGS-FISHHAWK RANCH W PH4B" apporval_date="2016-10-11" />
<project value="4146018" desription="LKLDN-TMF-GFLD 56 SFU DONOVAN RD ESTATES DESIGN" apporval_date="2016-10-11" />
</plant>
<plant value="TR">
<project value="4139904" desription="2016 CO161 OA341 SPECIAL SERVICES BLANKET" apporval_date="2016-10-11" />
<project value="4144128" desription="WSSD RWB M6 GPON CARD ADDITION TO SUPPORT GROWTH" apporval_date="2016-10-11" />
</plant>
</state>
</root>
更新可以从XML中创建结果表,如下所示
SELECT CASE WHEN nd.value('local-name(.)','nvarchar(max)')=N'state' THEN nd.value('@value','nvarchar(max)') END AS [State]
,CASE WHEN nd.value('local-name(.)','nvarchar(max)')=N'plant' THEN nd.value('@value','nvarchar(max)') END AS [ClassOfPlant]
,CASE WHEN nd.value('local-name(.)','nvarchar(max)')=N'project' THEN nd.value('@value','nvarchar(max)') END AS [Project]
,CASE WHEN nd.value('local-name(.)','nvarchar(max)')=N'project' THEN nd.value('@desription','nvarchar(max)') END AS [Description]
,CASE WHEN nd.value('local-name(.)','nvarchar(max)')=N'project' THEN nd.value('@apporval_date','date') END AS [ProjApprovalDate]
FROM @xml.nodes('//*[local-name(.)!="root"]') AS A(nd)
结果State ClassOfPlant Project Description ProjApprovalDate
FL NULL NULL NULL NULL
NULL OP NULL NULL NULL
NULL NULL 4145813 BRND-RBB ... 2016-10-11
NULL NULL 4146018 LKLDN-TMF... 2016-10-11
NULL TR NULL NULL NULL
NULL NULL 4139904 2016 CO16... 2016-10-11
NULL NULL 4144128 WSSD RWB ... 2016-10-11
如果需要,使用ISNULL
返回空字符串而不是NULL
…
SQL CLI工具并不是真正为整齐地表示数据而设计的。您可能会发现,在SQL中尝试这样做会带来更多的麻烦。对于一个快速的解决方案,我会考虑使用R来格式化数据。