SQL Server - Change the Data Output

Hi

I am trying to create a query that will change the query output from Rows to Columns based on specific criteria.

For explaining the details, I have prepared a demo database -

CREATE DATABASE TestDB1001
GO

USE TestDB1001
GO



CREATE TABLE TestDB1001.dbo.Table1002 (
  xdate DATETIME2(0) NULL,
  symbol NVARCHAR(255) NULL,
  sector NVARCHAR(255) NULL,
  price_future_percent FLOAT NULL,
  oi_future_percent FLOAT NULL,

) ON [PRIMARY]
GO







SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO

INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-13 00:00:00.0000000', N'AXISBANK', N'Banking', 0.6, -0.15)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-13 00:00:00.0000000', N'DLF', N'Realty', 1, -0.16)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-13 00:00:00.0000000', N'ICICIBANK', N'Banking', 0.87, 0.77)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-13 00:00:00.0000000', N'UNITECH', N'Realty', 2.6, -0.17)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-14 00:00:00.0000000', N'AXISBANK', N'Banking', 0.09, -2.59)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-14 00:00:00.0000000', N'DLF', N'Realty', -0.82, 0.58)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-14 00:00:00.0000000', N'ICICIBANK', N'Banking', -0.9, -0.66)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-14 00:00:00.0000000', N'UNITECH', N'Realty', -1.27, 1.24)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-15 00:00:00.0000000', N'AXISBANK', N'Banking', 0.2, 1.78)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-15 00:00:00.0000000', N'DLF', N'Realty', 2.18, 2.7)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-15 00:00:00.0000000', N'ICICIBANK', N'Banking', 0.32, 0.49)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-15 00:00:00.0000000', N'UNITECH', N'Realty', 1.28, 3.93)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-16 00:00:00.0000000', N'AXISBANK', N'Banking', 4.07, 6.88)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-16 00:00:00.0000000', N'DLF', N'Realty', -0.68, 1.72)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-16 00:00:00.0000000', N'ICICIBANK', N'Banking', 0.9, 9.15)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-16 00:00:00.0000000', N'UNITECH', N'Realty', 0, 0.69)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-17 00:00:00.0000000', N'AXISBANK', N'Banking', -1.29, -4.54)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-17 00:00:00.0000000', N'DLF', N'Realty', -1.63, 2.56)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-17 00:00:00.0000000', N'ICICIBANK', N'Banking', -0.03, 1.1400000000000001)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-17 00:00:00.0000000', N'UNITECH', N'Realty', -2.5300000000000002, 1.77)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-20 00:00:00.0000000', N'AXISBANK', N'Banking', -0.07, -1.6400000000000001)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-20 00:00:00.0000000', N'DLF', N'Realty', -5.11, 11.54)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-20 00:00:00.0000000', N'ICICIBANK', N'Banking', -0.58, -1.85)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-20 00:00:00.0000000', N'UNITECH', N'Realty', -0.65, 0.79)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-21 00:00:00.0000000', N'AXISBANK', N'Banking', -2.56, -0.37)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-21 00:00:00.0000000', N'DLF', N'Realty', -2.12, 0.55)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-21 00:00:00.0000000', N'ICICIBANK', N'Banking', -1.56, -3.26)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-21 00:00:00.0000000', N'UNITECH', N'Realty', -8.5, -0.72)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-22 00:00:00.0000000', N'AXISBANK', N'Banking', 0.88, 3.51)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-22 00:00:00.0000000', N'DLF', N'Realty', 0.52, 3.13)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-22 00:00:00.0000000', N'ICICIBANK', N'Banking', 1.88, 3.6)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-22 00:00:00.0000000', N'UNITECH', N'Realty', 2.14, 0.36)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-23 00:00:00.0000000', N'AXISBANK', N'Banking', -1.13, 3.77)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-23 00:00:00.0000000', N'DLF', N'Realty', 0.94, 0.48)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-23 00:00:00.0000000', N'ICICIBANK', N'Banking', -1.17, 3.92)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-23 00:00:00.0000000', N'UNITECH', N'Realty', -1.4, 0.27)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-24 00:00:00.0000000', N'AXISBANK', N'Banking', -0.49, 11.96)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-24 00:00:00.0000000', N'DLF', N'Realty', -4.45, 7.75)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-24 00:00:00.0000000', N'ICICIBANK', N'Banking', -3.94, 17.73)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-24 00:00:00.0000000', N'UNITECH', N'Realty', -2.84, 1.44)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-27 00:00:00.0000000', N'AXISBANK', N'Banking', -4.3, 6.4)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-27 00:00:00.0000000', N'DLF', N'Realty', -1.8900000000000001, -0.69)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-27 00:00:00.0000000', N'ICICIBANK', N'Banking', -3.17, 5.82)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-27 00:00:00.0000000', N'UNITECH', N'Realty', -2.92, 4.71)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-28 00:00:00.0000000', N'AXISBANK', N'Banking', 0.97, 3.54)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-28 00:00:00.0000000', N'DLF', N'Realty', -5.49, 4.67)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-28 00:00:00.0000000', N'ICICIBANK', N'Banking', -2.06, 11.5)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-28 00:00:00.0000000', N'UNITECH', N'Realty', -1.5, 6.23)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-29 00:00:00.0000000', N'AXISBANK', N'Banking', 0.8, -3.4)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-29 00:00:00.0000000', N'DLF', N'Realty', 6.22, 1.43)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-29 00:00:00.0000000', N'ICICIBANK', N'Banking', 1.19, 1.4)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-29 00:00:00.0000000', N'UNITECH', N'Realty', -3.82, 9.95)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-30 00:00:00.0000000', N'AXISBANK', N'Banking', -0.1, -16.51)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-30 00:00:00.0000000', N'DLF', N'Realty', 7.8100000000000005, -16.54)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-30 00:00:00.0000000', N'ICICIBANK', N'Banking', 0.62, -14)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-30 00:00:00.0000000', N'UNITECH', N'Realty', 17.32, -35.2)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-31 00:00:00.0000000', N'AXISBANK', N'Banking', 1.4, 3.65)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-31 00:00:00.0000000', N'DLF', N'Realty', 4.66, 4.23)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-31 00:00:00.0000000', N'ICICIBANK', N'Banking', 4.08, 10.11)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-07-31 00:00:00.0000000', N'UNITECH', N'Realty', 2.68, 5.68)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-03 00:00:00.0000000', N'AXISBANK', N'Banking', 0.15, -2.07)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-03 00:00:00.0000000', N'DLF', N'Realty', 0.79, -0.31)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-03 00:00:00.0000000', N'ICICIBANK', N'Banking', 2.99, -12.11)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-03 00:00:00.0000000', N'UNITECH', N'Realty', 0.65, 2.51)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-04 00:00:00.0000000', N'AXISBANK', N'Banking', 0.73, -5.42)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-04 00:00:00.0000000', N'DLF', N'Realty', -0.48, -2)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-04 00:00:00.0000000', N'ICICIBANK', N'Banking', 0.46, -7.78)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-04 00:00:00.0000000', N'UNITECH', N'Realty', -1.95, -0.79)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-05 00:00:00.0000000', N'AXISBANK', N'Banking', -0.11, 0.23)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-05 00:00:00.0000000', N'DLF', N'Realty', 6.11, 6.36)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-05 00:00:00.0000000', N'ICICIBANK', N'Banking', -0.71, -1.8399999999999999)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-05 00:00:00.0000000', N'UNITECH', N'Realty', 4.64, 8.21)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-06 00:00:00.0000000', N'AXISBANK', N'Banking', 0.64, -1.48)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-06 00:00:00.0000000', N'DLF', N'Realty', 0.33, 2.32)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-06 00:00:00.0000000', N'ICICIBANK', N'Banking', -0.16, -0.43)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-06 00:00:00.0000000', N'UNITECH', N'Realty', 0.63, -0.2)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-07 00:00:00.0000000', N'AXISBANK', N'Banking', -0.72, -1.34)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-07 00:00:00.0000000', N'DLF', N'Realty', 2.64, 4.9)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-07 00:00:00.0000000', N'ICICIBANK', N'Banking', -0.32, 0.1)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-07 00:00:00.0000000', N'UNITECH', N'Realty', -1.26, 1.02)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-10 00:00:00.0000000', N'AXISBANK', N'Banking', -0.55, 0.64)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-10 00:00:00.0000000', N'DLF', N'Realty', 1.77, 1.37)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-10 00:00:00.0000000', N'ICICIBANK', N'Banking', -0.27, -0.95)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-10 00:00:00.0000000', N'UNITECH', N'Realty', 1.9100000000000001, 1.87)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-11 00:00:00.0000000', N'AXISBANK', N'Banking', -1.04, 1.22)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-11 00:00:00.0000000', N'DLF', N'Realty', -2.89, -6.9399999999999995)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-11 00:00:00.0000000', N'ICICIBANK', N'Banking', -2.22, 2.98)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-11 00:00:00.0000000', N'UNITECH', N'Realty', -2.5, -1.21)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-12 00:00:00.0000000', N'AXISBANK', N'Banking', -2.02, 5.46)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-12 00:00:00.0000000', N'DLF', N'Realty', -5.82, 1.63)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-12 00:00:00.0000000', N'ICICIBANK', N'Banking', -3.49, 4.46)
INSERT TestDB1001.dbo.Table1002(xdate, symbol, sector, price_future_percent, oi_future_percent) VALUES ('2015-08-12 00:00:00.0000000', N'UNITECH', N'Realty', -7.05, -1.81)
GO

Open in new window


The [price_future_percent] column data should be shown side by side, for each date, with grand total shown on the very right hand side.
Please see the attached snapshot to understand it better.
A-Required.png
The original table data is on the left hand side and the required output is on the right hand side in this snapshot.

Actually I am able to get the required output by using this query -

DECLARE @xdateValue DATETIME = '2015-08-01 00:00:00.000'  -- we are filtering the data based on xdate column here, because the original table has past data for many years, whereas we just need to do this work on past few days data only

CREATE TABLE #TemporaryTableZ(
	xdate					DateTime,
	sector					varchar(100),
	symbol					varchar(100),
	xdateFormat				varchar(100),
	price_future_percent	float,
	GrandTotal				float NULL
)

INSERT INTO #TemporaryTableZ
select xdate,sector,symbol,xdateFormat = DATENAME(MONTH,xdate) +' ' +CAST(DATEPART(DD,xdate) AS VARCHAR),price_future_percent,NULL
FROM Table1002 where xdate > @xdateValue

;WITH CTETotal AS(
	select symbol, GrandTotal = SUM(price_future_percent)
	FROM	Table1002 where xdate > @xdateValue	group by symbol
)

UPDATE	T
SET		T.GrandTotal = C.GrandTotal
FROM	#TemporaryTableZ T INNER JOIN CTETotal C ON T.symbol = C.symbol

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @PivotColumnName AS NVARCHAR(MAX)
 
--Get distinct values of the PIVOT Column 
SELECT @PivotColumnName= ISNULL(@PivotColumnName + ',','')
       + QUOTENAME(xdateFormat)
FROM (SELECT DISTINCT xdateFormat,xdate FROM #TemporaryTableZ) AS T ORDER BY T.xdate
 
--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT symbol, ' + @PivotColumnName + ',GrandTotal
    FROM (SELECT xdateFormat,symbol,price_future_percent,GrandTotal FROM #TemporaryTableZ) AS T
    PIVOT(SUM(price_future_percent) 
          FOR xdateFormat IN (' + @PivotColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

DROP TABLE #TemporaryTableZ

Open in new window


This query shown above works correctly and display the data output as shown in this snapshot -
B-Output.png
I am sure that there are much easier ways to do this type of work in SQL Server, which will not involve using any temporary tables etc. Can you please suggest some better approach for getting the desired output.

If you want any more explanations, then please ask and I will explain in greater details.

I have also attached the excel sheet having the original table data and the required output data format, so that it is easier for you to understand, what I am trying to do.
Pivot.xlsx

I am using Microsoft SQL Server Management Studio version-  12.0.2000.8, Microsoft Office 2013 x64 and Windows 7 x64

Thanks

PS: Please do not be confused by different DATES in the snapshots and excel file etc. Because that part is non important, as we can dynamically change the Date Values according to any specific requirements for the query.

And also note that the sample table "Table1002" in this example is different from the sample table "Table1001" that I used in my previous questions.
happy 1001Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MlandaTCommented:
You need to use the SQL PIVOT command. Your query will look like this:
select symbol, sector, [2015-07-13 00:00:00], [2015-07-14 00:00:00], [2015-07-15 00:00:00], [2015-07-16 00:00:00], [2015-07-17 00:00:00], [2015-07-20 00:00:00], [2015-07-21 00:00:00], [2015-07-22 00:00:00], [2015-07-23 00:00:00], [2015-07-24 00:00:00], [2015-07-27 00:00:00], [2015-07-28 00:00:00], [2015-07-29 00:00:00], [2015-07-30 00:00:00], [2015-07-31 00:00:00], [2015-08-03 00:00:00], [2015-08-04 00:00:00], [2015-08-05 00:00:00], [2015-08-06 00:00:00], [2015-08-07 00:00:00], [2015-08-10 00:00:00], [2015-08-11 00:00:00], [2015-08-12 00:00:00]
from 
	(select symbol, sector, xdate, price_future_percent from TestDB1001.dbo.Table1002) as data
pivot (
	MAX(price_future_percent)
	FOR xdate IN ([2015-07-13 00:00:00], [2015-07-14 00:00:00], [2015-07-15 00:00:00], [2015-07-16 00:00:00], [2015-07-17 00:00:00], [2015-07-20 00:00:00], [2015-07-21 00:00:00], [2015-07-22 00:00:00], [2015-07-23 00:00:00], [2015-07-24 00:00:00], [2015-07-27 00:00:00], [2015-07-28 00:00:00], [2015-07-29 00:00:00], [2015-07-30 00:00:00], [2015-07-31 00:00:00], [2015-08-03 00:00:00], [2015-08-04 00:00:00], [2015-08-05 00:00:00], [2015-08-06 00:00:00], [2015-08-07 00:00:00], [2015-08-10 00:00:00], [2015-08-11 00:00:00], [2015-08-12 00:00:00])
) p

Open in new window

Yes, you need to hard-code the values to pivot on. So we rather make it dynamic:
declare @fields varchar(max), @sql varchar(max)
select @fields = isnull(@fields + ', ', '') + QUOTENAME(xdate) from (select distinct xdate from TestDB1001.dbo.Table1002) t order by xdate

print @fields + char(10)

select @sql = 'select symbol, sector, ' + @fields + '
from 
	(select symbol, sector, xdate, price_future_percent from TestDB1001.dbo.Table1002) as data
pivot (
	MAX(price_future_percent)
	FOR xdate IN (' + @fields + ')
) p'

print ( @sql )
exec ( @sql )

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
>>"I am sure that there are much easier ways to do this type of work in SQL Server"
mmmm, not really

SQL is great with dynamic rows, it does that without problem, but it isn't built for dynamic columns, particularly with dynamic column headings. So the approach you are already using is the general approach needed which is to generate the SQL dynamically to deal with the dynamic columns and headings.

Now, your current approach is using a temp table, and whilst these are NOT things I like to use, here they are useful because you do need to reference the data a couple of times (e.g. to get the column headings, then to get the final outcome). You might be able to use a CTE instead, you could try it perhaps.

The only saving I'm suggesting below is that you don't need the update step:
DECLARE @xdateValue datetime = '20150801'  -- we are filtering the data based on xdate column here, because the original table has past data for many years, whereas we just need to do this work on past few days data only

CREATE TABLE #TemporaryTableZ (
      xdate datetime
    , sector varchar(100)
    , symbol varchar(100)
    , xdateFormat varchar(100)
    , price_future_percent float
    , GrandTotal float NULL
)

INSERT INTO #TemporaryTableZ
      SELECT
            xdate
          , sector
          , symbol
          , xdateFormat = DATENAME(MONTH, xdate) + ' ' + CAST(DATEPART(DD, xdate) AS varchar)
          , price_future_percent
          , SUM(price_future_percent) OVER(PARTITION BY symbol) AS GrandTotal
      FROM Table1002
      WHERE xdate > @xdateValue


DECLARE @DynamicPivotQuery AS nvarchar(max)
DECLARE @PivotColumnName AS nvarchar(max)

--Get distinct values of the PIVOT Column 
SELECT
      @PivotColumnName = ISNULL(@PivotColumnName + ',', '')
      + QUOTENAME(xdateFormat)
FROM (
      SELECT DISTINCT
            xdateFormat
          , xdate
      FROM #TemporaryTableZ
) AS T
ORDER BY T.xdate

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery =
N'SELECT symbol, ' + @PivotColumnName + ',GrandTotal
    FROM (SELECT xdateFormat,symbol,price_future_percent,GrandTotal FROM #TemporaryTableZ) AS T
    PIVOT(SUM(price_future_percent) 
          FOR xdateFormat IN (' + @PivotColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

DROP TABLE #TemporaryTableZ
;

Open in new window

oh, and you may use YYYYMMDD as a safe way to declare a date (at time 00:00:00.00000)
Vitor MontalvãoMSSQL Senior EngineerCommented:
happy1001, do you still need help with this question?
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

happy 1001Author Commented:
Yes Victor, I am looking for that.
Thanks
MlandaTCommented:
No feedback was given on the proposed solutions though. Did they work? Did you try them? Your feedback helps get issues resolved the way you want. We might purpose something along the lines of what you need, but before we commit to working out a complete solution, we may need your feedback along the way.
happy 1001Author Commented:
@MlandaT
I am sorry for not commenting earlier. That solution will become too complex when we will add multiple columns and different variations of the display. If we necessarily need to hard-code, as you have shown, then I will have to avoid it.

Thanks and regards
MlandaTCommented:
You will notice that I gave a second example which makes it all dynamic, so that you do not have to hardcode anything. You can read the comments and check out the code already posted.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.