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) |
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.
Pivot without aggregate function or Row to Columns by group in Oracle
select city
,min (case type_id when 1 then amount end) as type_1_amount
,min (case type_id when 2 then amount end) as type_2_amount
from (select city,type_id,amount
,row_number () over
(
partition by city,type_id
order by amount
) as rn
from Table_1
)
group by city
,rn
order by city
,rn
+-------------+---------------+---------------+
| CITY | TYPE_1_AMOUNT | TYPE_2_AMOUNT |
+-------------+---------------+---------------+
| Los Angeles | 35400 | 162000 |
+-------------+---------------+---------------+
| Moscow | 158000 | 1478000 |
+-------------+---------------+---------------+
| Moscow | 741200 | (null) |
+-------------+---------------+---------------+
| New York | 94500 | 5500000 |
+-------------+---------------+---------------+
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 Column in SQL without Aggregation
You can use conditional aggregation:
select object_key,
max(case when name = 'OBJECT_NAME' then value end) as object_name,
max(case when name = 'Start Time' then value end) as start_time,
max(case when name = 'End Time' then value end) as end_time,
max(case when name = 'row_count' then value end) as row_count,
max(case when name = 'Execution Time' then value end) as execution_time
from t
group by object_key;
I'm not sure why you specify "no aggregation" in the question.
If you really have an aversion to aggregation use can use joins:
select ton.object_key, ton.value as object_name,
ts.value as start_time, te3.value as end_time,
. .
from t ton on
t ts
on ton.object_key = ts.object_key join
t te
on ton.object_key = t3.ojbect_key. join
. . . ;
How to Pivot Without Aggregation, Is that possible?
If your have multiple value for each row then what you expect without aggregation function?
And if you have a single value per each row, so it's no matter that use sum,min,max or avg aggregation function.
like this(in oracle):
select row_id, 'C1', 'C2', 'C3'
from table
pivot(max("value") for column_id in('C1', 'C2','C3'));
executable version with sample:
select row_id, 'C1', 'C2', 'C3'
from (select 1 table_id, 'C1' Column_id, 1 Row_id, 20000 "value" from dual union all
select 1 table_id, 'C2' Column_id, 1 Row_id, 30000 "value" from dual union all
select 1 table_id, 'C3' Column_id, 1 Row_id, 25000 "value" from dual union all
select 1 table_id, 'C1' Column_id, 2 Row_id, 80200 "value" from dual union all
select 1 table_id, 'C2' Column_id, 2 Row_id, 50000 "value" from dual union all
select 1 table_id, 'C3' Column_id, 2 Row_id, 95000 "value" from dual)
pivot(max("value") for column_id in('C1', 'C2','C3'))
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 transpose rows/columns without aggregation?
Pivot doesn't actually require numeric data to work with aggregate functions, e.g.:
create table dbo.SurveyData (
Survey int,
Question int,
Answer varchar(10)
);
insert dbo.SurveyData (Survey, Question, Answer)
values
(1, 1, 'Blue'),
(1, 2, 'Puppy'),
(1, 3, 'Strawberry'),
(2, 1, 'Red'),
(2, 2, 'Kitten'),
(2, 3, 'Chocolate');
select pvt.*
from (
select Survey, 'Q'+cast(Question as varchar(10)) as Question, Answer
from dbo.SurveyData
) Source
pivot (max(Answer) for Question in ( [Q1], [Q2], [Q3] )) pvt;
| Survey | Q1 | Q2 | Q3 |
|---|---|---|---|
| 1 | Blue | Puppy | Strawberry |
| 2 | Red | Kitten | Chocolate |
Related Topics
How to Create a Unique Index on a Null Column
How to Determine the Number of Days in a Month in SQL Server
The Maximum Recursion 100 Has Been Exhausted Before Statement Completion
How to Assign an Exec Result to a SQL Variable
Using 'Case Expression Column' in Where Clause
Split Column into Multiple Rows in Postgres
Bulk Delete on SQL Server 2008 (Is There Anything Like Bulk Copy (Bcp) for Delete Data)
Where Clause VS on When Using Join
Is There Something Like a Zip() Function in Postgresql That Combines Two Arrays
SQL Server:Dynamic Pivot Over 5 Columns
Search for a String in All Tables, Rows and Columns of a Db
MySQL Insert into Table Values.. VS Insert into Table Set