Help needed in data pivoting without aggregate in sqlserver 2008

Question

I need to pivot a table as follows : the initial table is like this

GEO PRD PACK    DATATYPE    Sales1  Sales2

T1  P1  M1  22          1     2

T1  P1  M1  23          2     8

T1  P1  M1  24          3     5

T2  P2  M2  22          3     2

T2  P2  M2  23          1     4

T2  P2  M2  24          1     7

and what I want is :

GEO PRD PACK 22_Sales1 22_Sales2 23_Sales1 23_Sales2 24_Sales1 24_Sales2

T1  P1  M1       1         2         2         8         3         5

T2  P2  M2       3         2         1         4         1         7

here, the distinct DATATYPE is fixed : always will be : 22, 23, 24, no less and no more.

Please, can anyone help me how to write the proper query ?

score 0 · Answer 1 · answered Aug 16 '12 at 11:43

To use a PIVOT with your data you would use the following. This performs an UNPIVOT first and then a PIVOT to get the data in the format that you need:

create table t1
(
    geo varchar(2),
    prd varchar(2),
    pack varchar(2),
    datatype int,
    sales1 int,
    sales2 int
)
insert into t1 values ('T1', 'P1', 'M1', 22, 1, 2)
insert into t1 values ('T1', 'P1', 'M1', 23, 2, 8)
insert into t1 values ('T1', 'P1', 'M1', 24, 3, 5)
insert into t1 values ('T2', 'P2', 'M2', 22, 3, 2)
insert into t1 values ('T2', 'P2', 'M2', 23, 1, 4)
insert into t1 values ('T2', 'P2', 'M2', 24, 1, 7)

select *
from 
(
    select geo, prd, pack, cast(datatype as varchar(5)) + '_' + col col,value
    from t1
    unpivot
    (
        value
        for col in (sales1, sales2)
    ) u
) x
pivot
(
    sum(value)
    for col in ([22_sales1], [22_sales2], [23_Sales1], [23_Sales2], [24_Sales1], [24_Sales2])
) p

See SQL Fiddle with Demo

score 0 · Answer 2 · answered Jun 06 '11 at 09:43

select geo, prd, pack,   
  sum(sales_22_1) sales_22_1,
  sum(sales_22_2) sales_22_2,
  sum(sales_23_1) sales_23_1,
  sum(sales_23_2) sales_23_2,
  sum(sales_24_1) sales_24_1, 
  sum(sales_24_2) sales_24_2 
from
(select geo, prd, pack, 
  (case when datatype = 22 then sales1
   else null
   end) as sales_22_1,
(case when datatype = 22 then sales2
   else null
   end) as sales_22_2,
(case when datatype = 23 then sales1
   else null
   end) as sales_23_1,
(case when datatype = 23 then sales2
   else null
   end) as sales_23_2,
(case when datatype = 24 then sales1
   else null
   end) as sales_24_1,
(case when datatype = 24 then sales2
   else null
   end) as sales_24_2
 from mytab)
group by geo, prd, pack

(untested, since I don't have a SQL server instance available).

score 0 · Answer 3 · edited May 23 '17 at 12:12

0

Recent versions of SQL Server have a PIVOT function.

Here is an example of how to do pivoting in several SQL Server versions.

And there are many good answers to this very popular SO question: SQL Server PIVOT examples?

Here is an example from that page of what a simple PIVOT statement looks like:

SELECT act AS 'Action', [View] as 'View', [Edit] as 'Edit'
FROM (
    SELECT act, cmd FROM data
) AS src
PIVOT (
    MAX(cmd) FOR cmd IN ([View], [Edit])
) AS pvt

edited May 23 '17 at 12:12

Community

1
1

answered Jun 06 '11 at 09:44

DOK

32,337
7
60
92

How can I implement PIVOT here ? – tanvir80 Jun 08 '11 at 10:04
Start with the inner SELECT. Make sure you're getting the right data. The outer SELECT specifies the new columns. You'll probably have to start small and gradually build your SQL. – DOK Jun 08 '11 at 11:06

Help needed in data pivoting without aggregate in sqlserver 2008

3 Answers3