TSQL Pivot without aggregate function
You can use the MAX aggregate, it would still work. MAX of one value = that value..
In this case, you could also self join 5 times on customerid, filter by dbColumnName per table reference. It may work out better.
Pivot rows to columns without aggregate
The PIVOT
function requires an aggregation to get it to work. It appears that your VAL
column is a varchar
so you will have to use either the MAX
or MIN
aggregate functions.
If the number of tests is limited, then you can hard-code the values:
select sbno, Test1, Test2, Test3
from
(
select test_name, sbno, val
from yourtable
) d
pivot
(
max(val)
for test_name in (Test1, Test2, Test3)
) piv;
See SQL Fiddle with Demo.
In your OP, you stated that you will have an larger number of rows to turn into columns. If that is the case, then you can use dynamic SQL:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(TEST_NAME)
from yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT sbno,' + @cols + '
from
(
select test_name, sbno, val
from yourtable
) x
pivot
(
max(val)
for test_name in (' + @cols + ')
) p '
execute(@query)
See SQL Fiddle with Demo.
Both versions will give the same result:
| SBNO | TEST1 | TEST2 | TEST3 |
---------------------------------
| 1 | 0.304 | 2.3 | PASS |
| 2 | 0.31 | 2.5 | PASS |
| 3 | 0.306 | 2.4 | (null) |
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.
How to pivot multiple columns without aggregation
Use row_number()
function and do the conditional aggregation :
select id, IdCust, Ref,
max(case when Seq = 1 then stock end) as [Stock A], -- second table *id*
max(case when Seq = 1 then code end) as [Code 1],
max(case when Seq = 1 then price end) as [Price1],
max(case when Seq = 2 then stock end) as [Stock B], -- second table *id*
max(case when Seq = 2 then code end) as [Code 2],
max(case when Seq = 2 then price end) as [Price2]
from (select f.*, s.Id Stock, s.Code, s.Price,
row_number() over (partition by f.Ref order by s.id) as Seq
from first f
inner join second s on s.Ref = f.Ref
) t
group by id, IdCust, Ref;
However, this would go with known values else you would need go with dynamic solution for that.
SQL Server 2012 PIVOT without aggregate
There are several ways that you can transform the data. Some use an aggregate function and others don't. But even though you are pivoting a string you can still apply an aggregate.
Aggregate with CASE:
select name,
max(case when category = 'A' then 'X' else '' end) CategoryA,
max(case when category = 'B' then 'X' else '' end) CategoryB,
max(case when category = 'C' then 'X' else '' end) CategoryC,
max(case when category = 'D' then 'X' else '' end) CategoryD
from yourtable
group by name
See SQL Fiddle with Demo
Static Pivot:
You can still use the PIVOT
function to transform the data even though the values are strings. If you have a known number of categories, then you can hard-code the query:
select name,
coalesce(A, '') CategoryA,
coalesce(B, '') CategoryB,
coalesce(C, '') CategoryC,
coalesce(C, '') CategoryD
from
(
select name, category, 'X' flag
from yourtable
) d
pivot
(
max(flag)
for category in (A, B, C, D)
) piv
See SQL Fiddle with Demo.
Dynamic Pivot:
If you have an unknown number of categories, then you can use dynamic SQL:
DECLARE @cols AS NVARCHAR(MAX),
@colsNull AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(category)
from yourtable
group by category
order by category
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @colsNull = STUFF((SELECT ', coalesce(' + QUOTENAME(category)+', '''') as '+QUOTENAME('Category'+category)
from yourtable
group by category
order by category
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT name, ' + @colsNull + '
from
(
select name, category, ''X'' flag
from yourtable
) x
pivot
(
max(flag)
for category in (' + @cols + ')
) p '
execute(@query)
See SQL Fiddle with Demo.
Multiple Joins:
select c1.name,
case when c1.category is not null then 'X' else '' end as CategoryA,
case when c2.category is not null then 'X' else '' end as CategoryB,
case when c3.category is not null then 'X' else '' end as CategoryC,
case when c4.category is not null then 'X' else '' end as CategoryD
from yourtable c1
left join yourtable c2
on c1.name = c2.name
and c2.category = 'B'
left join yourtable c3
on c1.name = c3.name
and c3.category = 'C'
left join yourtable c4
on c1.name = c4.name
and c4.category = 'D'
where c1.category = 'A'
See SQL Fiddle with Demo
All queries will give the result:
| NAME | CATEGORYA | CATEGORYB | CATEGORYC | CATEGORYD |
--------------------------------------------------------
| Joe | X | X | | X |
| Mary | X | | X | X |
How to pivot table without using aggregate function SQL?
The pivot syntax requires an aggregate function. It's not optional.
https://docs.snowflake.com/en/sql-reference/constructs/pivot.html
SELECT ... FROM ... PIVOT ( <aggregate_function> ( <pivot_column> )
FOR <value_column> IN ( <pivot_value_1> [ , <pivot_value_2> ... ] ) )[ ... ]
In the Snowflake documentation, optional syntax is in square braces. The <aggregate_function>
is not in square braces, so it's required. If you only have one value, any of the aggregate functions you listed except count will work and give the same result.
create or replace table T1("SOURCE" string, ATTRIBUTE string, CATEGORY int);
insert into T1("SOURCE", attribute, category) values
('GOOGLE', 'MOVIES', 1),
('YAHOO', 'JOURNAL', 2),
('GOOGLE', 'MUSIC', 1),
('AOL', 'MOVIES', 3);
select *
from T1
PIVOT ( sum ( CATEGORY )
for "SOURCE" in ( 'GOOGLE', 'YAHOO', 'AOL' ));
ATTRIBUTE | 'GOOGLE' | 'YAHOO' | 'AOL' |
---|---|---|---|
MOVIES | 1 | null | 3 |
MUSIC | 1 | null | null |
JOURNAL | null | 2 | null |
Using PIVOT with SQL Server without Aggregate function
You need to "FEED" the pivot with an X-Axis,Y-Axis and a Value. We create a row key via dense_rank()
Example
Declare @YourTable Table ([Name] varchar(50),[Occupation] varchar(50)) Insert Into @YourTable Values
('Samantha','Doctor')
,('Julia','Actor')
,('Maria','Actor')
,('Meera','Singer')
,('Ashley','Professor')
,('Ketty','Professor')
,('Christeen','Professor')
,('Jane','Actor')
,('Jenny','Doctor')
,('Priya','Singer')
Select *
from (Select *
,RN = dense_rank() over (partition by occupation order by name)
From @YourTable
) src
Pivot (max(Name) for Occupation in ([Doctor], [Professor],[Singer], [Actor]) ) pvt
Returns
RN Doctor Professor Singer Actor
1 Jenny Ashley Meera Jane
2 Samantha Christeen Priya Julia
3 NULL Ketty NULL Maria
NOTE:
If you don't want RN in your results, rather than the top SELECT *
, you can specify the desired columns
SELECT [Doctor], [Professor],[Singer], [Actor]
From (...) src
Pivot (...) pvt
EDIT - Commentary
If you run the inner query
Select *
,RN = dense_rank() over (partition by occupation order by name)
From @YourTable
Order By RN
You'll get
Name Occupation RN
Jane Actor 1
Jenny Doctor 1
Ashley Professor 1
Meera Singer 1
Priya Singer 2
Christeen Professor 2
Samantha Doctor 2
Julia Actor 2
Maria Actor 3
Ketty Professor 3
RN becomes the Y-Axis, Occupation becomes the X-Axis and Name is the value.
Pivots by design are aggregates, therefore we just need a Y-Axis to perform the group by.
Related Topics
How to Generate a Random, Unique, Alphanumeric Id of Length N in Postgres 9.6+
How to Add Multiple "Not Like '%%' in the Where Clause of SQLite3
How to Use Merge on Linked Servers
Postgresql Visual Interface Similar to PHPmyadmin
Differencebetween a Candidate Key and a Primary Key
Divide the Table Data Randomly Based on Percentages
Db2 - Returning the Top 5 of Each Category
Pass String Variable Without Quotes in Query Vba
Difference Between Fetch/For to Loop a Cursor in Pl/Sql
SQL Query Selecting Different Row Result in JSON_Modify Because of in Operator Provided Value
How to Make a SQL Query for Last Transaction of Every Account
Set Identity_Insert Off for All Tables