happy 1001
asked on
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 -
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.
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 -
This query shown above works correctly and display the data output as shown in this snapshot -
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.
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)
GOThe [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.
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 #TemporaryTableZThis query shown above works correctly and display the data output as shown in this snapshot -
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Vitor Montalvão
happy1001, do you still need help with this question?
ASKER
Yes Victor, I am looking for that.
Thanks
Thanks
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.
ASKER
@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
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
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.