需要在SQL Server 2008中转置数据.如何实现此目的? [英] Need to transpose data in SQL server 2008. How do you achieve this?
问题描述
DECLARE @LOAN_BALANCE DECIMAL,
@ZMONTH INT,
@ZYEAR INT,
@OLEM DECIMAL
SET @ZMONTH=5
SET @ZYEAR =2015
;with cte as
(
SELECT CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT) as COMPS ,
INT_ARREARS+PRIN_ARREARS AS TOTS,
IDNO,AC_BALANCE,LOANUMBER,CUSTOMER
FROM HLOANS where DEDUCT >0 and INT_ARREARS+PRIN_ARREARS>0
AND CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT) >0
AND CMONTH=@ZMONTH AND CYEAR=@ZYEAR
)
SELECT CUSTOMER AS 'CUSTOMER' ,
AC_BALANCE AS 'LOAN BALANCE',
CASE WHEN COMPS BETWEEN 1 AND 3 THEN CAST('1-3' AS Varchar(8))
WHEN COMPS BETWEEN 4 AND 6 THEN '4-6'
ELSE 'The Rest' END AS [Grouping],
TOTS AS 'TOTS' ,
00.00 AS 'OLEM' ,
00.00 AS 'SUB-STANDARD' ,
00.00 AS 'LOSS'
FROM cte
ORDER BY CASE WHEN COMPS BETWEEN 1 AND 3 THEN CAST('1-3' AS Varchar(8))
WHEN COMPS BETWEEN 4 AND 6 THEN '4-6'
WHEN COMPS BETWEEN 7 AND 12 THEN '7-12'
ELSE 'The Rest' END
-------------------------------------------------- -----------------------------------
结果
-------------------------------------------------- -----------------------------------
-------------------------------------------------------------------------------------
outcome
-------------------------------------------------------------------------------------
CUSTOMER LOAN BALANCE Grouping TOTS OLEM SUB-STANDARD
----------------------------------------------------------------------------
CALTECH 7676710.46 1-3 171631.26 0.00 0.00
DANNEX 3058630.15 4-6 49452.68 0.00 0.00
PLOT 4150208.33 1-3 470323.78 0.00 0.00
请求
-------
预期结果
request
-------
Expected Outcome
CUSTOMER LOAN BALANCE Grouping TOTS OLEM SUB-STANDARD
----------------------------------------------------------------------------
CALTECH 7676710.46 1-3 171631.26 171631.26 0.00
DANNEX 3058630.15 4-6 49452.68 0.00 49452.68
PLOT 4150208.33 1-3 470323.78 470323.78 0.00
组''1-3''与''OLEM''
一起使用
组''4-6''与''SUB-STANDARD''
一起使用
请调整上面的Select语句，以实现期望的
我尝试过的事情:
检查了Internet并检查了一些sql语句，但都无济于事.
Group ''1-3'' goes with ''OLEM''
GROUP ''4-6'' goes with ''SUB-STANDARD''
Please adjust the Select Statement above so as to achieve the Expected
What I have tried:
Checked the internet and reviewed some sql statements, all to no avail.
推荐答案
我的第一个想法是引入第二个CTE将分组分配给行和因此，使使用GROUP BY更加容易.例如.
My first thought with this was to introduce a second CTE to assign the groupings to the rows and therefore make it easier to use a GROUP BY. E.g.
DECLARE @ZMONTH INT = 5
DECLARE @ZYEAR INT = 2015
;with cte as
(
SELECT CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT) as COMPS ,
INT_ARREARS+PRIN_ARREARS AS TOTS,
IDNO,AC_BALANCE,LOANNUMBER,CUSTOMER
FROM HLOANS
where DEDUCT >0 and INT_ARREARS+PRIN_ARREARS>0
AND CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT) >0
AND CMONTH=@ZMONTH AND CYEAR=@ZYEAR
), cte2 as -- This CTE is just to get the grouping in place
(
SELECT CUSTOMER, AC_BALANCE,
CASE WHEN COMPS BETWEEN 1 AND 3 THEN CAST('1-3' AS Varchar(8))
WHEN COMPS BETWEEN 4 AND 6 THEN '4-6'
ELSE 'The Rest' END AS [Grouping],
TOTS
FROM cte
)
-- This query is to get the actual data required
SELECT CUSTOMER, SUM(AC_BALANCE) as [LOAN BALANCE], [Grouping],
SUM(TOTS) AS TOTS,
OLEM = CASE WHEN [Grouping] = '1-3' THEN SUM(TOTS) ELSE 0.0 END,
[SUB-STANDARD] = CASE WHEN [Grouping] = '4-6' THEN SUM(TOTS) ELSE 0.0 END
FROM cte2
GROUP BY CUSTOMER, [Grouping]
ORDER BY [Grouping]
一种替代方法是将分组放在单独的表中，然后可以将其作为简单JOIN的一部分.我在这里使用了TABLE变量，但是它可以很容易地成为数据库中的临时表或永久表.
An alternative approach would be to have the groupings in a separate table which can then be part of a simple JOIN. I''ve used a TABLE variable here but it could just as easily be a temporary table or even a permanent table on the database.
DECLARE @ZMONTH INT = 5
DECLARE @ZYEAR INT = 2015
DECLARE @Groupings TABLE (comp int, title varchar(30))
DECLARE @maxComp int = (select CAST(MAX((INT_ARREARS+PRIN_ARREARS)/DEDUCT) AS INT) FROM HLOANS)
;WITH q AS
(
SELECT 1 AS num
UNION ALL
SELECT num + 1
FROM q
WHERE num < @maxComp
)
INSERT INTO @Groupings
SELECT num, CASE WHEN num BETWEEN 1 AND 3 THEN CAST('1-3' AS Varchar(8))
WHEN num BETWEEN 4 AND 6 THEN '4-6'
ELSE 'The Rest' END AS [Grouping]
FROM q
;with cte as
(
SELECT CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT) as COMPS ,
INT_ARREARS+PRIN_ARREARS AS TOTS,
IDNO,AC_BALANCE,LOANNUMBER,CUSTOMER
FROM HLOANS
where DEDUCT >0 and INT_ARREARS+PRIN_ARREARS>0
AND CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT) >0
AND CMONTH=@ZMONTH AND CYEAR=@ZYEAR
)
SELECT CUSTOMER, SUM(AC_BALANCE) as [LOAN BALANCE], g.title AS [Grouping],
SUM(TOTS) AS TOTS,
OLEM = CASE WHEN G.[title] = '1-3' THEN SUM(TOTS) ELSE 0.0 END,
[SUB-STANDARD] = CASE WHEN G.title= '4-6' THEN SUM(TOTS) ELSE 0.0 END
FROM cte
INNER JOIN @Groupings G ON cte.COMPS = g.comp
GROUP BY CUSTOMER, G.title
ORDER BY G.title
第一位(q)是递归CTE，它仅生成数字1到最大可能的COMP值，并为每个数字赋予其所属的分组的标题.我的测试数据产生了
The first bit (q) is a recursive CTE that just generates the numbers 1 through to the maximum possible value of COMP and gives each of those numbers the title of the grouping that it falls into. My test data produced
num title
1 1-3
2 1-3
3 1-3
4 4-6
5 4-6
该查询的其余部分与原始查询大致相同，但直接连接到该数字列表.这样，通过临时表的标题对GROUP进行操作非常容易，而不是在GROUP BY和ORDER BY子句中使用CASE语句.
这应该比替代方法快一些，因为所有字符处理都已从主查询中删除，并且每个可能的值仅计算"一次.
The rest of the query is more or less the same as your original query but with a straight-forward JOIN to that list of numbers. It''s then very easy to GROUP by the title from the temporary table rather than having CASE statements in the GROUP BY and ORDER BY clauses.
This should run a little faster than the alternative as all of the character handling has been taken out of the main query and is only "calculated" once per possible value.
这篇关于需要在SQL Server 2008中转置数据.如何实现此目的?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
