問題描述
我在使用 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 中的列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！