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 |
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)
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 |
+-----------------+------------+------------+------------+
SQL PIVOT with dynamic header
You can use this query.
DECLARE @ColNames NVARCHAR(MAX) = ''
SELECT @ColNames = @ColNames + ', ' + QUOTENAME( DUN_ID ) FROM MyTable
GROUP BY DUN_ID
DECLARE @SqlText NVARCHAR(MAX) = '
SELECT * FROM (SELECT MEA_DateTime, PTR_ID, EXP_ID, DUN_ID, MEA_Value FROM MyTable ) SRC
PIVOT( MAX(MEA_Value) FOR DUN_ID IN ( '+ STUFF(@ColNames,1,1,'') +') ) AS PVT ORDER BY PTR_ID, EXP_ID'
EXEC(@SqlText)
Result:
MEA_DateTime PTR_ID EXP_ID 2 3 4
----------------------- ----------- ----------- ---------- -------- --------
2009-08-10 00:00:00.000 24 14 15.10 14.30 16.70
2009-08-10 00:00:10.000 24 15 13.00 NULL 13.40
2009-08-10 00:00:20.000 24 16 17.80 17.70 16.20
2009-08-10 00:00:00.000 25 14 NULL 34.00 19.00
2009-08-10 00:00:10.000 25 15 22.10 23.10 NULL
2009-08-10 00:00:20.000 25 16 24.60 18.30 18.20
Creating Dynamic Pivot Table Column Names
Your result is correct above however, you will not have your columns sorted by the correct date...
The below would cater for that:
DECLARE
@Cols1 VARCHAR(MAX),
@Cols2 VARCHAR(MAX),
@Query VARCHAR(MAX),
@Period VARCHAR(MAX) = -13 ; --/ Select number of months to view back on (excluding current month) \--
declare @tmptbl table (PeriodDate datetime, col1 varchar(100), col2 varchar(100))
insert into @tmptbl (PeriodDate, col1, col2)
SELECT DISTINCT dPeriodDate,'ISNULL('+ QUOTENAME(CONCAT(DATENAME(MONTH, dPeriodDate),' ',YEAR(dPeriodDate)))+',0) AS' + QUOTENAME(CONCAT(DATENAME(MONTH, dPeriodDate),' ',YEAR(dPeriodDate))) col1
, QUOTENAME(CONCAT(DATENAME(MONTH, dPeriodDate),' ',YEAR(dPeriodDate))) col2
FROM _bvSTTransactionsFull AS S join _etblPeriod p on EOMONTH(s.TxDate) = p.dPeriodDate
WHERE S.Module = 'AR' AND S.TxDate > = DATEADD(MONTH, -13, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
SELECT
@Cols1 = STUFF((SELECT ',' + col1
FROM @tmptbl order by PeriodDate
FOR XML PATH (''), TYPE).value('.','NVARCHAR(MAX)'),1,1,'') --/ allows the first SELECT fields to have the ISNULL function and Alias \--
SELECT
@Cols2 = STUFF((SELECT ',' + col2
FROM @tmptbl order by PeriodDate
FOR XML PATH (''), TYPE).value('.','NVARCHAR(MAX)'),1,1,'') --/ becasue of the need for ISNULL above, second @Cols needed for the Pivot (pivot cannot have ISNULL in it) \--
SELECT
@Query =
'SELECT
Item_Code_Desc,
'+@Cols1+'
FROM
(SELECT
CONCAT(ST.Code,'' - '', ST.Description_1) AS Item_Code_Desc,
STT.ActualQuantity AS Qty,
CONCAT(DATENAME(MONTH, STT.TxDate),'' '',YEAR(STT.TxDate)) AS [Period]
FROM
_bvSTTransactionsFull AS STT
INNER JOIN
StkItem AS ST ON STT.AccountLink = ST.StockLink
WHERE
STT.TxDate >= DATEADD(MONTH, '+@Period+', DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
AND STT.Module = ''AR'') AS P
PIVOT
(SUM(P.Qty)
FOR P.Period IN ('+@cols2+')
) AS PVT '
PRINT @Query
EXEC (@Query)
I left the changes I made in small caps for you to see the changes...
SQL Server Pivots: Displaying row values to column headers
There are two ways of doing this with static SQL and dynamic SQL:
Static Pivot:
SELECT P.ItemNo, IsNull(P.[1], 0) as Wk1, IsNull(P.[2], 0) as Wk2
, IsNull(P.[3], 0) as Wk3, IsNull(P.[4], 0) as Wk4
, IsNull(P.[5], 0) as Wk5, IsNull(P.[6], 0) as Wk6
, IsNull(P.[7], 0) as Wk7, IsNull(P.[8], 0) as Wk8
, IsNull(P.[9], 0) as Wk9
FROM
(
SELECT ItemNo, WeekNo, [Value]
FROM dbo.Items
) I
PIVOT
(
SUM([Value])
FOR WeekNo IN ([1], [2], [3], [4], [5], [6], [7], [8], [9])
) as P
Dynamic Pivot:
DECLARE
@cols AS NVARCHAR(MAX),
@y AS INT,
@sql AS NVARCHAR(MAX);
-- Construct the column list for the IN clause
SET @cols = STUFF(
(SELECT N',' + QUOTENAME(w) AS [text()]
FROM (SELECT DISTINCT WeekNo AS W FROM dbo.Items) AS W
ORDER BY W
FOR XML PATH('')),
1, 1, N'');
-- Construct the full T-SQL statement
-- and execute dynamically
SET @sql = N'SELECT *
FROM (SELECT ItemNo, WeekNo, Value
FROM dbo.Items) AS I
PIVOT(SUM(Value) FOR WeekNo IN(' + @cols + N')) AS P;';
EXEC sp_executesql @sql;
GO
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.
Related Topics
Phpmyadmin - Total Record Count Varies
How to Prevent SQL Injection in Wordpress
Insert Multiple Rows in One Table Based on Number in Another Table
Executing Ssis Package with SQL Authentication
Get Every Hour for a Time Range
Escaping Strings Containing Single Quotes in Powershell Ready for SQL Query
Ms Access: How to Count Distinct Value Using Access Query
What Does It Mean to Have Jobs with a Null Stop Date
Ssis - Performing a Lookup on Another Table to Get Related Column
How to Re-Use Result for Select, Where and Order by Clauses
Unique Constraint Controlled by a Bit Column
Reverse- Geocoding: How to Determine the City Closest to a (Lat,Lon) with Bigquery SQL
Postgresql - Order by an Array
Sqlserver Case Expressions - Short Circuit Evaluation
SQL Query That Distinguishes Between ß and Ss