How to Create a Pivot Query in SQL Server Without Aggregate Function

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) |

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.

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.

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 |

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.

Pivot without aggregate function in MSSQL 2008 R2

You can use the PIVOT function to get the result, you will just have to use row_number() to help.

The base query for this will be:

select skill_id, skill, parameter,
  row_number() over(partition by skill, skill_id order by skill_id) rn
from yt;

See SQL Fiddle with Demo. I use row_number() to apply a distinct value to each row within the skill and skill_id, you will then use this row number value as the column to PIVOT.

The full code with the PIVOT applied will be:

select skill_id, skill,[Parameter_1], [Parameter_2], [Parameter_3]
from 
(
  select skill_id, skill, parameter,
    'Parameter_'+cast(row_number() over(partition by skill, skill_id 
                                     order by skill_id) as varchar(10)) rn
  from yt
) d
pivot
(
  max(parameter)
  for rn in ([Parameter_1], [Parameter_2], [Parameter_3])
) piv;

See SQL Fiddle with Demo.

In your case, it seems like you will have an unknown number of parameters for each skill. If that is true, then you will want to use dynamic SQL to get the result:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Parameter_'
                          +cast(row_number() over(partition by skill, skill_id 
                               order by skill_id) as varchar(10))) 
                    from yt
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT skill_id, skill,' + @cols + ' from 
             (
                select skill_id, skill, parameter,
                  ''Parameter_''+cast(row_number() over(partition by skill, skill_id 
                                   order by skill_id) as varchar(10)) rn
                from yt
            ) x
            pivot 
            (
                max(parameter)
                for rn in (' + @cols + ')
            ) p '

execute(@query);

See SQL Fiddle with Demo

---

Related Topics