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..
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: