将行转置为 sql 中的列 [英] transpose rows to columns in sql
问题描述
我在使用 SQL 查询获取所需输出时遇到问题.
I have problem in getting the desired output with the SQL query.
我的sql数据如下:
TOTAL Charge PAYMNET A B C D E MonthYear
------- ----------- ----------- --------- -------- ---------- ------- ------- ----------
661 157832.24 82967.80 700.00 10.70 58329.33 0.00 0.00 Oct-2013
612 95030.52 17824.28 850.00 66.10 53971.41 0.00 0.00 Nov-2013
584 90256.35 16732.91 700.00 66.10 52219.87 0.00 0.00 Dec-2013
511 72217.32 12336.12 285.00 53.17 42951.12 0.00 0.00 Jan-2014
我需要如下输出，
Data Jan-2013 Feb-2013 Mar-2013
TOTALCOUNT 761 647 671
Charge 126888 119995 151737.5
Payment 25705.4 26235.47 28704.41
A 1089.08 1020 745
B 2100.4 1947.25 1868.22
C 94246.55 84202.15 115673.7
D 0 0 0
E 0 0 0
我已经看到了 pivot 和 unpivot 的例子，在 pivot 中我没有将列标题作为行数据，而在unpivot 我没有找到可以转置多列的示例.我还有另一个选择可以在代码中获得这个结果.但是我想知道在sql中是否有可能得到这种结果?
I have seen the examples of pivot and unpivot, in pivot I don't get the column headers as row data, and in unpivot I didn't found an example where I can transpose multiple columns. I have another option to get this result in the code. But I want to know is it possible to get this kind of result in sql?
编辑
结果只会给出 3 或 4 个月，不会更多.
The result will give only for 3 or 4 months, not more than that.
更新 :第一个示例数据是我将在多个表上进行多次连接和分组后获得的实际数据，我将这些数据存储到临时表中.我试图通过修改由于表结构而无法实现的查询来获得所需的结果.我设法得到了第一个示例数据中的结果，但这不是客户想要看到的！！！因此，我需要将只有 3 到 4 行的临时表数据处理为所需的输出.获得第一个结果的查询是select * from temp.需要对temp表结果进行处理.
Update : The first sample data is the actual data which I will get as a result of multiple joins and grouping on multiple tables, which I will store into a temp table. I tried to get the required result by modifying the query which is not possible because of the table structure. I managed to get the result as in the first sample data, but this is not what the client want to see!!! So I need to process the temp table data which will be only 3 to 4 rows into required output. The query to get the first result is select * from temp. The processing needs to be done on temp table result.
更新 2
我尝试了以下查询
declare @cols varchar(max)
select @cols = STUFF((select ', ' + MonthYear
from #tmp for xml path('')),1,1,'')
declare @query varchar(max)
set @query =
'select ''TOTAL'' as Data,' +@cols+' from
(select MonthYear,TOTALCLAIMS from #tmp)st
pivot
(
MAX(TOTAL) for MonthYear in (' + @cols + ')
)pt;'
哪个正确地给了我第一行！！！但我尝试使用 union 作为
Which gave me the first row correctly!!! But I tried to use union as
set @query =
'select ''TOTAL'' as Data,' +@cols+' from
(select MonthYear,TOTALCLAIMS from #tmp)st
pivot
(
MAX(TOTAL) for MonthYear in (' + @cols + ')
)pt;
union
select ''CHARGES'' as Data,' +@cols+' from
(select MonthYear,TOTALCLAIMS from #tmp)st
pivot
(
MAX(CHARGES) for MonthYear in (' + @cols + ')
)pt;'
由于联合附近的语法不正确而导致错误.有谁知道如何联合 pivot 结果?或者有没有更好的方法来做到这一点?
Which gives an error as incorrect syntax near union. Any one know how to union pivot results? Or is there any better way to do this?
谢谢.
推荐答案
我已经尝试过这段代码.请检查并让我知道它是否有效
I have tried this code. Please check and let me know if it works
我知道它看起来不太好.也不确定它的性能如何.
I know that it doesnt look so good. Also not sure how it will be performance wise.
--Can have more columns like A,B,...
DECLARE @tbl TABLE
(
TOTAL INT,
CHARGE FLOAT,
PAYMENT FLOAT,
MONTHYEAR VARCHAR(50)
)
--Test data
INSERT INTO @tbl SELECT 661, 157832.24, 82967.80, 'Oct2013'
INSERT INTO @tbl SELECT 612, 95030.52, 17824.28, 'Nov2013'
INSERT INTO @tbl SELECT 584 ,90256.35, 16732.91, 'Dec2013'
--Can be a physical table
CREATE TABLE #FinalTbl
(
DATA VARCHAR(100)
)
--inserted hardcode records in data column. To add it dynamically you would need to loop through information_schema.columns
--SELECT *
--FROM information_schema.columns
--WHERE table_name = 'tbl_name'
INSERT INTO #FinalTbl
VALUES ('TOTAL')
INSERT INTO #FinalTbl
VALUES ('CHARGE')
INSERT INTO #FinalTbl
VALUES ('PAYMENT')
DECLARE @StartCount INT, @TotalCount INT, @Query VARCHAR(5000), @TOTAL INT,@CHARGE FLOAT,@PAYMENT FLOAT,@MONTHYEAR VARCHAR(50)
SELECT @TotalCount = COUNT(*) FROM @tbl;
SET @StartCount = 1;
WHILE(@StartCount <= @TotalCount)
BEGIN
SELECT @TOTAL = TOTAL,
@CHARGE = CHARGE,
@PAYMENT = PAYMENT,
@MONTHYEAR = MONTHYEAR
FROM
(SELECT ROW_NUMBER() over(ORDER BY MONTHYEAR) AS ROWNUM, * FROM @tbl) as tbl
WHERE ROWNUM = @StartCount
SELECT @Query = 'ALTER TABLE #FinalTbl ADD ' + @MONTHYEAR + ' VARCHAR(1000)'
EXEC (@Query)
SELECT @Query = 'UPDATE #FinalTbl SET ' + @MONTHYEAR + ' = ''' + CONVERT(VARCHAR(50), @TOTAL) + ''' WHERE DATA = ''TOTAL'''
EXEC (@Query)
SELECT @Query = 'UPDATE #FinalTbl SET ' + @MONTHYEAR + ' = ''' + CONVERT(VARCHAR(50), @CHARGE) + ''' WHERE DATA = ''CHARGE'''
EXEC (@Query)
SELECT @Query = 'UPDATE #FinalTbl SET ' + @MONTHYEAR + ' = ''' + CONVERT(VARCHAR(50), @PAYMENT) + ''' WHERE DATA = ''PAYMENT'''
EXEC (@Query)
SELECT @StartCount = @StartCount + 1
END
SELECT * FROM #FinalTbl
DROP TABLE #FinalTbl
希望能帮到你
这篇关于将行转置为 sql 中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
