Crosstab Queries - 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

Transact-SQL (2000)

Crosstab Queries

Author

Topic

Bernhard
Starting Member

12 Posts

Posted - 2004-09-29 : 09:37:07

hi!

I've got three crosstab queries from ms access which should work at msde/sqlserver.

two shouldn't be a problem, because the number of columns is static.
but they doesn't work at all. I've allready read some articles about the same problem, but nothing worked.
so here is the statement:
CREATE VIEW QRY_Bounces
AS
SELECT myTBL.Somedatahere,
[1] = COUNT(CASE [TBL_Stats_Campaigns].[IDBounce] WHEN 1 THEN 1 ELSE 0 END),
[2] = COUNT(CASE [TBL_Stats_Campaigns].[IDBounce] WHEN 2 THEN 1 ELSE 0 END)
FROM [TBL_Stats_Campaigns], myTBL
WHERE SomeContraintshere
GROUP BY myTBL.Somedatahere
GO

Explanation:
IDBounce is a ID from an other table.
If IDBounce = 1 It should be count at column 1
If IDBounce = 2 It should be count at column 2
Now all data at column 1 and column 2 are the same! WHY
please help me!

thx
Bernhard

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-09-29 : 09:43:59

Change your COUNT to a SUM.

Mark

Bernhard
Starting Member

12 Posts

Posted - 2004-10-01 : 03:44:58

Thank you very much, this works!

The last query i don't know how to realise at sql server is the following:

This is a dynamic crosstab-query.
Take a table TBL_Subscribers with the following entries:
ID = 1
ID = 2
ID = 3
ID = 4
Take a table TBL_Interests with the following entries:
ID = 1
ID = 2
ID = 3
Then take a table TBL_Subscribers_Interests with the following entries:
ID IDSubscriber IDInterest
1 1 1
2 1 2
3 2 1

Then the query should bring the following:
IDSubscriber 1 2 3
--------------------
1 1 1 0
2 1 0 0
3 0 0 0
4 0 0 0

OK?
If a add new data at the TBL_Interests the query should look like the following:
IDSubscriber 1 2 3 4
------------------------
1 1 1 0 0
2 1 0 0 0
3 0 0 0 0
4 0 0 0 0
So the columns are the ids of the entries at the table TBL_Interests and this dynamically!

How can i do that?
with a query?
with a stored procedure?
???

At msAccess it would look a little bit like the following:
TRANSFORM Count(TBL_Subscriber_Interests.ID) AS Int_Selected
SELECT TBL_Subscribers.ID
FROM TBL_Subscribers LEFT JOIN (TBL_Interests RIGHT JOIN TBL_Subscriber_Interests ON TBL_Interests.ID = TBL_Subscriber_Interests.IDInterest) ON TBL_Subscribers.ID = TBL_Subscriber_Interests.IDSubscriber
GROUP BY TBL_Subscribers.ID
PIVOT TBL_Interests.ID;

But this also only works if at least one subscriber has got a interest.

thank you very much!
Bernhard

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-10-01 : 07:30:47

smth like following

declare @sql as varchar(8000), @i int

set @i= (select count(distinct IDSubscriber) from [TBL_Stats_Campaigns]) --here i mistaked before
set @sql='SELECT IDSubscriber'
while @i>0
begin
set @sql=@sql+',['+ cast(@i as varchar)+']=SUM(CASE [TBL_Stats_Campaigns].[IDBounce] WHEN '+cast(@i as varchar)+'THEN 1 ELSE 0 END)'
set @i=@i-1
end
set @sql=@sql+' FROM [TBL_Stats_Campaigns], myTBL WHERE SomeContraintshere GROUP BY myTBL.Somedatahere'

exec(@sql)
--or
exec sp_executesql @sql

Bernhard
Starting Member

12 Posts

Posted - 2004-10-04 : 03:20:20

sorry, but i've to ask you again.
So I think the code you postet is quite ok.

i put it into a stored procedure.
CREATE PROCEDURE sp_Subscribers_Interests AS
DECLARE @sql AS VARCHAR(8000), @i INT

SET @i= (SELECT COUNT(DISTINCT IDSubscriber) FROM [TBL_Stats_Campaigns])
SET @sql='SELECT IDSubscriber'
WHILE @i>0
BEGIN
SET @sql=@sql+',['+ cast(@i AS VARCHAR)+']=SUM(CASE [TBL_Stats_Campaigns].[IDBounce] WHEN '+cast(@i AS VARCHAR)+'THEN 1 ELSE 0 END)'
SET @i=@i-1
END
SET @sql=@sql+' FROM [TBL_Stats_Campaigns], myTBL WHERE SomeContraintshere GROUP BY myTBL.Somedatahere'

exec(@sql)
--or
--exec sp_executesql @sql
GO

but how can I use this now?
I think I'll need a view which is executing this sql-statement which was built at this procedure.

[I never did something with stored procedures. Only at oracle some years ago. So I've forgotten this things]

thx

Bernhard
Starting Member

12 Posts

Posted - 2004-10-04 : 03:25:46

ehm. Of course i changed the needed parameters. It looks now like:
CREATE PROCEDURE sp_Subscribers_Interests AS
DECLARE @sql AS VARCHAR(8000), @i INT

SET @i= (SELECT COUNT(DISTINCT IDSubscriber) FROM [TBL_Stats_Campaigns])
SET @sql='SELECT IDSubscriber, [TBL_Subscribers].[IDAccount]'
WHILE @i>0
BEGIN
SET @sql=@sql+',['+ cast(@i AS VARCHAR)+']=SUM(CASE [TBL_Stats_Campaigns].[IDBounce] WHEN '+cast(@i AS VARCHAR)+'THEN 1 ELSE 0 END)'
SET @i=@i-1
END
SET @sql=@sql+' FROM [TBL_Stats_Campaigns], [TBL_Subscribers]GROUP BY [TBL_Subscribers].[IDAccount], [TBL_Stats_Campaigns].[IDSubscriber]'

exec(@sql)
--or
--exec sp_executesql @sql
GO

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-10-04 : 07:14:25

sp is used like:

exec sp_Subscribers_Interests

Bernhard
Starting Member

12 Posts

Posted - 2004-10-04 : 09:12:48

This doesn't work.
Wrong syntax near [a column which isn't at the sp]???

i created a view and wrote
exec sp_Subscribers_Interests

Bernhard
Starting Member

12 Posts

Posted - 2004-10-04 : 09:14:18

Or there's a message one row affected.
So i don't get the view!
grrr

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-10-04 : 09:19:58

BOL:

Rewriting Stored Procedures as Functions
This topic describes how to determine whether to rewrite existing stored procedure logic as user-defined functions. For example, if you want to invoke a stored procedure directly from a query, repackage the code as a user-defined function.

In general, if the stored procedure returns a (single) result set, define a table-valued function. If the stored procedure computes a scalar value, define a scalar function.

Criteria for Table-Valued Functions
If a stored procedure meets the following criteria, it is a good candidate for being rewritten as a table-valued function:

The logic is expressible in a single SELECT statement but is a stored procedure, rather than a view, only because of the need for parameters. This scenario can be handled with an inline table-valued function.

The stored procedure does not perform update operations (except to table variables).

There is no need for dynamic EXECUTE statements

The stored procedure returns one result set.

The primary purpose of the stored procedure is to build intermediate results that are to be loaded into a temporary table, which is then queried in a SELECT statement. INSERT...EXEC statements can be written using table-valued functions. For example, consider the following sequence:
INSERT #temp EXEC sp_getresults
SELECT ...
FROM #temp, t1
WHERE ...

The sp_getresults stored procedure can be rewritten as a table-valued function, for example fn_results(), which means the preceding statements can be rewritten as:

SELECT ...
FROM fn_results(), t1
WHERE ...

You may use UDF in View - not SP. Try to rewrite your SP to UDF as shown above

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-10-05 : 02:32:11

And ... why do you need to create UDF or View except of using SP directly on your client software?

Bernhard
Starting Member

12 Posts

Posted - 2004-10-05 : 04:15:56

Cause the client software allready exists. There are many dynamic created sql statements all over the webapplication. the database is an ms access db. now we want the msde as database.
the problem is the ms access supports dynamic crosstab queries. msde/sql server not.

so i have to create this dynamicly.
thank you

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-10-05 : 04:28:21

I had similar task. Converting existing mdb to adp. Good luck

Bernhard
Starting Member

12 Posts

Posted - 2004-10-05 : 04:46:42

Thank you, I hope I'll find all differences between "sql" and "sql" *g* before the user will find it!

Bernhard
Starting Member

12 Posts

Posted - 2004-10-07 : 11:40:19

Not yet a solution

I looked at functions:
CREATE FUNCTION [name] ([{@Parametername...}])
RETURNS [tblname] TABLE {TABLEDEFINITOIN!}
[AS]
BEGIN
...
RETURN ..
END

So there have to be a tabledefinition before the begin statement. the tabledefiniton is dynamic!

how can i do this?

i get cracy with this! I'm looking forward at november where i'm going to do a mcp course for sqlserver2000

please help!
thx

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-10-08 : 08:23:43

CREATE PROCEDURE crosstab
@source varchar(1000),
@fields varchar(100)=null, --when null, then without grouping
@func varchar(100),
@pivot varchar(1000),
@filtr varchar(1000)=null,
@into varchar(100)=null
AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
set @sql='SELECT DISTINCT ' + @pivot + ' as pivot INTO ##pivot FROM ' + @source + ' WHERE ' + isnull(@filtr+' AND ','') + @pivot + ' Is Not Null'
EXEC (@sql)

SELECT @sql='', @func=stuff(@func, len(@func), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
'isnull('+stuff(@func,charindex( '(', @func )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ',0), ' FROM ##pivot order by pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)

SELECT @sql='SELECT * from (SELECT '+isnull(@fields+',','')+@sql+isnull(' INTO '+@into,'')+' FROM '+@source+isnull(' WHERE '+@filtr,'')+isnull(' GROUP BY '+@fields,'')+') a WHERE '
while charindex(',',@fields)>0
set @fields=stuff(@fields, charindex(',',@fields),1,' is not null AND ')
set @fields=@fields+' is not null'
set @sql=@sql+@fields

EXEC (@sql)

SET ANSI_WARNINGS ON
GO

Specially for Bernhard:

exec crosstab
'(SELECT a.IDSubscriber, a.IDAccount, TBL_Interests.ID
FROM TBL_Interests full join (SELECT TBL_Subscribers.ID AS IDSubscriber, TBL_Subscriber_Interests.IDInterest AS IDInterest, TBL_Subscribers.IDAccount
FROM TBL_Subscribers LEFT OUTER JOIN
TBL_Subscriber_Interests ON TBL_Subscribers.ID = TBL_Subscriber_Interests.IDSubscriber) a
ON TBL_Interests.ID = a.IDInterest) b',
'IDSubscriber,IDAccount',
'count(ID)',
'ID'

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-10-09 : 05:29:35

sorry of above

exec crosstab
'tbl_interests b full join tbl_subscribers a full join TBL_Subscribers_Interests c on IDSubscriber=a.ID on IDInterest=b.id',
'a.id',
'count(c.ID)',
'b.ID'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources