如何获得以下输出 [英] How to Get The Following Output

问题描述

我的表

My Table

Month	Year	Dealer	Dealer_Code	City	 Active	Points
9	2012	Jaswant Motors	10000	Jalandhar  1	500
9	2012	Speedways	10001	Amritsar	   1	1300
9	2012	Jaswant Motors	10000	Jalandhar  1	279
9	2012	Speedways	10001	Amritsar	   1	88

所需输出

Desired Output

Month	Dealer	                             Service_Points	manth	year
Sep-12	Jaswant Motors - 10000 - Jalandhar 	779	          9	2012
Sep-12	Speedways - 10001 - Amritsar	        1388	          9	2012
Sep-12	Total	                                  2167	         [123]	[1234] 
Total	Jaswant Motors - 10000 - Jalandhar	         779	         [234]	[2345] 
Total	Speedways - 10001 - Amritsar	        1388	         [234]	[2345]
Total	Total	                                  2167	         [234]	[345]

注意： - 方括号中的值（[]）是虚数值

推荐答案

嗨..



这是 http://msdn.microsoft.com/ en-us / library / bb522495（v = sql.105）.aspx [ ^ ]

可以帮到你。



谢谢你。

Hi..

This http://msdn.microsoft.com/en-us/library/bb522495(v=sql.105).aspx[^]
can help you.

Thank you.

试试下面的sql

Try below sql

select convert( varchar(3) , DATENAME(MM,DATEADD(mm,Month,-1)) ) + '-' + right (convert(varchar,Year ),2) as Month ,Dealer ,Service_Points, manth, year from TableName

你将不得不将其分解为多个查询。



首先将总数放入与输出匹配的临时表中，所有字段当你混合数据类型时，必须是varchar，这是一个非常糟糕的想法。



然后你选择你的详细记录并使用 UNION 到临时表，你可能想要添加一个ID列，以确保你可以正确排序数据。



这是一个特别愚蠢的查询如总计应该在您的消费应用程序中完成。

You are going to have to break this up into multiple queries.

First get the totals into a temp table matching your output, all fields will have to be varchar as you are mixing data types, a really crappy idea.

Then you select your detail records and use a UNION to the temp table, you may want to add an ID column to insure you can sort the data correctly.

This is a particularly silly query as the totalling should be done in your consuming application.

这篇关于如何获得以下输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！