Skip to main content

Pivot Multiple Columns in sql server


MY table name  product1

ID    Amount      Date
----
1    300     02-02-2010 00:00
 2    400     02-02-2009 00:00
 3    200     02-02-2011 00:00
 4    300     22-02-2010 00:00
 5    400     12-02-2009 00:00
 6    500     22-02-2009 00:00
 7    600     02-02-2006 00:00
 8    700     02-07-2012 00:00
 9    500     08-02-2012 00:00
10    800     09-02-2011 00:00
11    500     06-02-2010 00:00
12    600     01-02-2011 00:00
13    300     02-02-2019 00:00

Desired output:
   Y1          Y2            Y3 ...........
 sum(amount)   sum(amount)   sum(amount)
What is an approach, where Y1 is the year part of the date, such that the result column would be the following?
YEAR       2006   2009    2010   2011   2012
-   ---------
Total         600   1300     800   1900   1200

 Create procedure [dbo].[pivot]  
 as  
 begin  
 DECLARE @Years nvarchar(max)  
 SELECT @Years = STUFF(  
 (  
 select distinct ',[' + cast(Year([Dates]) as nvarchar(4)) + ']'  
 from Product1  
 for xml path('')  
 ),  
 1,1,'')  
 DECLARE @SQL nvarchar(max)  
 SELECT @SQL = N'  
 select * from ( select amount, year([Dates]) as [y] from product1 ) Data  
 PIVOT ( sum(Amount) FOR [y] IN ( ' + @Years + ' ) ) PivotTable  
 UNION  
 select * from ( select units, year([Dates]) as [y] from product1 ) Data  
 PIVOT ( sum(units) FOR [y] IN ( ' + @Years + ' ) ) PivotTable  
 '  
 EXECUTE (@SQL)  
 end  

Popular posts from this blog

Resolved : Power BI Report connection error during execution

Getting Below Power BI Report connection error during execution . Error: Something went wrong Unable to connect to the data source undefined. Please try again later or contact support. If you contact support, please provide these details. Underlying error code: -2147467259 Table: Business Sector. Underlying error message: AnalysisServices: A connection cannot be made. Ensure that the server is running. DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259 Microsoft.Data.Mashup.ValueError.DataSourceKind: AnalysisServices Microsoft.Data.Mashup.ValueError.DataSourcePath: 10.10.10.60;T_CustomerMaster_ST Microsoft.Data.Mashup.ValueError.Reason: DataSource.Error Cluster URI: WABI-WEST-EUROPE-redirect.analysis.windows.net Activity ID: c72c4f12-8c27-475f-b576-a539dd81826a Request ID: dfb54166-c78f-4b40-779f-e8922a6687ad Time: 2019-09-26 10:03:29Z Solution: We found report connection not able to connect to SQL Analysis service so tried below option. ...

Song- Khamoshiyan Piano keyboard Chord,Notation and songs Lyrics

Song Aankhen Khuli Ho lyrics notation

Song : Aankhen Khuli Ho Movie: Mohabbatein Notes used : W=>Western - C D E F G- A- B-/ H=>Hindustani - S R G M P- D- N- ( Here for western, G=G-, A=A-, & B=B- ) ( For hindustani, P=P-, D=D-, & N=N- ) Song I : Aankhen Khuli...Ho Ya.. Ho Bandh W=> A.... C... B..C.. E.. E...... A... A.... H=> D... S... N..S.. G G....... D... D.... Deedaar Un Ka Ho.o.taa Hai.. W=> A...B....A....D.BAG....ADB... H=> D...N...D.....R.NDP...DRN... Kaise Kahoon Main O..Yaaraa W=> B..D.. D....E.... D.....C..C..C... H=> N..R.. R....G... R.....S..S..S..... Ye Pyaar Kaise Hota Hai W=> E...B.....DB...AG...B..AA H=> G...N....RN...DP...N...DD (Tururu ru ru, ru ru rururu ru......) W=> AA...GA...BCE..., B...DB..GA H=> DD...PD...NSG..., N..RN.. PD Song II: Aa.aj He Kisi..par Yaa.ro.on..., Marke De..Khe..gein Hum W=> E....FEDCBABC.D.. D D......., G A B C.... E.......D...D..... H=> G....MGRSNDNS.R. R R......., P D N S.....G........R...R.... Pyaar Ho...