Pivot Without Aggregate Function in Mssql 2008 R2

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

Pivot without aggregate

select *
from 
(
  select ID, Name, TotalCost
  from Table
) src
pivot
(
  sum(TotalCost)
  for Name in ('Wash, Dry & Fold','Hand Wash and Fold','Pressing Only','Dry Clean' )
) piv;

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.

Pivot Dynamic Columns, no Aggregation

Yes you can perform a dynamic pivot. Sometimes it is easier to work up the PIVOT query using a static version first so you can see how the query and results will appear. Then transform the query into a dynamic version.

Here is an example of a static vs. dynamic version of a query:

Static (SQL Fiddle):

select *
from 
(
    select u.userid,
        u.fname,
        u.lname,
        u.mobile,
        r.question,
        r.choice
    from users u
    left join results r
        on u.questionid = r.questionid
        and u.choiceid = r.choiceid
) x
pivot
(
    min(choice)
    for question in([are you], [from])
) p

Dynamic (SQL Fiddle):

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.question) 
            FROM results c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT userid, fname, lname, mobile, ' + @cols + ' from 
            (
                select u.userid,
                    u.fname,
                    u.lname,
                    u.mobile,
                    r.question,
                    r.choice
                from users u
                left join results r
                    on u.questionid = r.questionid
                    and u.choiceid = r.choiceid
           ) x
            pivot 
            (
                min(choice)
                for question in (' + @cols + ')
            ) p '

execute(@query)

If you can provide more details around your current table structure and then some sample data. We should be able to help you create the version that you would need for your situation.

As I said though, sometimes it is easier to start with a static version, where you hard-code in the columns that you need to transform first, then move on to the dynamic version.

SQL: pivot dates and hours without aggregate function

You can try something like this

    WITH data_CTE(iid, COL0,COL1,RID,ErrDesc, GtDesc)
AS

(

SELECT id, DateTrans, HourTrans,
  ROW_NUMBER() OVER (PARTITION BY (DateTrans)ORDER BY  id) AS RID ,ErrDesc, GtDesc
  FROM Gates

)

SELECT ErrDesc, GtDesc, [05/22/2018], [06/02/2018], [07/02/2018], [08/02/2018]

FROM

(SELECT  COL0,COL1,RID ,ErrDesc, GtDesc

FROM data_CTE)C

PIVOT

(

max(COL1)

FOR COL0 IN ( [05/22/2018], [06/02/2018], [07/02/2018], [08/02/2018])

) AS PivotTable;

Here is the Fiddler link.This is not the exact order you want.

---

Related Topics