SQL Server : Pivot with custom column names
There are a few ways that you can do this.
If you had a known number of questions/answers then you could use row_number() along with an aggregate function and a CASE expression:
select id,
max(case when rn = 1 then question end) question1,
max(case when rn = 1 then answer end) answer1,
max(case when rn = 2 then question end) question2,
max(case when rn = 2 then answer end) answer2,
max(case when rn = 3 then question end) question3,
max(case when rn = 3 then answer end) answer3
from
(
select id, question, answer,
row_number() over(partition by id order by id, question) rn
from yt
) src
group by id;
See SQL Fiddle with Demo
Another suggestion would be to use both the UNPIVOT and the PIVOT function to get the result. The UNPIVOT will take your question and answer columns and convert them into multiple rows.
The basic syntax for the UNPIVOT will be:
select id,
col+cast(rn as varchar(10)) col,
value
from
(
-- when you perform an unpivot the datatypes have to be the same.
-- you might have to cast the datatypes in this query
select id, question, cast(answer as varchar(500)) answer,
row_number() over(partition by id order by id, question) rn
from yt
) src
unpivot
(
value
for col in (question, answer)
) unpiv;
See Demo. This gives a result:
| ID | COL | VALUE |
--------------------------------------------------------------
| 4482515 | question1 | I would like to be informed by mail. |
| 4482515 | answer1 | No |
| 4482515 | question2 | Plan to Purchase? |
| 4482515 | answer2 | Over 12 months |
| 4482515 | question3 | Test Question Text |
| 4482515 | answer3 | some Answer |
As you can see, I added a row_number() value to the initial subquery so you can associate each answer to the question. Once this has been unpivoted, then you can pivot the result on the new column names with the question/answer with the concatenated row number value. The code with the PIVOT syntax will be:
select id, question1, answer1, question2, answer2,
question3, answer3
from
(
select id,
col+cast(rn as varchar(10)) col,
value
from
(
-- when you perform an unpivot the datatypes have to be the same.
-- you might have to cast the datatypes in this query
select id, question, cast(answer as varchar(500)) answer,
row_number() over(partition by id order by id, question) rn
from yt
) src
unpivot
(
value
for col in (question, answer)
) unpiv
) d
pivot
(
max(value)
for col in (question1, answer1, question2, answer2,
question3, answer3)
) piv;
See SQL Fiddle with Demo. Now in your situation, you stated that you will have a dynamic number of questions/answers. If that is the case, then you will need to use dynamic SQL to get the result:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(c.col+cast(rn as varchar(10)))
from
(
select row_number() over(partition by id
order by id, question) rn
from yt
) d
cross apply
(
select 'question' col, 1 sort union all select 'answer', 2
) c
group by col, rn, sort
order by rn, sort
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT id, ' + @cols + '
from
(
select id,
col+cast(rn as varchar(10)) col,
value
from
(
-- when you perform an unpivot the datatypes have to be the same.
-- you might have to cast the datatypes in this query
select id, question, cast(answer as varchar(500)) answer,
row_number() over(partition by id order by id, question) rn
from yt
) src
unpivot
(
value
for col in (question, answer)
) unpiv
) d
pivot
(
max(value)
for col in (' + @cols + ')
) p '
execute(@query);
See SQL Fiddle with Demo. These give a result:
| ID | QUESTION1 | ANSWER1 | QUESTION2 | ANSWER2 | QUESTION3 | ANSWER3 |
------------------------------------------------------------------------------------------------------------------------------------
| 4482515 | I would like to be informed by mail. | No | Plan to Purchase? | Over 12 months | Test Question Text | some Answer |
Pivoting rows to columns with custom column names in SQL Server
You can use row_number() and conditional aggregation:
SELECT t1.Message, a.*
FROM Table1 t1 CROSS APPLY
(SELECT MAX(CASE WHEN seqnum = 1 THEN value END) as greeting1,
MAX(CASE WHEN seqnum = 2 THEN value END) as greeting2,
MAX(CASE WHEN seqnum = 3 THEN value END) as greeting3,
MAX(CASE WHEN seqnum = 4 THEN value END) as greeting4,
MAX(CASE WHEN seqnum = 5 THEN value END) as greeting5,
MAX(CASE WHEN seqnum = 6 THEN value END) as greeting6
FROM (SELECT s.value,
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as seqnum
FROM STRING_SPLIT(t1.Message,'"')
WHERE value LIKE '%.%'
) s
) s;
Note: In practice, this will probably preserve the ordering of the values. However that is not guaranteed -- based on the documentation.
T-SQL Pivot with Custom Column names
Easy way for this is to use group by, sum and case -- like so:
SELECT UserName,
SUM(CASE WHEN Job_Func = 'DEV' THEN 1 ELSE 0 END) AS Developer,
SUM(CASE WHEN Job_Func = 'NET_ENG' THEN 1 ELSE 0 END) AS NetworkEngineer,
SUM(CASE WHEN Job_Func = 'QA' THEN 1 ELSE 0 END) AS QA,
SUM(CASE WHEN Job_Func = 'SUPERVISOR' THEN 1 ELSE 0 END) AS Supervisor
FROM TABLE_NAME
GROUP BY UserName
If you only want 1 even when a job function is listed more than once you can use MAX instead of SUM
Get custom column name while doing pivot in SQL Server
It is a simple query and you don't need a sub-query or CTE:
just remember Pivot will rename the From table to Pivot Table [e.g. Col] :
select
name,
Col.[1] as amountval1,
Col.[2] as amountval2
from bank
PIVOT (sum(amount) For Val in ([1],[2])) Col
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...
Dynamic Column Names with Pivot SQL
Your stored procedure looks like it's incomplete or you've changed certain things, but I believe you're looking for something like the following:
DECLARE @ProgramList VARCHAR(MAX);
SELECT @ProgramList = STUFF((SELECT ',' + QUOTENAME(ServerName) FROM [Servers] ORDER BY ServerURL FOR XML PATH ('')), 1, 1, '');
DECLARE @sql VARCHAR(MAX);
SELECT @sql = '
SELECT *
FROM
(
SELECT P.Name,
P.Entity,
P.Field,
S.ServerName
FROM ProjectFields AS P
JOIN [Servers] AS S
ON S.ServerURL = P.ServerURL
) AS P
PIVOT
(
MIN(Field) FOR ServerName IN (' + @ProgramList + ')
) AS PIV
ORDER BY PIV.Name;';
--PRINT @sql;
EXEC sp_executesql @sql;
SQL Server Dynamic Pivot Column Names
DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',[' + COLUMN_NAME + ']',
'[' + COLUMN_NAME + ']')
FROM (SELECT DISTINCT COLUMN_NAME,INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION O
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'CODES') PV
ORDER BY O
DECLARE @query NVARCHAR(MAX)
SET @query = '
SELECT TOP 0 * FROM
(
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ''CODES''
) x
PIVOT
(
MIN(COLUMN_NAME)
FOR [COLUMN_NAME] IN (' + @cols + ')
) p
'
EXEC SP_EXECUTESQL @query
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 |
Change column name while using PIVOT SQL Server 2008
Use AS to give a column alias.
SELECT EmployeeID,
[DD14C4C2-FC9E-4A2E-9B96-C6A20A169B2E] AS Name,
[BE8149E2-0806-4D59-8482-58223C2F1735] AS Age,
[23B2C459-3D30-41CA-92AE-7F581F2535D4] AS Salary
FROM EmployeeAttributes PIVOT ( MAX(VALUE) FOR AttributeID IN (
[DD14C4C2-FC9E-4A2E-9B96-C6A20A169B2E],
[BE8149E2-0806-4D59-8482-58223C2F1735],
[23B2C459-3D30-41CA-92AE-7F581F2535D4]) ) P
Related Topics
Oracle Rows to Column Transformation
How to Get Only One Row Per Record in Master Table
Postgresql 9.4 - Prevent App Selecting Always the Latest Updated Rows
Select Unique Column Names from a List of Joined Tables
Is It Better to Do an Equi Join in the from Clause or Where Clause
Using Indexes in JSON Array in Postgresql
How to Call a User Defined Function to Use with Select, Group By, Order By
Insert into Values with Where Clause
Performance and Readability of Regexp_Substr VS Instr and Substr
Transpose Select Results with Oracle
Row_Number Simulation in SQL Server 2000
How to Count the Number of Words in a String in Oracle
Different Ways to Alias a Column