Querying a matrix of tables - 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)

Querying a matrix of tables

Author

Topic

Carleon123
Starting Member

9 Posts

Posted - 2004-12-09 : 11:47:03

I'm sorry if this is an elementary question for some of you - in fact I hope that it is! :)

I have three tables that can be simplified as follows:

tblProjects
projectKey
customerKey
serviceKey

tblCustomers
customerKey
customerName

tblServices
serviceKey
serviceName

And I need to print a matrix report that is a list of all customers and all the possible Services they've had. Something like the following (not sure if it'll look right):


           Service1  Service2  Service3  Service4
Customer1     1          0         4         0
Customer2     0          0         2         0
Customer3     4          1         0         0
...

Any help with this query is very much appreciated!

Mike

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-09 : 12:00:30

What code do you have so far?

Some sample data would be helpful, also. Now that I think about it for a minute, it would be helpful if you supplied the DDL statements to created these tables, (including DRI, of course), and INSERT statements to populate the tables. You have already supplied the expected outcome which is helpful.

HTH

=================================================================
Hear the sledges with the bells - Silver bells!
What a world of merriment their melody foretells!
How they tinkle, tinkle, tinkle,
In the icy air of night!
While the stars that oversprinkle
All the heavens, seem to twinkle
With a crystalline delight;
Keeping time, time, time,
In a sort of Runic rhyme,
To the tintinnabulation that so musically wells
From the bells, bells, bells, bells,
Bells, bells, bells
From the jingling and the tinkling of the bells.

Happy Holidays!

Carleon123
Starting Member

9 Posts

Posted - 2004-12-09 : 14:08:48

quote:
Originally posted by Bustaz Kool

What code do you have so far?

Some sample data would be helpful, also. Now that I think about it for a minute, it would be helpful if you supplied the DDL statements to created these tables, (including DRI, of course), and INSERT statements to populate the tables. You have already supplied the expected outcome which is helpful.

HTH

Hi, thank you for the response.

I'm sorry for my ignorance, but I'm not entirely sure how to get you the DDL and DRI statements for these tables (I created them on SQL Server 2000 enterprise manager).

As for what I have so far, it's not pretty! The only solution I can think of is joining the above tables on the keys, pulling a list of all the records (grouped and sorted by customer), put them in an array using GetRows (in ASP), and then using a rather convoluted ASP loop to look at each record and place it in the proper location.

So, for example, my array of returned recordsets could look something like this:


projectKey1, customerKey1, serviceKey1, customerName1, serviceName1
projectKey2, customerKey2, serviceKey2, customerName2, serviceName2
...
projectKeyN, customerKeyN, serviceKeyN, customerNameN, serviceNameN

And the loop logic would be something like this:


For rows 0 to N
  If (it's the first row) then display a new row line

  ElseIf (it's not the first row)
    If the customerName of this row (row2) is the same as row1 then 
       Add the service to this same like
    ElseIf it's not the same customer Then
      Create a new line
  End If
Next

I hope that makes sense, if you have any other questions please let me know, I will do my best to answer them.

Thanks again,
Mike

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-09 : 14:15:15

Cross tabs, get 'em while they're hot:

http://www.sqlteam.com/searchresults.asp?SearchTerms=cross+tab

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-09 : 14:27:12

this is a classic crosstab query ( search for crosstab on this site )

select
	c.customerName,
	sum(case when p.serviceKey = 1 then 1 else 0 end) as Service1,	-- important referencing tblProjects in case
	sum(case when p.serviceKey = 2 then 1 else 0 end) as Service2,
	sum(case when p.serviceKey = 3 then 1 else 0 end) as Service3,
	sum(case when p.serviceKey = 4 then 1 else 0 end) as Service4
from
	tblCustomers c cross join tblServices s	-- sets up the matrix
	left join tblProjects p
		on c.customerKey = p.customerKey
		and s.serviceKey = p.serviceKey
group by
	c.customerName

The thing with the provided example is that it is "hardcoded",
which is ok if You don't get too many different Services I guess.

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-09 : 14:31:05

Here is an example which is more dynamic:
( or follow rob's link )

-- setup
set nocount on
create table #tblProjects( projectKey int, customerKey int, serviceKey int )
create table #tblCustomers( customerKey int, customerName as 'Customer' + ltrim(customerKey) )
create table #tblServices( serviceKey int, serviceName as 'Service' + ltrim(serviceKey) )

insert #tblCustomers(customerKey) select 1 union select 2 union select 3 union select 55 union select 66
insert #tblServices(serviceKey) select 1 union select 2 union select 3 union select 4 union select 99 union select 101
insert #tblProjects(customerKey,serviceKey) select 66,99 union all select 2,2 union all select 2,2 union all select 2,2


/************************ the work ***********************/
-- temporary table for holding dynamic sql
create table #cmd( rownum int identity, sql varchar(8000) )

-- insert the dynamic sql
insert #cmd select 'select c.customerName'
insert #cmd select ',sum(case when p.serviceKey = ' + ltrim(serviceKey) + ' then 1 else 0 end) as ' + serviceName from #tblServices
insert #cmd select 'from
	#tblCustomers c cross join #tblServices s	-- sets up the matrix
	left join #tblProjects p
		on c.customerKey = p.customerKey
		and s.serviceKey = p.serviceKey
group by c.customerName'

-- execute the dynamic sql
exec sp_execresultset 'select sql from #cmd order by rownum'
/************************ end work ***********************/

-- cleanup
drop table #cmd
drop table #tblCustomers
drop table #tblServices
drop table #tblProjects

customerName         Service1    Service2    Service3    Service4    Service99   Service101  
-------------------- ----------- ----------- ----------- ----------- ----------- ----------- 
Customer1            0           0           0           0           0           0
Customer2            0           3           0           0           0           0
Customer3            0           0           0           0           0           0
Customer55           0           0           0           0           0           0
Customer66           0           0           0           0           1           0

rockmoose

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-09 : 15:50:57

woah! hold on here! what is this? very cool, but does that WORK?
on the server i have access to here at work, I don't see sp_ExecResultSet, and this won't run ...

- Jeff

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-09 : 15:54:54

sp_execresultset is one of those ***REALLY*** cool undocumented procedures that will either work for you with no problem, or drive you utterly f---ing apeshit trying to get to work.

I wrote a cross tab procedure very much like rockmoose's and completely froze the server I ran it on. Haven't touched it since.

Carleon123
Starting Member

9 Posts

Posted - 2004-12-09 : 15:58:01

Thanks a lot guys, I'm trying to read and understand everything you've posted here (including the links) - it's just taking a little while as I've not done much more than simple Select calls before (haven't used stored procedures just yet!). I just have a couple of questions regarding the above code if you have a minute to spare!

quote:

Originally posted by rockmoose


/************************ the work ***********************/
-- temporary table for holding dynamic sql
create table #cmd( rownum int identity, sql varchar(8000) )

-- insert the dynamic sql
insert #cmd select 'select c.customerName'
insert #cmd select ',sum(case when p.serviceKey = ' + ltrim(serviceKey) + ' then 1 else 0 end) as ' + serviceName from #tblServices
insert #cmd select 'from
	#tblCustomers c cross join #tblServices s	-- sets up the matrix
	left join #tblProjects p
		on c.customerKey = p.customerKey
		and s.serviceKey = p.serviceKey
group by c.customerName'

-- execute the dynamic sql
exec sp_execresultset 'select sql from #cmd order by rownum'
/************************ end work ***********************/

-- cleanup
drop table #cmd
drop table #tblCustomers
drop table #tblServices
drop table #tblProjects

Let's assume that the tables have already been created and are already populated. I'm not entirely sure how to run the above code. I am working on an website using ASP and this is one of the reports I need to run. So I guess my question is how do I literally process the above code from ASP? Do I need to create a Stored Proc using Enterprise manager for the above? And finally, how do I retrieve the final recordset?

Again, I am sorry for my ignorance - I know it's annoying to explain things in such detail!

Thanks again for the solutions.

Mike

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-12-09 : 16:06:29

Have you guys actually seen the underlying TSQL for sp_ExecResultSet (-:

Its listed in the master db...

You will NEVER use it again after you do.....

DavidM

"Always pre-heat the oven"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-09 : 16:20:45

ah! I just found it ... scary stuff .....

- Jeff

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-09 : 16:41:25

Eeeeeeeeeeeeeewwwwwwwwwwwww, that wasn't even the one I was thinking of. I made a mistake, I meant to say xp_execresultset. THAT one is difficult to get working.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-09 : 16:42:27

There is also a "xp_execresultset".
Anyway I have used it on Win2k+SQLEnterprise, Win2003+SQLDeveloper, WinXP+SQLdeveloper (3 different installs&machines).
Always worked, that is why i posted it in good confidence.

The code in it is as Byrmol pointed out quite, what shall we say, not so nice.
But I have used it sometimes to overcome the 8000 char limit for dynamic sql.
Manually coding EXEC( sql1 + sql2 + sql2 ) is not so nice either.

Now we have robvolk's and byrmol's harsh judgement on the proc...
I really like it because it is very powerful in many situations.
And basically what it does is to just concat exec( @r1 + @r2 + .... + @r250 )
from a table and execute it for You.
Not the most efficient, maybe not the most beautiful, but does it (at least for me sometimes

).

Carleon:
The basic idea behind the code is to generate a dynamic sql clause that will perform the crosstab query for you.
You could use a variable (@crosstabsql) to hold the dynamic sql and do: exec( @crosstabsql ) or sp_executsql @crosstabsql.
The sp_executeresultset is undocumented, use it if you want to.
( also see the variety of crosstab solutions posted on this forum )

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-09 : 16:52:32

quote:
Originally posted by Carleon123
Let's assume that the tables have already been created and are already populated. I'm not entirely sure how to run the above code. I am working on an website using ASP and this is one of the reports I need to run. So I guess my question is how do I literally process the above code from ASP? Do I need to create a Stored Proc using Enterprise manager for the above? And finally, how do I retrieve the final recordset?

Yes, create a stored procedure that will generate the crosstab report for You.

Alternatively, retrieve the raw data from the db and programatically do a crosstab in your code.

rockmoose

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-09 : 16:58:25

I get


Server: Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 53
The object 'sp_ExecResultSet' does not exist in database 'master'.

Brett

8-)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-09 : 19:37:07

Only exists in SQL Server 2000, just in case you're on 7.0 still. xp_execresultset is in all versions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources