如何将记录集列转换为行

I have a query whose code looks like this:

我有一个查询,其代码如下所示:

SELECT DocumentID, ComplexSubquery1 ... ComplexSubquery5
FROM   Document
WHERE  ...

ComplexSubquery are all numerical fields that are calculated using, duh, complex subqueries.

ComplexSubquery是使用复数子查询计算的所有数字字段。

I would like to use this query as a subquery to a query that generates a summary like the following one:

我想将此查询用作查询的子查询,该查询生成如下所示的摘要:

   Field          DocumentCount           Total
     1                 dc1                 s1
     2                 dc2                 s2
     3                 dc3                 s3
     4                 dc4                 s4
     5                 dc5                 s5

Where:

dc<n> = SUM(CASE WHEN ComplexSubquery<n> > 0 THEN 1                  END)
s <n> = SUM(CASE WHEN Field = n              THEN ComplexSubquery<n> END)

How could I do that in SQL Server?

我怎么能在SQL Server中这样做?

---

NOTE: I know I could avoid the problem by discarding the original query and using unions:

注意:我知道我可以通过丢弃原始查询和使用联合来避免此问题:

SELECT '1' AS TypeID,
       SUM(CASE WHEN ComplexSubquery1 > 0 THEN 1 END) AS DocumentCount
       SUM(ComplexSubquery1)                          AS Total
FROM   (SELECT DocumentID, BLARGH ... AS ComplexSubquery1) T
UNION ALL
SELECT '2' AS TypeID,
       SUM(CASE WHEN ComplexSubquery2 > 0 THEN 1 END) AS DocumentCount
       SUM(ComplexSubquery2)                          AS Total
FROM   (SELECT DocumentID, BLARGH ... AS ComplexSubquery2) T
UNION ALL
...

But I want to avoid this route, because redundant code makes my eyes bleed. (Besides, there is a real possibility that the number of complex subqueries grow in the future.)

但是我想避开这条路线,因为多余的代码让我的眼睛流血。 (此外,未来很有可能复杂子查询的数量增加。)

2 个解决方案

#1

WITH Document(DocumentID, Field) As
(
SELECT 1, 1 union all
SELECT 2, 1 union all
SELECT 3, 2 union all
SELECT 4, 3 union all
SELECT 5, 4 union all
SELECT 6, 5 union all
SELECT 7, 5
), CTE AS
(
SELECT DocumentID, 
       Field, 
       (select 10) As ComplexSubquery1,  
       (select 20) as ComplexSubquery2,
       (select 30) As ComplexSubquery3,  
       (select 40) as ComplexSubquery4,
       (select 50) as ComplexSubquery5
FROM   Document
)
SELECT Field, 
       SUM(CASE WHEN RIGHT(Query,1) = Field AND QueryValue  > 1 THEN 1 END ) AS DocumentCount,
       SUM(CASE WHEN RIGHT(Query,1) = Field THEN QueryValue END ) AS Total
FROM CTE
UNPIVOT  (QueryValue FOR Query IN 
      (ComplexSubquery1, ComplexSubquery2, ComplexSubquery3, 
       ComplexSubquery4, ComplexSubquery5)
)AS unpvt
GROUP BY Field

Returns

Field       DocumentCount Total
----------- ------------- -----------
1           2             20
2           1             20
3           1             30
4           1             40
5           2             100

#2

I'm not 100% positive from your example, but perhaps the PIVOT operator will help you out here? I think if you selected your original query into a temporary table, you could pivot on the document ID and get the sums for the other queries.

我不是100%肯定你的例子,但也许PIVOT运营商会帮助你在这里?我想如果您将原始查询选择到临时表中,您可以转移文档ID并获取其他查询的总和。

I don't have much experience with it though, so I'm not sure how complex you can get with your subqueries - you might have to break it down.

我对它没有多少经验,所以我不确定你的子查询有多复杂 - 你可能不得不打破它。