How to set null values to zero when pivoting

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to set null values to zero when pivoting

Author

Topic

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-06-04 : 19:52:39

I have a table with the following structure

[ID] [A] [B] [Cnt] [Qty]
1 s v1 4 40
2 g v1 2 23
3 p v2 7 22
4 l v3 1 6
5 v v4 7 5

Since I do not know before hand what [B] will be , I have created a dynamic script to pivot the data :

Select *
from ( select [ID],[A],[B],sum([Cnt]) AS Cnt
group by [ID],[A],[B]
) a
PIVOT ( sum(cnt) for [B] in (@list)) b

Now thequstion is :
1. How do I amend this qry to eliminate nulls in my output
[ID] [A] [v1] [v2] ......[vn]
1 s null 9
2 g 10 null

2. Is it possible to include both [cnt] and [Qty] traspose along
[B] = v1,v2 ...... vn

Any advice will be most welcome.

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-04 : 20:00:53

Check Coalesce in Books online

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-06-04 : 20:10:24

I tried this SUM(COALESCE([Cnt],0 )) and sum(Isnull([Cnt],0)0 , but this does not give me the desired results

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-05 : 00:20:07

Try giving COALESCE in select list

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-06-05 : 07:23:27

since my select list of the of the [Cnt] columns is dynamic , can somebody advice on
how i could dynamically implement the followiing

Select [ID],[A],COALESCE([V1]) as [v1],COALESCE([V2]) as [v2],....,COALESCE([Vn]) as [vn],
from ( select [ID],[A],[B],sum([Cnt]) AS Cnt
group by [ID],[A],[B]
) a
PIVOT ( sum(cnt) for [B] in (@list))

v1 .....,vn is my dynamic list.

Advice welcomed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources