Can SQL Server Pivot without knowing the resulting column names?
The example you linked to uses dynamic SQL. Unfortunately, there is no other built-in method for pivoting in SQL Server when the output columns are not known in advance.
If the data is not too large, it's probably easiest to simply run a normal row query from ASP.NET and perform your pivot in the application code. If the data is very large, then you'll have to generate the SQL dynamically after first querying for the possible column values.
Note that you don't actually need to write a SQL statement that generates dynamic SQL; you can simply generating the SQL in ASP.NET, and that will most likely be much easier. Just don't forget to escape the distinct Site values before chucking them in a generated query, and don't forget to parameterize whatever parts of the SQL statement that you normally would without the pivot.
Dynamic TSQL Pivot without aggregate function
Sample data
create table ExternalPersonRelationTable
(
PersonId int,
SubjectCode int
);
insert into ExternalPersonRelationTable (PersonId, SubjectCode) values
(4187, 3),
(4187, 278),
(4429, 3),
(4429, 4),
(4463, 99),
(4464, 174),
(4464, 175);
Solution
Start with a (limited) static version of the pivot query as a reference.
select piv.SubjectCode as Code,
isnull(convert(bit, piv.[4187]), 0) as [4187],
isnull(convert(bit, piv.[4429]), 0) as [4429],
isnull(convert(bit, piv.[4463]), 0) as [4463],
isnull(convert(bit, piv.[4464]), 0) as [4464]
from ExternalPersonRelationTable epr
pivot (max(epr.PersonId) for epr.PersonId in ([4187],[4429],[4463],[4464])) piv;
Identify the dynamic parts and construct (and validate) those.
-- constuct lists
declare @fieldList nvarchar(1000);
declare @pivotList nvarchar(1000);
with cte as
(
select epr.PersonId
from ExternalPersonRelationTable epr
group by epr.PersonId
)
select @fieldList = string_agg('isnull(convert(bit, piv.['
+ convert(nvarchar(10), cte.PersonId)
+ ']), 0) as ['
+ convert(nvarchar(10), cte.PersonId)
+ ']', ', ') within group (order by cte.PersonId),
@pivotList = string_agg('['
+ convert(nvarchar(10), cte.PersonId)
+ ']', ',') within group (order by cte.PersonId)
from cte;
-- validate lists
select @fieldList as FieldList;
select @pivotList as PivotList;
Merge the dynamic parts in the final query (and validate during development phase).
-- construct query
declare @query nvarchar(3000) = 'select piv.SubjectCode as Code, '
+ @fieldList
+ 'from ExternalPersonRelationTable epr '
+ 'pivot (max(epr.PersonId) for epr.PersonId in ('
+ @pivotList
+ ')) piv;';
-- validate query
select @query as Query;
Run the dynamic query.
-- run query
exec sp_executesql @query;
Result
Code 4187 4429 4463 4464
---- ----- ----- ----- -----
3 True True False False
4 False True False False
99 False False True False
174 False False False True
175 False False False True
278 True False False False
Fiddle to see things in action.
Dynamic Pivot table, how to remove NULL values without knowing column names?
so, this gets a little messy, but here's the idea
For this i'm querying out of the master table and pivoting on a variate of received (datetime).
declare @columns varchar(max)
declare @columnsisnull varchar(max)
declare @sql nvarchar(max)
SELECT @columns = STUFF(( SELECT DISTINCT TOP 100 PERCENT '],[' + CONVERT(VARCHAR(7), m1.received, 120)
FROM master m1 where m1.received between DATEADD(year, -1, getdate()) and GETDATE()
ORDER BY '],[' + CONVERT(VARCHAR(7), m1.received, 120) desc
FOR XML PATH('')), 1, 2, '') + ']'
SELECT @columnsisnull = STUFF(( SELECT DISTINCT TOP 100 PERCENT ', isnull([' + CONVERT(VARCHAR(7), m1.received, 120) + '],0)'
FROM master m1 where m1.received between DATEADD(year, -1, getdate()) and GETDATE()
--ORDER BY ', isnull([' + CONVERT(VARCHAR(7), m1.received, 120) + '],0)'
FOR XML PATH('')), 1, 2, '')
this looks basically like your code for getting the cols, with the difference being the @columnsisnull where i just append the isnull function into the columns
then for your @sql
set @sql = N'SELECT name, ' + @columnsisnull + ' from master ) p
pivot(sum(amount) for received in ( '+@columns+')) as pvt'
execute(@sql)
Why is the Max function used when we pivot text columns in SQL Server?
Because in your example you've chosen EVENT as the value to show in the PIVOT intersections (i.e. since you've specified EVENT in the PIVOT clause), the value must be specified with one of the permissible aggregate functions, as there are potentially multiple rows for each of the column values that you've chosen in your pivot, when grouped by the remaining columns (i.e. DATE in your case).
In Sql Server[1], MAX() or MIN() is commonly used when pivoting non-numeric columns, as it is able to show one of the original of the values of the column.
Any non-aggregate and non-pivoted columns will be left as-is and will be used to form the groups on which the pivot is based (in your case, column DATE isn't either in the aggregate, or the column pivot, so it will form the row group)
Consider the case where your pivoted table contains multiple rows matching your predicate, such as this:
INSERT INTO strategy (DATE, SITA, EVENT) VALUES
('1 Jan 2018', 'ABZPD', 'Event1'),
('1 Jan 2018', 'BFSPD', 'Event2'),
('1 Jan 2018', 'BFSPD', 'Event3');
After Pivot:
DATE ABZPD BFSPD
2018-01-01T00:00:00Z Event1 Event3
i.e. During the Pivot, the BFSPD rows for Event2 and Event3 needed to somehow be projected into a single cell - hence the need for an aggregate. This aggregate is still needed, even if there is known to be just one value (this being the case for the Event1 value for SITA ABZPD in the above example).
Since BFSPD has two events, you'll need to somehow resolve how to project a value into a single cell value. The use of MAX on the VARCHAR column resolves the 'largest' value (Event3) in the event of multiple rows in projecting into the same resulting pivot 'cell' - SqlFiddle example here
You could choose to use COUNT(Event) to show you the number of events per row / pivot intersection - Fiddle
And you could switch the aggregate on EVENT with DATE - EVENT is thus used in the column grouping.
*1 Aggregates like AVG or STDEV are obviously not available to strings. Other RDBMS have additional aggregates like FIRST which will arbitrarily take the first value, or GROUP_CONCAT / LIST_AGG, which can fold string values together with a delimiter. And PostGres allows you to make your own aggregate functions!. But sadly, none of this in SqlServer, hence MIN()
/ MAX() for now.
How do I PIVOT a set of columns based on a specific column?
To pivot multiple ways, you'll have to aggregate each individually. Note that in the code below, if the 'type' condition in the case statement is not met, null is returned. So the aggregations (I'm using sum here) will only aggregate over that type.
select id,
country,
[Republic Approved] = sum(case when type = 'republic' then approved end),
[Republic Disbursed] = sum(case when type = 'republic' then disbursed end),
...,
[Sultanate Approved] = sum(case when type = 'sultanate' then approved end),
...
from sourceTable
group by id,
country
I'm making some assumptions on what you really want here though, as your stated desired results implies there's more data in your dataset.
Alternatively, you could first unpivot on those payment type columns, concatenate the resulting names with the government type, and then pivot on the concatenated names. But this is going to be less performant and probably less readable, though possibly you'll get away with fewer characters typed (never the wisest goal).
Pivot or transpose a table in SQL Server without GROUPING BY
This can be accomplished using the PIVOT function. The GROUP BY will work because you have an indicator that makes each of the rows distinct. For your data the indicator is the rowNumber column.
If you have a set number of columns, then you will want to hard-code them using a static pivot. The code will be similar to this following:
select [1], [2], [3]
from
(
select colNumber, RowNumber, CellData
from yourtable
) src
pivot
(
max(CellData)
for colnumber in ([1], [2], [3])
) piv;
See SQL Fiddle with Demo.
In your case, you stated that you will have a unknown number of columns. If that is your situation then you will need to use dynamic sql to build the list of columns to pivot. I demonstrated the static version because it makes it easier to convert the code to dynamic SQL.
The key to the dynamic sql version is getting the list of columns which is done by querying your table and creating a string of the column names. This is done using FOR XML PATH:
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(colNumber)
from yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
This list is then added into the query string that you generate and the final code is then:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(colNumber)
from yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT ' + @cols + '
from
(
select colNumber, rowNumber, CellData
from yourtable
) x
pivot
(
min(CellData)
for colNumber in (' + @cols + ')
) p '
execute(@query)
See SQL Fiddle with Demo.
Both give the result:
| 1 | 2 | 3 |
-----------------------------
| Orange | Apple | Banana |
| Grape | Corn | Lemon |
| Tomato | Lettuce | Onion |
Pivoting 2 columns from 3 Tables and creating pivot-column-names to avoid conflict - SQL-Server 2008R2
I personally would do this a bit different. Since you are trying to pivot two separate columns that screams to use the UNPIVOT function.
The unpivot will convert your multiple columns into rows to then pivot.
Since you have SQL Server 2008, you can use CROSS APPLY and values:
select id, course, teacher, col, flag
from
(
Select cd.Id, c.CourseName as Course, t.TeacherName as Teacher
,cast(r.Id as varchar(10))as RoomId
, r.RoomName as RoomName
,cast(100 + s.Id as varchar(10)) as StudentId
, s.StudentName as Student
, '1' flag
FROM CourseDetails cd
Left JOIN Courses c
ON cd.CourseId = c.Id
Left JOIN Teachers t
ON cd.TeacherId = t.Id
Left JOIN CourseMember cm
ON cd.Id = cm.CourseDetailsId
Left JOIN Students s
ON cm.StudentId = s.Id
Left JOIN Rooms r
ON cd.RoomId = r.Id
) d
cross apply
(
values ('roomname', roomname),('student',student)
) c (value, col)
See Demo. The unpivot generates a result similar to this:
| ID | COURSE | TEACHER | COL | FLAG |
-------------------------------------------------------------
| 1 | C# 1 - Basics | Marc G. | E7 | 1 |
| 1 | C# 1 - Basics | Marc G. | Penny | 1 |
| 2 | C# 2 - Intermediate | Sam S. | E7 | 1 |
| 2 | C# 2 - Intermediate | Sam S. | Penny | 1 |
| 2 | C# 2 - Intermediate | Sam S. | E7 | 1 |
| 2 | C# 2 - Intermediate | Sam S. | Koothrappali | 1 |
| 3 | C# 3 - Advanced | John S. | A3 | 1 |
| 3 | C# 3 - Advanced | John S. | Cooper | 1 |
You will see that the col data contains all the values that you want to pivot. Once the data is in the rows, if will be easy to apply one pivot:
select id, course, teacher,
coalesce(A3, '') A3,
coalesce(E7, '') E7,
coalesce(Koothrappali, '') Koothrappali,
coalesce(Cooper, '') Cooper,
coalesce(Penny, '') Penny,
coalesce(Amy, '') Amy
from
(
select id, course, teacher, col, flag
from
(
Select cd.Id, c.CourseName as Course, t.TeacherName as Teacher
,cast(r.Id as varchar(10))as RoomId
, r.RoomName as RoomName
,cast(100 + s.Id as varchar(10)) as StudentId
, s.StudentName as Student
, '1' flag
FROM CourseDetails cd
Left JOIN Courses c
ON cd.CourseId = c.Id
Left JOIN Teachers t
ON cd.TeacherId = t.Id
Left JOIN CourseMember cm
ON cd.Id = cm.CourseDetailsId
Left JOIN Students s
ON cm.StudentId = s.Id
Left JOIN Rooms r
ON cd.RoomId = r.Id
) d
cross apply
(
values ('roomname', roomname),('student',student)
) c (value, col)
) d
pivot
(
max(flag)
for col in (A3, E7, Koothrappali, Cooper, Penny, Amy)
) piv
See SQL Fiddle with Demo.
Then to convert this to dynamic SQL, you are only pivoting one column, so you will use the following to get the list of columns:
select @cols = STUFF((SELECT ',' + QUOTENAME(col)
from
(
select id, roomname col, 1 SortOrder
from rooms
union all
select id, StudentName, 2
from Students
) d
group by id, col, sortorder
order by sortorder, id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
This will get the list of distinct rooms and students that are then used in the pivot. So the final code will be:
DECLARE @cols AS NVARCHAR(MAX),
@colsNull AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(col)
from
(
select id, roomname col, 1 SortOrder
from rooms
union all
select id, StudentName, 2
from Students
) d
group by id, col, sortorder
order by sortorder, id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @colsNull = STUFF((SELECT ', coalesce(' + QUOTENAME(col)+', '''') as '+QUOTENAME(col)
from
(
select id, roomname col, 1 SortOrder
from rooms
union all
select id, StudentName, 2
from Students
) d
group by id, col, sortorder
order by sortorder, id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'SELECT
id, course, teacher,' + @colsNull + '
from
(
select id, course, teacher, col, flag
from
(
Select cd.Id, c.CourseName as Course, t.TeacherName as Teacher
,cast(r.Id as varchar(10))as RoomId
, r.RoomName as RoomName
,cast(100 + s.Id as varchar(10)) as StudentId
, s.StudentName as Student
, ''1'' flag
FROM CourseDetails cd
Left JOIN Courses c
ON cd.CourseId = c.Id
Left JOIN Teachers t
ON cd.TeacherId = t.Id
Left JOIN CourseMember cm
ON cd.Id = cm.CourseDetailsId
Left JOIN Students s
ON cm.StudentId = s.Id
Left JOIN Rooms r
ON cd.RoomId = r.Id
) d
cross apply
(
values (''roomname'', roomname),(''student'',student)
) c (value, col)
) d
pivot
(
max(flag)
for col in (' + @cols + ')
) p '
execute(@query)
See SQL Fiddle with Demo.
Note I implemented a flag to be used in the pivot, this basically generates a Y/N if there is a value for the room or student.
This gives a final result:
| ID | COURSE | TEACHER | A3 | E7 | KOOTHRAPPALI | COOPER | PENNY | AMY |
---------------------------------------------------------------------------------------
| 1 | C# 1 - Basics | Marc G. | | 1 | | | 1 | |
| 2 | C# 2 - Intermediate | Sam S. | | 1 | 1 | | 1 | |
| 3 | C# 3 - Advanced | John S. | 1 | | | 1 | | |
| 4 | C# 3 - Advanced | Reed C. | | 1 | 1 | | | |
| 5 | SQL 1 - Basics | Marc G. | 1 | | | | | |
| 6 | SQL 2 - Intermediate | Marc G. | 1 | | | | | |
| 7 | SQL 3 - Advanced | Marc G. | | 1 | | 1 | | 1 |
| 8 | SQL 3 - Advanced | gbn | 1 | | 1 | | | |
As a side note, this data can also be unpivoted using the unpivot function in sql server. (See Demo with unpivot)
Combination of dynamic pivot and static pivot in SQL Server
I have used your static pivot part of the query as the source of dynamic pivot. Create two sets of dynamic pivot column list. One for pivoting and the another with Coalesce() to select pivoted columns (to convert null into 0). If there is no categcount for any category then that category has been replaced with null (case when). Two more aliases for Category and SumCatCount have been created since those were used in pivot condition.
Here goes your answer:
create table #temp
(
Place nvarchar(20),
State nvarchar(20),
Category nvarchar(20) null,
CategCount int null,
MCount int null,
Buys int,
Cost int
)
insert into #temp values ('London', 'UK', 'Old', 3, NULL, 22, 4.50)
insert into #temp values ('London', 'UK', 'Old', 6, 5, 3, 22.00)
insert into #temp values ('Brussels', 'BE', 'Young', 2, NULL, 4, 3.50)
insert into #temp values ('Brussels', 'BE', 'M', NULL, 5, 12, 1.20)
insert into #temp values ('Brussels', 'BE', 'M', NULL, 2, 1, 1.20)
DECLARE @cols AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';
DECLARE @colsForSelect AS NVARCHAR(MAX)='';
SET @cols = STUFF((SELECT distinct ',' + quotename(category)
FROM #temp where CategCount is not null
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @colsForSelect = STUFF((SELECT distinct ',' + ' Coalesce('+quotename(category)+',0) '+ quotename(category)
FROM #temp where CategCount is not null
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
--select (@cols) as bm
set @query =
'SELECT count,place,state,(case when OldSumCatCount >0 then OldCategory else null end)Category,SumMCount, ' + @colsForSelect + ',SumCost,SumBuys from
(
select count(*) as count, place, state,category OldCategory, category,
sum(ISNULL(MCount, 0)) as SumMCount,
sum(ISNULL(CategCount, 0)) as OldSumCatCount,
sum(ISNULL(CategCount, 0)) as SumCatCount,
sum(Cost) as SumCost,
sum(ISNULL(buys, 0)) as SumBuys
from #temp
group by place , state, category
) src
pivot
(
max(SumCatCount) for Category in (' + @cols + ')
) piv
order by place desc,count'
execute(@query)
GO
| count | place | state | Category | SumMCount | Old | Young | SumCost | SumBuys |
|---|---|---|---|---|---|---|---|---|
| 2 | London | UK | Old | 5 | 9 | 0 | 26 | 25 |
| 1 | Brussels | BE | Young | 0 | 0 | 2 | 3 | 4 |
| 2 | Brussels | BE | null | 7 | 0 | 0 | 2 | 13 |
Related Topics
What Is the Affect of Convert() on Index While Searching
Simple Recursive Query in Oracle
Why Do SQL Id Sequences Go Out of Sync (Specifically Using Postgres)
How to Join Two Tables Together with Same Number of Rows by Their Order
How to Calculate Moving Sum with Reset Based on Condition in Teradata SQL
How to Select a Column in SQL Server with a Special Character in the Column Name
Support for JSON in Oracle 11G
Orderby in SQL Server to Put Positive Values Before Negative Values
Update Oracle Table with Values from CSV File
How to Force Nolock Hint for SQL Server Logins
Generating Xml File from SQL Server 2008
SQL Server Query for Many to Many Relationship
Transact-Sql: How to Tokenize a String