how to pivot column to rows in Sql server 2008 R2

Question

I have the following problem and I think I should use OLAP functions, but I'd never used them before.

So I have:

row_no       Id       category         sell_value
1           1111          5             1670.75
2           2222          4             2777.51
3           3333          15             151.8         
4           4444          9               24.54
5           5555         25               110

And I want to get:

id   category_5  category_4  category_15   category_9    category_25
1111   1670.75        0           0             0            0
2222      0        2777.51        0             0            0  
3333      0           0          151.8          0            0
4444      0           0            0           24.54         0
5555      0           0            0             0           110

Is there any possibility to have my result by using only pls/sql? Or a procedure is required, or how can it be done.

Regards,

please search SO, as pivot questions have been asked many times before... — Tanner, May 30 '14 at 11:35
here's a little sample: http://stackoverflow.com/search?q=sql+pivot — Tanner, May 30 '14 at 11:36

score 1 · Answer 1 · answered May 30 '14 at 12:05

Test Data

CREATE TABLE Test_Table1
(row_no INT, Id INT, category INT, sell_value DECIMAL(10,2))
INSERT INTO Test_Table1 VALUES
(1,           1111,          5,             1670.75),
(2,           2222,          4,             2777.51),
(3,           3333,          15,             151.8 ),      
(4,           4444,          9 ,              24.54),
(5,           5555,         25 ,              110  )

Query

SELECT ID
       ,ISNULL(Category4, 0)  AS Category4
       ,ISNULL(Category5, 0)  AS Category5
       ,ISNULL(Category9, 0)  AS Category9
       ,ISNULL(Category15, 0) AS Category15
       ,ISNULL(Category25, 0) AS Category25   
FROM (
SELECT ID, Sell_Value, 'Category' + CAST(category AS VARCHAR(10)) AS Cats
FROM Test_Table1)t 
PIVOT (SUM(Sell_Value)
       FOR Cats
       IN (Category4, Category5, Category9,Category15,Category25)
       )p

Result Set

╔══════╦═══════════╦═══════════╦═══════════╦════════════╦════════════╗
║  ID  ║ Category4 ║ Category5 ║ Category9 ║ Category15 ║ Category25 ║
╠══════╬═══════════╬═══════════╬═══════════╬════════════╬════════════╣
║ 1111 ║ 0.00      ║ 1670.75   ║ 0.00      ║ 0.00       ║ 0.00       ║
║ 2222 ║ 2777.51   ║ 0.00      ║ 0.00      ║ 0.00       ║ 0.00       ║
║ 3333 ║ 0.00      ║ 0.00      ║ 0.00      ║ 151.80     ║ 0.00       ║
║ 4444 ║ 0.00      ║ 0.00      ║ 24.54     ║ 0.00       ║ 0.00       ║
║ 5555 ║ 0.00      ║ 0.00      ║ 0.00      ║ 0.00       ║ 110.00     ║
╚══════╩═══════════╩═══════════╩═══════════╩════════════╩════════════╝

score 0 · Answer 2 · answered May 30 '14 at 12:34

DECLARE @Test  TABLE 
(row_no INT, Id INT, category INT, sell_value DECIMAL(10,2))
INSERT INTO @Test_Table1 VALUES
(1,           1111,          5,             1670.75),
(2,           2222,          4,             2777.51),
(3,           3333,          15,             151.8 ),      
(4,           4444,          9 ,              24.54),
(5,           5555,         25 ,              110  )


select ID,
  ISNULL(max(case when ID = 1111 then isnull(sell_value,0) end),0) Category4,
  ISNULL(max(case when ID = 2222 then isnull(sell_value,0) end),0) Category5,
  ISNULL(max(case when ID = 3333 then isnull(sell_value,0) end),0) Category9,
  ISNULL(max(case when ID = 4444 then isnull(sell_value,0) end),0) Category15,
  ISNULL(max(case when ID = 5555 then isnull(sell_value,0) end),0) Category25   
from @Test  
group by row_no,Id

how to pivot column to rows in Sql server 2008 R2

2 Answers2