Jon rows(is it possible?) - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Jon rows(is it possible?)

Author

Topic

dnagahawatte
Starting Member

24 Posts

Posted - 2004-05-20 : 04:52:51

Hi Guys
I have a view which takes data from two tables (Employee and courses)
It gives me a row per entry, means if one employee has done 3 courses it gives me three rows under the same employee.
Can i get this in to a single row with courses they have done as columns in that row.
I am trying to display these data in a asp.net Datagrid.
please help!!
thanks

gpl
Posting Yak Master

195 Posts

Posted - 2004-05-20 : 05:08:38

Have a look at this article, it may be everything you need
http://www.sqlteam.com/item.asp?ItemID=2955
Graham

dnagahawatte
Starting Member

24 Posts

Posted - 2004-05-20 : 18:55:48

quote:
Originally posted by gpl

Have a look at this article, it may be everything you need
http://www.sqlteam.com/item.asp?ItemID=2955
Graham

Hi Graham.
Thank you very much for the link u supplied.Thats almost everything i need.Except for one small thing.
How i get rid of the null values it produces.
As an example, as it says in the article i have used ISnull
Pubs example:--
EXECUTE crosstab 'select title from titles inner join sales on (sales.title_id=titles.title_id)
group by title', 'sum(ISNULL(qty,2))','ISNULL(stor_id,0)','stores'

I still get null values.
What am i doing wrong.?
Thanks

dnagahawatte
Starting Member

24 Posts

Posted - 2004-05-21 : 04:19:48

If anybody can help me with above problem really appreciate it.
Thanks

gpl
Posting Yak Master

195 Posts

Posted - 2004-05-21 : 04:31:12

I have never actually used this procedure, but from your example try the following change

EXECUTE crosstab 'select title from titles inner join sales on (sales.title_id=titles.title_id)
group by title', 'ISNULL(sum(ISNULL(qty,2)) , 0)','ISNULL(stor_id,0)','stores'

I have placed an ISNULL around your sum statement, so that nulls there are returned as zero, this might work

Graham

dnagahawatte
Starting Member

24 Posts

Posted - 2004-05-21 : 04:49:15

Thank you very much for the quick reply.
I tried this method and it gives me following error.
So it seems that this is the corect way to do it but syntax is incorrect.What is wrong, i cant see it yet?
Hope u have time to have a look and help me.
Thanks

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.

gpl
Posting Yak Master

195 Posts

Posted - 2004-05-21 : 06:53:11

I have had a closer look at the article, I see why my suggestion did not work - the crosstab procedure extracts the sum part and the isnull just confuses it.

Maybe someone else who has used this has an idea

Graham

dnagahawatte
Starting Member

24 Posts

Posted - 2004-06-01 : 06:24:41

i found the answer for this if anybody interested
sum(qty else 0)

Thanks for all ur help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources