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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Question about crosstab
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

roy_kty
Starting Member

29 Posts

Posted - 09/20/2006 :  23:59:46  Show Profile  Reply with Quote
Dear all,

I use the crosstab function but face the follow problem

I have records 1,2,3,4,5 and they have types A,B,C,D,E
now I have data

1 A
1 A
2 A
1 B
1 B
2 B
4 C
5 D

so the result is :
A B C D
1 2 2 0 0
2 1 1 0 0
4 0 0 1 0
5 0 0 0 1

However the 3 and E cannot show as the data not exist in the table
so how can I show the record 3 and E that

A B C D E
1 2 2 0 0 0
2 1 1 0 0 0
3 0 0 0 0 0
4 0 0 1 0 0
5 0 0 0 1 0

if I want to add a column in the store procedure??
Moreover, how to I call store procedure in view or functions??

Thx for yours answer!!

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 09/21/2006 :  00:10:37  Show Profile  Reply with Quote

declare @table table
(
	col1	int,
	col2	char(1)
)
insert into @table
select	1, 'A'	union all
select	1, 'A'	union all
select	2, 'A'	union all
select	1, 'B'	union all
select	1, 'B'	union all
select	2, 'B'	union all
select	4, 'C'	union all
select	5, 'D'

select 	num, 
	[A] = sum(case when col2 = 'A' then 1 else 0 end),
	[B] = sum(case when col2 = 'B' then 1 else 0 end),
	[C] = sum(case when col2 = 'C' then 1 else 0 end),
	[D] = sum(case when col2 = 'D' then 1 else 0 end),
	[E] = sum(case when col2 = 'E' then 1 else 0 end) 
from	(
		select 1 as num union all select 2 union all select 3 union all
		select 4 union all select 5
	) n left join @table t
	on	n.num	= t.col1
group by num

/* RESULT
num         A           B           C           D           E           
----------- ----------- ----------- ----------- ----------- ----------- 
1           2           2           0           0           0
2           1           1           0           0           0
3           0           0           0           0           0
4           0           0           1           0           0
5           0           0           0           1           0

(5 row(s) affected)
*/



KH

Go to Top of Page

roy_kty
Starting Member

29 Posts

Posted - 09/25/2006 :  04:17:35  Show Profile  Reply with Quote
Thx for your ans!!
I try try sin
Go to Top of Page

roy_kty
Starting Member

29 Posts

Posted - 09/25/2006 :  04:52:03  Show Profile  Reply with Quote
However I cannot insert the code into the view as it does not support CASE=.=""""
how can I solve this problem!!
Really thx for your answer!!
Go to Top of Page

roy_kty
Starting Member

29 Posts

Posted - 09/25/2006 :  04:54:07  Show Profile  Reply with Quote
Moreover I use MS SQL 2000
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 09/25/2006 :  05:00:57  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
Post what you have tried till now ?

Chirag
Go to Top of Page

roy_kty
Starting Member

29 Posts

Posted - 09/25/2006 :  21:59:13  Show Profile  Reply with Quote
I have tried the sql khtan given and it normal run in sql query,
however when I put it into view it cannot work as view don't support CASE WHEN statement,
So I try to use Openquery to try the sql statement however it have the error

Server: Msg 7411, Level 16, State 1, Line 1
Server 'MyServer' is not configured for DATA ACCESS.

Which MyServer is one of the data server in my MSSQL

then I try to set its option

exec sp_serveroption '<MyServer>', 'data access', 'true'

however it occur error :
Server: Msg 15015, Level 16, State 1, Procedure sp_serveroption, Line 32
The server '<MyServer>' does not exist. Use sp_helpserver to show available servers.

I used sp_helpserver but the server exist in the first record.
By the way, I login into MyServer to execute the sql

So now I cannot solve the problem.
Moreover I don't really want to change the server option as I afraid some security problem

Thx for all replies!!

Edited by - roy_kty on 09/25/2006 22:00:03
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 09/26/2006 :  01:56:53  Show Profile  Reply with Quote
"however when I put it into view it cannot work as view don't support CASE WHEN statement,"
Can you post your query ?


KH

Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 09/26/2006 :  02:06:50  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
quote:
however when I put it into view it cannot work as view don't support CASE WHEN statement


View does support Case When End

Post what you are trying to code in your view...

Openquery , is normally used when you want to retrive records from the different servers... why are you using the OpenQuery..

BTW, you are getting that error since you must have not registered your server for registering the server, check sp_addlinkedserver in BOL (Book online. SQL Server Help)


Chirag
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.99 seconds. Powered By: Snitz Forums 2000