Question about crosstab - 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

Site Related Forums

Article Discussion

Question about crosstab

Author

Topic

roy_kty
Starting Member

29 Posts

Posted - 2006-09-20 : 23:59:46

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)

17689 Posts

Posted - 2006-09-21 : 00:10:37

[code]
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)
*/
[/code]

roy_kty
Starting Member

29 Posts

Posted - 2006-09-25 : 04:17:35

Thx for your ans!!
I try try sin

roy_kty
Starting Member

29 Posts

Posted - 2006-09-25 : 04:52:03

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!!

roy_kty
Starting Member

29 Posts

Posted - 2006-09-25 : 04:54:07

Moreover I use MS SQL 2000

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-25 : 05:00:57

Post what you have tried till now ?

Chirag

roy_kty
Starting Member

29 Posts

Posted - 2006-09-25 : 21:59:13

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!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-26 : 01:56:53

"however when I put it into view it cannot work as view don't support CASE WHEN statement,"
Can you post your query ?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-26 : 02:06:50

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources