CODEWITHSUNDEEP
sqlsql-server-2008pivot
Muhammad Usman
Muhammad Usman

Reputation: 10148

Unable to pivot data in SQL

I have an SQL query like 

select name from customers where id in (1,2,3,4,5)

this will return me 5 rows. What I am trying is

select Q1,Q2,Q3,Q4,Q5  from(select name from customers where id in (1,2,3,4,5)  
)d pivot( max(name) for names in (Q1,Q2,Q3,Q4,Q5)
 ) piv;

to convert these 5 rows into 5 columns.

But I am getting null values in columns.

enter image description here

I don't know where am I doing wrong. Any kind of help , suggestion would be appreciated.

Thanks

Upvotes: 0

Views: 58

Answers (1)

Shushil Bohara
Shushil Bohara

Reputation: 5656

You can try in this way, you are pivoting the name and selecting the name under value also so please first make is sure if there is a value for that ids and retrieve the value for which you are trying pivoting:

create table #customers(id int, name varchar(50), fee int) 
insert into #customers values
(1, 'Q1', 100),
(2, 'Q2', 200),
(3, 'Q3', 300),
(4, 'Q4', 400),
(5, 'Q5', 500),
(6, 'Q5',  600),
(7, 'Q5',  700)

select Q1,Q2,Q3,Q4,Q5  
from(select name, fee from #customers where id in (1,2,3,4,5)  
)d pivot(SUM(fee) for name in (Q1,Q2,Q3,Q4,Q5)
 ) piv;

OUTPUT:

Q1  Q2  Q3  Q4  Q5
100 200 300 400 500

What respective value you want to display under name, if you mention that then we can provide more exact solution for that. Hope you can change your code accordingly.

Upvotes: 1

Related Questions