SQLTeam.com | Weblogs | Forums

Query using pivot

What have you tried so far? Where are you having issues with writing the code for the question?

1 Like

I tried ...but I'm not getting the output as shown above..

select * from (
select NAME,type , amount FROM SAMPLE1
) sourcetable
pivot(
min(AMOUNT)
for type
IN (general,specific)
)AS pivottable

I got the output like this..

Uploading: 16246836856171040970406035858753.jpg...

You need 2 pivot statements to pivot the type and to pivot the amount.

I tried..still I'm not getting..if possible can you give me code to get output

hi

i am trying this .. if i can find a solution ..I will post the code here ..

hi vasu

hopefully this helps you ( i have a billion reasons for doing this )

i had to create another column id to help me with the 2nd pivot column

please click here for drop create sample data
create table Sample_Whatever 
(
Name varchar(100), 
Type varchar(100), 
Amount Int ,
id int 
)
insert into Sample_Whatever select 'Corporate','General', 2000,1
insert into Sample_Whatever select 'Corporate','General', 3000,2
insert into Sample_Whatever select 'Corporate','Specific', 2000,3 

select * from Sample_Whatever
; WITH cte_1
     AS (SELECT NAME,
                [general],
                [general] AS ok,
                [specific]
         FROM   (SELECT NAME,
                        type,
                        amount
                 FROM   sample_whatever) AS SourceTable
                PIVOT ( Max(amount)
                      FOR type IN ([General],
                                   ok,
                                   [Specific]) ) piv),
     cte_2
     AS (SELECT [name],
                [1],
                [2],
                [3]
         FROM   (SELECT NAME,
                        type,
                        id
                 FROM   sample_whatever) AS SourceTable
                PIVOT ( Max(type )
                      FOR [id] IN ([1],
                                   [2],
                                   [3]) ) piv)
SELECT *
FROM   cte_1,
       cte_2 

Thank you sir​:slightly_smiling_face::slightly_smiling_face: