如何在结果集中转置表数据? [英] How to transpose table data in result set?

问题描述

我的表格数据如下:
Invoice_id; Invoice_Number; Service_Head;金额

My Table Data as follows:
Invoice_id ;Invoice_Number ;Service_Head; Amount

1;	            1          AF 	       1200
2;                  1	       Excise_Duty     10000
3	            1	       Deumorages      1500
4	            2	       AF	       1500
5	            2	       Excise_Duty     12000
6	            2	       Deumorages      2000

我希望SQL查询获得以下结果集:

I want SQL query to get following Result Set:

Invoice_Number	;AF;	Excise_Duty;	Deumorages   ; Total
1	         1200;  10000	          1500	       12701
2	         1500;  12000	          2000	       15502

请建议我...
谢谢
Sekhar C

please suggest me...
Thanks
Sekhar C

推荐答案

我认为您正在寻找PIVOT.试试这个..
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/ [

Hi ,

I think you are looking for PIVOT. Try this..
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/[^]

我想我解决了.请通过下面的代码

Hi I think I solved it. Please go through the following code

DECLARE @listCol VARCHAR(2000)
SELECT @listCol=
STUFF(( SELECT DISTINCT '],[' + ltrim(rtrim([Service_Head]) )
FROM Invoice ORDER BY '],[' + ltrim(rtrim([Service_Head]))
FOR XML PATH('') ),1,2, '') + ']'

-- construct dyanamic query
DECLARE @query VARCHAR(MAX) ='SELECT * FROM (SELECT Invoice_Number,[Service_Head]
Service_Head,SUM(Amount) amt,
(SELECT SUM(Amount) FROM INVOICE WHERE Invoice_Number = I.Invoice_Number Group By
Invoice_Number) As Total
FROM Invoice I GROUP BY Invoice_Number,Service_Head ) as src
PIVOT ( SUM(amt) FOR Service_Head IN  ('+@listCol+'))as pvt '

-- execute query
execute(@query)

谢谢

Thank you

您的要求类似于

Your requirement is similar to SQLite Select data from multiple rows returned as one row[^].
Please check the accepted answer how it does the task with the help of COALESCE and write query as per your requirement.

I can''t write or test the query right now for you, as I don''t have the management studio, otherwise I would have done it for you.

Try to implement this and let me know...

Thanks...

这篇关于如何在结果集中转置表数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！