I need to present the month column data row wise corresponding to all the Fields([AC_Metrics As Euro],[AC_Metrics As Units],[AC_Metrics As LC],[BD_Metrics As Euro],[BD_Metrics As Units],[BD_Metrics As LC]). According to my requirement data should be appear like -
In the above image, data is same as the original data, but the presentation style of the data has been changed. In the above image all months from the row data became column header and other corresponding details (Euro and Units) became the value(total sum).
So my requirement has been full filled. I tried to pivot the SQL table but in different way.
I recognized that if there are two column then easily we can do the pivoting but when there are more than two columns then pivoting might be little bit difficult.
To implement this scenario i used pivoting after implementing the union and few temporary tables.
Step 1: Get all the data from the original table and insert into a temporary table.
SELECT * into #Temp1 from YourTable
Step 2: Select required data from the temporary table and implement union as below after that insert this data into an another temporary table -
Step 3: Declare variables as below -
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
Step 4: Assign the value to @cols and @query
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(MonthYear)
FOR XML PATH(''), TYPE
set @query = 'SELECT' + @cols + '
for MonthYear in (' + @cols + ')
) p '
Step 5: Execute the query and drop the temporary tables
drop table #Temp1
drop table #Temp2