Understanding PIVOT function in T-SQL
A PIVOT used to rotate the data from one column into multiple columns.
For your example here is a STATIC Pivot meaning you hard code the columns that you want to rotate:
create table temp
(
id int,
teamid int,
userid int,
elementid int,
phaseid int,
effort decimal(10, 5)
)
insert into temp values (1,1,1,3,5,6.74)
insert into temp values (2,1,1,3,6,8.25)
insert into temp values (3,1,1,4,1,2.23)
insert into temp values (4,1,1,4,5,6.8)
insert into temp values (5,1,1,4,6,1.5)
select elementid
, [1] as phaseid1
, [5] as phaseid5
, [6] as phaseid6
from
(
select elementid, phaseid, effort
from temp
) x
pivot
(
max(effort)
for phaseid in([1], [5], [6])
)p
Here is a SQL Demo with a working version.
This can also be done through a dynamic PIVOT where you create the list of columns dynamically and perform the PIVOT.
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.phaseid)
FROM temp c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT elementid, ' + @cols + ' from
(
select elementid, phaseid, effort
from temp
) x
pivot
(
max(effort)
for phaseid in (' + @cols + ')
) p '
execute(@query)
The results for both:
ELEMENTID PHASEID1 PHASEID5 PHASEID6
3 Null 6.74 8.25
4 2.23 6.8 1.5
Convert Rows to columns using 'Pivot' in SQL Server
If you are using SQL Server 2005+, then you can use the PIVOT function to transform the data from rows into columns.
It sounds like you will need to use dynamic sql if the weeks are unknown but it is easier to see the correct code using a hard-coded version initially.
First up, here are some quick table definitions and data for use:
CREATE TABLE yt
(
[Store] int,
[Week] int,
[xCount] int
);
INSERT INTO yt
(
[Store],
[Week], [xCount]
)
VALUES
(102, 1, 96),
(101, 1, 138),
(105, 1, 37),
(109, 1, 59),
(101, 2, 282),
(102, 2, 212),
(105, 2, 78),
(109, 2, 97),
(105, 3, 60),
(102, 3, 123),
(101, 3, 220),
(109, 3, 87);
If your values are known, then you will hard-code the query:
select *
from
(
select store, week, xCount
from yt
) src
pivot
(
sum(xcount)
for week in ([1], [2], [3])
) piv;
See SQL Demo
Then if you need to generate the week number dynamically, your code will be:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(Week)
from yt
group by Week
order by Week
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT store,' + @cols + ' from
(
select store, week, xCount
from yt
) x
pivot
(
sum(xCount)
for week in (' + @cols + ')
) p '
execute(@query);
See SQL Demo.
The dynamic version, generates the list of week numbers that should be converted to columns. Both give the same result:
| STORE | 1 | 2 | 3 |
---------------------------
| 101 | 138 | 282 | 220 |
| 102 | 96 | 212 | 123 |
| 105 | 37 | 78 | 60 |
| 109 | 59 | 97 | 87 |
How to write T-SQL query to Pivot data as below?
Using Jiacheng Gao's idea of using MAX() for pivot and some MSDN documentation, I came up with the following query :
SELECT id,empID,Eclass AS EType,[StartTime] AS StartTime,[EndTime] AS EndTime, [Type Code] AS [Type Code]
FROM
(SELECT id,empID,Eclass,Code,Etype
FROM DATA ) AS SourceTable
PIVOT
(
MAX(Etype)
FOR Code IN ([StartTime],[EndTime],[Type Code])
) AS PivotTable;
You can try it on the following fiddle.
How to pivot without knowing fixed columns in T-SQL
Demo on db<>fiddle
You can use ROW_NUMBER() to mark the number of values, e.g: 1, 2, 3.
Note that: ORDER BY [Balance] DESC to get the generated value as you wish.
DECLARE
@columns NVARCHAR(MAX) = '',
@sql NVARCHAR(MAX) = '';
SELECT Customer, Balance, Col = ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY [Balance] DESC)
into #b
FROM #a
SELECT @columns += QUOTENAME(Col) + ','
from (SELECT DISTINCT Col FROM #b) A
-- remove the last comma
SET @columns = LEFT(@columns, LEN(@columns) - 1);
SET @sql = 'SELECT * FROM ( SELECT Customer, Balance, Col FROM #b) src PIVOT( MAX([Balance]) FOR Col IN ('+ @columns +')) AS pivot_table;';
-- execute the dynamic SQL
EXECUTE sp_executesql @sql;
Output
Updated
Since concatenating strings is undocumented and unreliable. It does not always work as expected. So you should resolve with 2 solutions below
- Use STRING_AGG (From SQL Server 2017 and late)
SELECT STRING_AGG(QUOTENAME(Col), ', ')
from (SELECT DISTINCT Col FROM #b) A
// Output: [1], [2], [3]
- Use XML Extensions
DECLARE @columns NVARCHAR(MAX) = ''
SELECT @columns = (
SELECT QUOTENAME(Col) + ', '
FROM (SELECT DISTINCT Col FROM #b) A
FOR XML PATH(''), TYPE
).value('.','varchar(max)')
SELECT @columns
// Output: [1], [2], [3],
Thanks @GarethD's comment. Check it out on db<>fiddle
SQL Server: Examples of PIVOTing String data
Remember that the MAX aggregate function will work on text as well as numbers. This query will only require the table to be scanned once.
SELECT Action,
MAX( CASE data WHEN 'View' THEN data ELSE '' END ) ViewCol,
MAX( CASE data WHEN 'Edit' THEN data ELSE '' END ) EditCol
FROM t
GROUP BY Action
How do I pivot my data using a T-SQL query
A simple PIVOT should do the trick
Select *
From YourTable
Pivot ( max( Custom_Value ) for Custom_Label in ( [Classification],[Trade] ) ) pvt
EDIT -
Just in case you have extra columns not listed above... you need to "feed" the pivot with only the required columns.
Select *
From ( Select Description
,Status
,Issue_number
,Custom_Label
,Custom_Value
From YourTable
) src
Pivot ( max( Custom_Value ) for Custom_Label in ( [Classification],[Trade] ) ) pvt
GROUP BY statement with a PIVOT command in T-SQL
Use conditional aggregation. It is much simpler:
SELECT [Load ID],
MIN(CASE WHEN [Sequence] = 1 THEN [Stop Zip] END) as [1],
MIN(CASE WHEN [Sequence] = 2 THEN [Stop Zip] END) as [2],
MIN(CASE WHEN [Sequence] = 3 THEN [Stop Zip] END) as [3],
MIN(CASE WHEN [Sequence] = 4 THEN [Stop Zip] END) as [4],
FROM TMS_Load_Stops
GROUP BY [Load ID];
The PIVOT doesn't work because you have additional columns in the table being pivoted. I just don't like the syntax or how it works, but you can also fix it by only selecting the columns you need:
SELECT [Load ID], [1], [2], [3], [4]
FROM (SELECT [Load ID], [Sequence], [Stop Zip]
FROM TMS_Load_Stops
) ls
PIVOT (
MIN([Stop Zip])
for [Sequence] IN ([1],[2],[3],[4])
) PivotTable
;
T-SQL :: List all tables, columns and pivot content
Updated to support 2016
DROP TABLE IF EXISTS #ColumnsToDisplay
SELECT ROW_NUMBER () OVER (ORDER BY tab.name) AS Iteration,
s.name AS SchemaName,
tab.name AS table_name,
col.column_id,
col.name AS column_name,
t.name AS data_type,
col.max_length,
col.precision AS PrecisionNumber,
CAST(NULL AS VARCHAR(MAX)) AS DataSample
INTO #ColumnsToDisplay
FROM sys.tables AS tab
JOIN sys.schemas AS s
ON s.schema_id = tab.schema_id
JOIN sys.columns AS col
ON col.object_id = tab.object_id
LEFT JOIN sys.types AS t
ON col.user_type_id = t.user_type_id
DECLARE @Iterations INT = 0,
@CurrentIteration INT = 1;
SELECT @Iterations = MAX (Iteration)
FROM #ColumnsToDisplay
WHILE @CurrentIteration <= @Iterations
BEGIN
DECLARE @CurrentTableName VARCHAR(100) = '',
@CurrentColumnName VARCHAR(100) = '',
@DynamicQuery NVARCHAR(1000) = N''
DECLARE @Sample VARCHAR(MAX)
SET @CurrentTableName = '';
SET @DynamicQuery = N'';
SELECT @CurrentTableName = CONCAT (ttq.SchemaName, '.', ttq.table_name),
@CurrentColumnName = ttq.column_name
FROM #ColumnsToDisplay AS ttq
WHERE ttq.Iteration = @CurrentIteration
IF (@CurrentTableName = '')
BEGIN
SET @CurrentIteration += 1
CONTINUE
END
-- SQL Server 2019
-- SET @DynamicQuery = CONCAT (N'
-- SELECT @Sample = STRING_AGG(t.ColumnData,'', '')
-- FROM (
-- SELECT TOP 5 CAST(x.[', @CurrentColumnName, '] AS VARCHAR(MAX)) AS ColumnData
-- FROM ', @CurrentTableName, ' AS x
-- WHERE x.[', @CurrentColumnName, '] IS NOT NULL
-- )t')
-- SQL Server 2016 and lower where Stuff is supported
SET @DynamicQuery = CONCAT (N'
SELECT @Sample = STUFF((SELECT '', ''+ t.ColumnData
FROM (
SELECT TOP 5 CAST(x.[', @CurrentColumnName, '] AS VARCHAR(MAX)) AS ColumnData
FROM ', @CurrentTableName, ' AS x
WHERE x.[', @CurrentColumnName, '] IS NOT NULL
) AS t
FOR XML PATH('''')),1,1,'''')')
EXECUTE sys.sp_executesql @DynamicQuery,
N'@Sample VARCHAR(MAX) OUTPUT',
@Sample = @Sample OUTPUT
UPDATE #ColumnsToDisplay
SET DataSample = @Sample
WHERE Iteration = @CurrentIteration
SET @CurrentIteration += 1
END
SELECT ctd.Iteration,
ctd.SchemaName,
ctd.table_name,
ctd.column_id,
ctd.column_name,
ctd.data_type,
ctd.max_length,
ctd.PrecisionNumber,
ctd.DataSample
FROM #ColumnsToDisplay AS ctd
T-SQL PIVOT data from long form to wide by a date
The traditional cross tab / conditional aggregation version of a pivot() would be like so:
select
id
, date
, v28 = sum(case when num = 28 then value end)
, v29 = sum(case when num = 29 then value end)
, v30 = sum(case when num = 30 then value end)
from t
group by id, date
pivot() version:
select
Id
, date
, v28
, v29
, v30
from
(select id, date, num = 'v'+convert(varchar(10),num), value
from t) as t
pivot (sum(value) for num in (v28, v29, v30)) pvt
dynamic pivot code generation:
declare @cols nvarchar(max);
declare @sql nvarchar(max);
select @cols = stuff((
select distinct
', ' + 'v'+convert(varchar(10),num)
from t
order by 1
for xml path (''), type).value('.','nvarchar(max)')
,1,2,'')
select @sql = '
select Id, date, ' + @cols + '
from (
select Id, date, num = ''v''+convert(varchar(10),num), value
from t
) as t
pivot (sum([value]) for [num] in (' + @cols + ') ) p'
select @sql
exec(@sql);
generates the following:
select Id, date, v28, v29, v30
from (
select Id, date, num = 'v'+convert(varchar(10),num), value
from t
) as t
pivot (sum([value]) for [num] in (v28, v29, v30) ) p
test them all here: http://rextester.com/ZJS18834
results (ordered by id, date)
+----+---------------------+-----+------+------+
| id | date | v28 | v29 | v30 |
+----+---------------------+-----+------+------+
| 1 | 20.01.2005 00:00:00 | 58 | 197 | 90 |
| 1 | 08.02.2005 00:00:00 | 67 | 210 | 133 |
| 2 | 10.01.2005 00:00:00 | 87 | NULL | 119 |
| 2 | 11.07.2005 00:00:00 | 77 | 174 | NULL |
+----+---------------------+-----+------+------+
Related Topics
"You Tried to Execute a Query That Does Not Include the Specified Aggregate Function"
Where in Condition Not Accepting String Value
Search for "Whole Word Match" with SQL Server Like Pattern
Query Runs Slow with Date Expression, But Fast with String Literal
Postgresql Column Not Found, But Shows in Describe
Composite Primary Keys:Good or Bad
How to Use My SQL Knowledge with Cloudant/Couchdb
Should I Use SQL_Variant Data Type
Regular Expression to Match Common SQL Syntax
SQL Keep Getting Error with on Update Cascade
Sqlplus Statement from Command Line
How to Extract Week Number in SQL
Incomplete Information from Query on Pg_Views