How to Pivot Rows into Columns (Custom Pivoting)

How to pivot rows into columns (custom pivoting)

You could probably do it with the PIVOT function, but I prefer the old school method:

SELECT
    dy,
    MAX(CASE WHEN period = 1 THEN subj ELSE NULL END) AS P1,
    MAX(CASE WHEN period = 2 THEN subj ELSE NULL END) AS P2,
    MAX(CASE WHEN period = 3 THEN subj ELSE NULL END) AS P3,
    MAX(CASE WHEN period = 4 THEN subj ELSE NULL END) AS P4,
    MAX(CASE WHEN period = 5 THEN subj ELSE NULL END) AS P5,
    MAX(CASE WHEN period = 6 THEN subj ELSE NULL END) AS P6,
    MAX(CASE WHEN period = 7 THEN subj ELSE NULL END) AS P7
FROM
    Classes
GROUP BY
    dy
ORDER BY
    CASE dy
        WHEN 'Mon' THEN 1
        WHEN 'Tue' THEN 2
        WHEN 'Wed' THEN 3
        WHEN 'Thu' THEN 4
        WHEN 'Fri' THEN 5
        WHEN 'Sat' THEN 6
        WHEN 'Sun' THEN 7
        ELSE 8
    END

• I changed some column names to avoid reserved words

Pivoting rows to columns with custom column names in SQL Server

You can use row_number() and conditional aggregation:

SELECT t1.Message, a.*
FROM Table1 t1 CROSS APPLY 
     (SELECT MAX(CASE WHEN seqnum = 1 THEN value END) as greeting1,
             MAX(CASE WHEN seqnum = 2 THEN value END) as greeting2,
             MAX(CASE WHEN seqnum = 3 THEN value END) as greeting3,
             MAX(CASE WHEN seqnum = 4 THEN value END) as greeting4,
             MAX(CASE WHEN seqnum = 5 THEN value END) as greeting5,
             MAX(CASE WHEN seqnum = 6 THEN value END) as greeting6
      FROM (SELECT s.value,
                   ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as seqnum
            FROM STRING_SPLIT(t1.Message,'"')
            WHERE value LIKE '%.%'
           ) s
     ) s;

Note: In practice, this will probably preserve the ordering of the values. However that is not guaranteed -- based on the documentation.

Pivoting rows to columns for each id

This is unpivoting and a lateral join is a convenient approach. Presumably, you want the ids to be the same in the result set as in the original data:

select t.id, v.bin, v.sales
from t cross join lateral
     (values ('bin1', bin1), ('bin2', bin2), ('bin3', bin3)
     ) v(bin, sales);

Here is a db<>fiddle.

Pivoting rows into columns in SQL Server

You can perform with an UNPIVOT. There are two ways to do this:

1) In a Static Unpivot you would hard-code your Field columns in your query.

select firstname
  , lastname
  , replace(field, 'field', '')  as field
  , value
from test
unpivot
( 
  value
  for field in (field1, field2, field3, field27)
) u

See a SQL Fiddle for a working demo.

2) Or you could use a Dynamic Unpivot which will get the list of items to PIVOT when you run the SQL. The Dynamic is great if you have a large amount of fields that you will be unpivoting.

create table mytest
(
  firstname varchar(5),
  lastname varchar(10),
  field1 varchar(1),
  field2 varchar(1),
  field3 varchar(1),
  field27 varchar(1)
)

insert into mytest values('Mark', 'Smith', 'A', 'B', 'C', 'D')
insert into mytest values('John', 'Baptist', 'X', 'T', 'Y', 'G')
insert into mytest values('Tom', 'Dumm', 'R', 'B', 'B', 'U')

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

select @cols = stuff((select ','+quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('mytest') and
               C.name like 'Field%'
         for xml path('')), 1, 1, '')

set @query = 'SELECT firstname, lastname, replace(field, ''field'', '''')  as field, value
            from mytest
            unpivot 
            (
               value
               for field in (' + @cols + ')
            ) p '

execute(@query)

drop table mytest

Both will produce the same results.

Convert Rows to columns using 'Pivot' in SQL Server

If you are using SQL Server 2005+, then you can use the PIVOT function to transform the data from rows into columns.

It sounds like you will need to use dynamic sql if the weeks are unknown but it is easier to see the correct code using a hard-coded version initially.

First up, here are some quick table definitions and data for use:

CREATE TABLE yt 
(
  [Store] int, 
  [Week] int, 
  [xCount] int
);
    
INSERT INTO yt
(
  [Store], 
  [Week], [xCount]
)
VALUES
    (102, 1, 96),
    (101, 1, 138),
    (105, 1, 37),
    (109, 1, 59),
    (101, 2, 282),
    (102, 2, 212),
    (105, 2, 78),
    (109, 2, 97),
    (105, 3, 60),
    (102, 3, 123),
    (101, 3, 220),
    (109, 3, 87);

If your values are known, then you will hard-code the query:

select *
from 
(
  select store, week, xCount
  from yt 
) src
pivot
(
  sum(xcount)
  for week in ([1], [2], [3])
) piv;

See SQL Demo

Then if you need to generate the week number dynamically, your code will be:

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

select @cols = STUFF((SELECT ',' + QUOTENAME(Week) 
                    from yt
                    group by Week
                    order by Week
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT store,' + @cols + ' from 
             (
                select store, week, xCount
                from yt
            ) x
            pivot 
            (
                sum(xCount)
                for week in (' + @cols + ')
            ) p '

execute(@query);

See SQL Demo.

The dynamic version, generates the list of week numbers that should be converted to columns. Both give the same result:

| STORE |   1 |   2 |   3 |
---------------------------
|   101 | 138 | 282 | 220 |
|   102 |  96 | 212 | 123 |
|   105 |  37 |  78 |  60 |
|   109 |  59 |  97 |  87 |

Pivoting rows into columns

Using the reshape package.

First, melt your data.frame:

x <- melt(df) 

Then cast:

dcast(x, Color ~ Which + variable)

Depending on which version of the reshape package you're working with it could be cast() (reshape) or dcast() (reshape2)

Voila.

Pivoting rows into columns Oracle SQL

If you know the columns you want, you can use conditional aggregation:

select insertdate, id,
       max(case when PROPERTYNAME = 'ItemNumber' then propertyvalue end) as ItemNumber,
       max(case when PROPERTYNAME = 'ItemRev' then propertyvalue end) as ItemRev,
       max(case when PROPERTYNAME = 'BarCodeNumber' then propertyvalue end) as BarCodeNumber
from t
group by insertdate, id;

If you don't know all the properties up-front, then you need to construct the query dynamically as a string and use execute immediate.

Dynamically pivot rows into columns - SQL Server

Here is one way to do it using Dynamic Pivot

DECLARE @sql      VARCHAR(max)='', 
        @col_list VARCHAR(8000)='' 

SET @col_list = (SELECT DISTINCT Quotename([question]) + ',' 
                 FROM   Yourquery
                 FOR xml path('')) 

SET @col_list = LEFT (@col_list, Len(@col_list) - 1) 

SET @sql = 'select [DtCreated],[UserName]' + @col_list 
           + ' from Yourquery pivot (max([Answer]) for [Question] in (' 
           + @col_list + '))pv' 

EXEC(@sql) 

Update : You are missing Alias name to the sub-select

SET @sql = 'select [DtCreated],[UserName]' + @col_list 
           + ' from (SELECT 

sf.[DtCreated],    
sf.[UserName],
fc.Title,
sv.Value

  FROM [form].[SubmissionForm] sf
  inner join [form].[Submission] s on
  sf.id = s.SubmissionForm_Id
  inner join [form].[FormComponent] fc on s.FormComponentId = fc.Id
  inner join [form].[SubmissionValue] sv on s.Id = sv.Submission_Id
) a --here
pivot (max([sv.Value]) for [fc.Title] in (' 
           + @col_list + '))pv' 

EXEC(@sql) 

DEMO :

Schema setup

CREATE TABLE #Table1
    ([DtCreated] datetime, [UserName] varchar(8), [Question] varchar(11), [Answer] varchar(26))
;


INSERT INTO #Table1
    ([DtCreated], [UserName], [Question], [Answer])
VALUES
    ('2016-09-24 14:30:11', 'mauricio', 'Senha', '99658202'),
    ('2016-09-24 14:30:11', 'mauricio', 'Inteiro', '10'),
    ('2016-09-24 14:30:11', 'mauricio', 'Telefone', '(915) 438-05'),
    ('2016-09-24 14:30:11', 'mauricio', 'Email', 'mauriiciobarbosa@gmail.com'),
    ('2016-09-24 14:30:11', 'mauricio', 'Texto Livre', 'nksnksjksj nsjsnsjjs'),
    ('2016-09-24 14:30:11', 'mauricio', 'Decimal', '0.9')
;

Query :

declare @sql varchar(max)='',@col_list varchar(8000)=''

set @col_list = (select distinct quotename([Question])+',' from #Table1
for xml path(''))

set @col_list = left (@col_list,len(@col_list)-1)

set @sql = 'select [DtCreated],[UserName]'+@col_list+' from
#Table1
pivot (max([Answer]) for [Question] in ('+@col_list+'))pv'

exec(@sql)

Result :

╔═════════════════════════╦══════════╦════════════════════════════╦═════════╦══════════╦══════════════╦══════════════════════╗
║        DtCreated        ║ Decimal  ║           Email            ║ Inteiro ║  Senha   ║   Telefone   ║     Texto Livre      ║
╠═════════════════════════╬══════════╬════════════════════════════╬═════════╬══════════╬══════════════╬══════════════════════╣
║ 2016-09-24 14:30:11.000 ║ mauricio ║ mauriiciobarbosa@gmail.com ║      10 ║ 99658202 ║ (915) 438-05 ║ nksnksjksj nsjsnsjjs ║
╚═════════════════════════╩══════════╩════════════════════════════╩═════════╩══════════╩══════════════╩══════════════════════╝

Pivot rows into columns with fixed rows but unknown content in SQL Server

You can use window function row_number() to assign a rank to each unit in each candidate/qualification group, and then pivot with conditional aggregation:

select
    candidate,
    qualification,
    max(case when rn = 1 then unit end) unit_1,
    max(case when rn = 1 then passed end) unit_1_passed,
    max(case when rn = 2 then unit end) unit_2,
    max(case when rn = 2 then passed end) unit_2_passed
from (
    select
        t.*,
        row_number() over(
            partition by candidate, qualification 
            order by unit
        ) rn
    from exams t
) t
group by candidate, qualification 

---

Related Topics