Dynamic Pivot Queries with dynamic dates as column header in SQL Server
Try this query..it will help you
select * into #tempp from(
select '2016-01-17' as DATES,100 ORDERS,50 CANCELED_ORDERS
UNION ALL
SELECT '2016-01-18',120,20
UNION ALL
SELECT '2016-01-20',150,30
)AS A
--SELECT * FROM #tempp
declare @pivotcols nvarchar(max),@unpivotcols nvarchar(max),@SQLQUERY NVARCHAR(MAX)
select @pivotcols=stuff((select ','+quotename(dates) from #tempp for xml path('')),1,1,'')
--select @pivotcols
select @unpivotcols=stuff((select ','+name from tempdb.sys.columns where object_id =
object_id('tempdb..#tempp') and name<>'DATES' for xml path('')),1,1,'')
--select @unpivotcols
SET @SQLQUERY=N'select * from (
SELECT * FROM #tempp
)as a
unpivot (AMOUNTS FOR Dates in ('+@unpivotcols+N')
) AS UNPI
PIVOT (MAX(AMOUNTS) FOR DATES IN ('+@pivotcols+N')
)AS A'
PRINT @SQLQUERY
EXEC SP_EXECUTESQL @SQLQUERY
The output will be like.
+-----------------+------------+------------+------------+
| Dates | 2016-01-17 | 2016-01-18 | 2016-01-20|
+-----------------+------------+------------+------------+
| CANCELED_ORDERS | 50 | 20 | 30 |
| ORDERS | 100 | 120 | 150 |
+-----------------+------------+------------+------------+
Need to Dynamically Pivot Table Making Date Column New Table Headers
As you want multiple aggregation columns in your pivot you have to do the iterations for each aggregation value. I am using Sales as the table name. Please change the table name if it is different for you. Below query should give you the result you want.
DECLARE @cols AS NVARCHAR(MAX),
@Convertcols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@SQL AS NVARCHAR(MAX),
@PivotColumn Varchar(100),
@i Int = 1,
@AggColumn Varchar(100)
DECLARE @Columns TABLE (ID int IDENTITY(1,1), AccountInfo Varchar(max))
SELECT @cols = STUFF((SELECT ', '+ QUOTENAME(SalesDate)
from Sales
group by SalesDate
order by SalesDate
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SELECT @Convertcols = STUFF((SELECT ', CAST( '+ QUOTENAME(SalesDate) + ' AS VARCHAR(max)) AS ' + QUOTENAME(SalesDate)
from Sales
group by SalesDate
order by SalesDate
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
INSERT INTO @Columns
SELECT C.name
FROM SYS.COLUMNS C
INNER JOIN SYS.TABLES T ON C.OBJECT_ID = T.OBJECT_ID
WHERE T.NAME = 'Sales'
AND C.name <> 'SalesDate'
While @i <= (SELECT MAX(ID) FROM @Columns)
BEGIN
SELECT @AggColumn = AccountInfo FROM @Columns Where ID = @i
set @query = 'SELECT AccountInfo,' + @Convertcols +
' FROM
(
SELECT ''' + @AggColumn + ''' AS AccountInfo, SalesDate, '+ @AggColumn +'
FROM Sales
) X
PIVOT
(
Max('+ @AggColumn +')
FOR SalesDate in (' + @cols + ')
) P '
SET @SQL = CONCAT(@SQL, CHAR(10), CASE WHEN @i = 1 THEN '' ELSE 'UNION ' END, CHAR(10) , @query)
SET @i = @i+1
END
EXECUTE (@SQL)
Result:
How to pivot dynamically with date as column
Here is your sample table
SELECT * INTO #Names
FROM
(
SELECT 1 ID,'ITEM1' NAME
UNION ALL
SELECT 2 ID,'ITEM2' NAME
)TAB
SELECT * INTO #Stockdates
FROM
(
SELECT 1 ID,1 NAMEID,8 STOCK,'2-1-2014 ' [DATE]
UNION ALL
SELECT 2 ID,2 NAMEID,2 STOCK,'4-1-2014 ' [DATE]
)TAB
Put the join data to a temperory table
SELECT N.NAME,S.[DATE],S.STOCK
INTO #TABLE
FROM #NAMES N
JOIN #Stockdates S ON N.ID=S.NAMEID
Get the columns for pivot
DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',[' + CONVERT(NVARCHAR, [DATE], 106) + ']',
'[' + CONVERT(NVARCHAR, [DATE], 106) + ']')
FROM (SELECT DISTINCT [DATE] FROM #TABLE) PV
ORDER BY [DATE]
Now pivot it
DECLARE @query NVARCHAR(MAX)
SET @query = '
SELECT * FROM
(
SELECT * FROM #TABLE
) x
PIVOT
(
SUM(STOCK)
FOR [DATE] IN (' + @cols + ')
) p
'
EXEC SP_EXECUTESQL @query
And your result is here
SQL Server: Dynamic pivot with headers to include column name and date
In order to get both the expense and revenue columns as headers with the date, I would recommend applying both the UNPIVOT and the PIVOT functions.
The UNPIVOT will convert the expense and revenue columns into rows that you can append the date to. Once the date is added to the column names, then you can apply the PIVOT function.
The UNPIVOT code will be:
select id,
col+'_'+convert(varchar(10), date, 110) new_col,
value
from yt
unpivot
(
value
for col in (expense, revenue)
) un
See SQL Fiddle with Demo. This produces a result:
| ID | NEW_COL | VALUE |
-----------------------------------
| 1 | expense_12-31-2012 | 43 |
| 1 | revenue_12-31-2012 | 45 |
| 2 | expense_01-01-2013 | 32 |
As you can see the expense/revenue columns are now rows with a new_col that has been created by concatenating the date to the end. This new_col is then used in the PIVOT:
select id,
[expense_12-31-2012], [revenue_12-31-2012],
[expense_01-01-2013], [revenue_01-01-2013],
[expense_01-31-2013], [revenue_01-31-2013],
[expense_03-03-2013], [revenue_03-03-2013]
from
(
select id,
col+'_'+convert(varchar(10), date, 110) new_col,
value
from yt
unpivot
(
value
for col in (expense, revenue)
) un
) src
pivot
(
sum(value)
for new_col in ([expense_12-31-2012], [revenue_12-31-2012],
[expense_01-01-2013], [revenue_01-01-2013],
[expense_01-31-2013], [revenue_01-31-2013],
[expense_03-03-2013], [revenue_03-03-2013])
) piv;
See SQL Fiddle with Demo.
The above version will work great if you have a known number of dates to turn into columns but if you have an unknown number of dates, then you will want to use dynamic SQL to generate the result:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(c.col+'_'+convert(varchar(10), yt.date, 110))
from yt
cross apply
(
select 'expense' col union all
select 'revenue'
) c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT id,' + @cols + '
from
(
select id,
col+''_''+convert(varchar(10), date, 110) new_col,
value
from yt
unpivot
(
value
for col in (expense, revenue)
) un
) src
pivot
(
sum(value)
for new_col in (' + @cols + ')
) p '
execute(@query);
See SQL Fiddle with Demo. Both queries generate the same result.
Dynamic Pivot SQL Column Data to Header
dynamic sql
-- Build colums
DECLARE @cols NVARCHAR(MAX)
SELECT @cols = STUFF((
SELECT DISTINCT ',' + QUOTENAME([tabLabel])
FROM zDocusign_Document_Tab_Fields
FOR XML PATH('')
), 1, 1, '')
-- Selecting as FOR XML PATH will give you a string value with all of the fields combined
-- separated by comma. Stuff simply removes the first comma.
-- Quotename wraps the [tabLabel] value in brackets to allow for spaces in column name
-- You end up with
-- [City],[Gross Income],[Monthly Auto],[Monthly Mortgage/Rent],[Patient Phone],[Responsible Phone],[Street Address]
-- Build sql
DECLARE @sql NVARCHAR(MAX)
SET @sql = N'
SELECT ' + @cols +'
FROM zDocusign_Document_Tab_Fields
PIVOT (
MAX([value])
FOR [tabLabel] IN (' + @cols + ')
) p
'
-- Execute Sql
EXEC(@sql)
SQL Pivot - take a changing date from column and make it a row header
If this is MS SQL, you can use a dynamic pivot table. Here is a solution using your query (should work, but I don't have the base data to test it).
SELECT Database_Name,
FilingDate,
SUM( ISNULL(column1 ,0) +
ISNULL(column2],0) +
ISNULL([column3],0) +
ISNULL([column4],0)
) AS Total
INTO #T1
FROM SomeTable(NOLOCK)
GROUP BY Database_Name,
FilingDate
DECLARE @PivotColumnHeaders varchar(MAX)
SELECT @PivotColumnHeaders =
COALESCE(
@PivotColumnHeaders + ',[' + CAST(UC.FilingDate AS NVARCHAR(10)) + ']',
'[' + CAST(UC.FilingDate AS NVARCHAR(10)) + ']'
)
FROM (SELECT FilingDate FROM #T1 GROUP BY FilingDate) UC
DECLARE @PQuery varchar(MAX) = '
SELECT * FROM (SELECT Database_Name, FilingDate, Total FROM #T1 T0) T1
PIVOT (SUM([Total]) FOR FilingDate IN (' + @PivotColumnHeaders + ') ) AS P'
EXECUTE (@PQuery)
DROP TABLE #T1
Use pivot for dynamically changing column headers using sql in oracle
What you can do
SELECT *
FROM data
PIVOT
(
MAX(value) FOR label IN ('A' AS "A", 'B' AS "B",'C' AS "C",'D' AS "D")
)
WHERE ID = 120 AND app_id = 1
as a static pivot statement might be converted to a function which contains two respective parameters
CREATE OR REPLACE FUNCTION Get_Pivoted_Labels( i_id data.id%type, i_app_id data.app_id%type )
RETURN SYS_REFCURSOR IS
v_recordset SYS_REFCURSOR;
v_sql VARCHAR2(32767);
v_cols VARCHAR2(32767);
BEGIN
SELECT LISTAGG( ''''||label||''' AS "'||label||'"' , ',' )
WITHIN GROUP ( ORDER BY label )
INTO v_cols
FROM ( SELECT DISTINCT label
FROM data
WHERE ID = i_id AND app_id = i_app_id );
v_sql :=
'SELECT *
FROM data
PIVOT
(
MAX(value) FOR label IN ( '|| v_cols ||' )
)
WHERE ID = :id AND app_id = :aid';
OPEN v_recordset FOR v_sql USING i_id, i_app_id;
RETURN v_recordset;
END;
/
in which an auxiliary query, in which the label columns are distinctly selected, is used to determine the string(v_cols for 'A' AS "A", 'B' AS "B",'C' AS "C",'D' AS "D") to be concatenated to the main SQL string in order to be used within the cursor which returns a value of type SYS_REFCURSOR.
and is invoked by
VAR rc REFCURSOR
VAR v_id NUMBER
VAR v_app_id NUMBER
EXEC :rc := Get_Pivoted_Labels(:v_id,:v_app_id);
PRINT rc
from SQL developer's console.
Demonstration with generated SQL statements
If order of columns in the SELECT list matters, then use the code below in order to create the function
CREATE OR REPLACE FUNCTION Get_Pivoted_Labels( i_id data.id%type, i_app_id data.app_id%type )
RETURN SYS_REFCURSOR IS
v_recordset SYS_REFCURSOR;
v_sql VARCHAR2(32767);
v_cols_1 VARCHAR2(32767);
v_cols_2 VARCHAR2(32767);
BEGIN
SELECT LISTAGG( ''''||label||''' AS "'||label||'"' , ',' )
WITHIN GROUP ( ORDER BY label ),
LISTAGG( label , ',' )
WITHIN GROUP ( ORDER BY label )
INTO v_cols_1, v_cols_2
FROM ( SELECT DISTINCT label, value
FROM data
WHERE ID = i_id AND app_id = i_app_id );
v_sql :=
'SELECT ID, '|| v_cols_2 ||', app_id
FROM data
PIVOT
(
MAX(value) FOR label IN ( '|| v_cols_1 ||' )
)
WHERE ID = :id AND app_id = :aid';
OPEN v_recordset FOR v_sql USING i_id, i_app_id;
RETURN v_recordset;
END;
/
MSSQL dynamic pivot column values to column header
The problem with your current query is with the line:
MAX(SERVER_ID)
You want to display the PROPERTY_CHAR_VAL for each PROPERTY_NAME instead. The SERVER_ID will be a part of the final result as a column.
Sometimes when you are working with PIVOT is is easier to write the code first with the values hard-coded similar to:
select id, name1, name2, name3, name4
from
(
select id, property_name, property_value
from yourtable
) d
pivot
(
max(property_value)
for property_name in (name1, name2, name3, name4)
) piv;
See SQL Fiddle with Demo.
Once you have a version that has the correct logic, then you can convert it to dynamic SQL to get the result. This will create a sql string that will be executed and it will include all of your new columns names.
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(PROPERTY_NAME)
from yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT id, ' + @cols + '
from
(
select id, property_name, property_value
from yourtable
) x
pivot
(
max(property_value)
for property_name in (' + @cols + ')
) p '
execute sp_executesql @query;
See SQL Fiddle with Demo. Both will give a result:
| ID | NAME1 | NAME2 | NAME3 | NAME4 | NAME6 |
|----|--------|--------|--------|--------|--------|
| 1 | value | value | value | (null) | (null) |
| 2 | (null) | value | (null) | value | (null) |
| 3 | (null) | (null) | (null) | (null) | value |
Related Topics
How to Select All Values and Hide Null Values in SQL
T-Sql: Separate String into Multiple Columns
How to Grant the Database Owner (Dbo) the External Access Assembly Permission
Oracle SQL:Timestamps in Where Clause
SQL Query to Bring Last Letter in a String to First Letter Position
How to Convert Spark Schemardd into Rdd of My Case Class
Pivot a Table on a Value But Group the Data on One Line by Another
How to Find the Last Modified Date, Modified User of an Stored Procedure in SQL Server 2008
When to Use Grouping Sets, Cube and Rollup
Extract Sp and Ddl Scripts in Sybase Server
Need to Find Average Processing Time Between All Timestamp Records in Oracle SQL
SQL Server If Exists Then 1 Else 2
How to Determine Which Columns Are Shared Between Two Tables
Select All Records Don't Meet Certain Conditions in a Joined Table