CODEWITHSUNDEEP
sql-serverpivot-table
vinGa
vinGa

Reputation: 13

Pivot Table in SQL without using Aggegate Function

I am using SQL server 2012 . I have table like

col1   col2   col3
 1     abc     AA
 2     xyz     BB
 3     def     CC

I want to convert this table into

col1   col2  col3_AA  col3_BB  col3_CC
 1     abc    AA
 2     xyz              BB
 3     def                       CC

please edit table format. I am not able to do

Upvotes: 0

Views: 47

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

The conditional aggregation might help you 

select  col1, col2,
        max(case when col3 = 'AA' then col3 end) col3_AA,
        max(case when col3 = 'BB' then col3 end) col3_BB,
        max(case when col3 = 'CC' then col3 end) col3_CC
from table
group by col1, col2
order by col1

Upvotes: 1

ravi polara
ravi polara

Reputation: 572

Try This

1. Static PIVOT query.

For example, if the column "ITEM_HEAD_ID" values can only be like 1, 2 and 3, then this is what you need. According to your given data, you can use the following query:

SELECT *, (ISNULL([1], 0 ) + ISNULL([2], 0) + ISNULL([3], 0)) AS [Total]
FROM   (SELECT [TRXID],
               [ITEM_HEAD_ID],
               [ITEM_HEAD_AMT]
        FROM   [Table]) AS t
       PIVOT (MAX([ITEM_HEAD_AMT])
             FOR [ITEM_HEAD_ID] IN ([1], [2], [3])) AS p;

Note: [Table] is the name of your table. The result:

TRXID   1   2   3   Total
6   100.00  100.00  300.00  500.00
7   100.00  100.00  300.00  500.00

Upvotes: 0

Related Questions