I have table with following structure:
Cust Amount Type
1 150.00 1
1 100.00 2
I would like to get query result:
Cust Type1 Type2
1 150.00 0.00
1 0.00 100.00
How to do this with query?
On Jun 5, 4:02 pm, Mirnes
> I have table with following structure:
>
> Cust Amount Type
> 1 150.00 1
> 1 100.00 2
>
> I would like to get query result:
>
> Cust Type1 Type2
> 1 150.00 0.00
> 1 0.00 100.00
>
> How to do this with query?
create table #customer( cust int,amount numeric(10,2), type int)
insert into #customer values (1,150.00,1)
insert into #customer values (1,100.00,2)
select cust,
case when type = 1 then amount else 0.00 end as type1 ,
case when type = 2 then amount else 0.00 end as type2
from #customer
drop table #customer
You can use CASE, like this:
SELECT Cust,
CASE WHEN Type = 1
THEN Amount ELSE 0.0 END AS Type1,
CASE WHEN Type = 2
THEN Amount ELSE 0.0 END AS Type2
FROM Foobar
HTH,
Plamen Ratchev
http://www.SQLStudio.com
Mirnes wrote:
> I have table with following structure:
>
> Cust Amount Type
> 1 150.00 1
> 1 100.00 2
>
> I would like to get query result:
>
> Cust Type1 Type2
> 1 150.00 0.00
> 1 0.00 100.00
>
> How to do this with query?
In addition to the CASE solution already posted, SQL Server 2005
allows some sort of explicit pivot functionality. I've never used
it, though, so I don't remember the syntax.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.