cross tabs quesry?
-
Hi folks,
I am trying to dynamically add columns to my result set.I believe that I may need a cross tabs query.
Essentially, what I want to do is list all the attributes that a specific product posseses, I would like to also display the values of these attributes.
schema:
tblProduct
prodId
prodName
tblAttribute
attribId
attribName
tblProductAttribute
prodId (refs tblProduct)
attribId (refs tblAttribute)
prodAttribValue
QUERY:
SELECT DISTINCT
p.prodId,
p.prodName,
a.attribName,
pa.attribValue
FROM
tblProduct p,
tblProductAttribute pa,
tblAttribute a
WHERE p.prodId = pa.prodId
AND a.attribId = pa.attribId
AND p.prodId = 0
This gets me the following:
prodId prodName attribName attribValue
0 vwBeetle height 100
0 vwBeetle length 200
What I would like is:
prodId prodName height length
0 vwBeetle 100 200
Is this possible?
Please let me know if i need to clarify anything.
Cheers,
yogi
-
Check out lowphat's post from a couple of days ago:
-
Hi Jmasden,
Cheers for the reply.
I see that this example works for a pre-determined number of columns.
In my example, a product will have a variable number of corresponding attribute columns, eg,
a product could contain:
1) a single attribute, eg "height"
2) multiple attributes, eg
a) height
b) weight
c) length
(sorry for not making that clear in my original post)
Unfortunately, I do not see how I could translate this requirement into tsql.Could anyone give me a push in the right direction?
Many thanks,
yogi.
-
You will need to use dynamic SQL. I haven't tried the code below, so there may be errors, but it should give you the idea.
declare @attid int, @attname varchar(255)
declare @sql_start varchar(1000), @sql_end varchar(1000)
declare @fixed_len int
declare @sql6 varchar(8000), @sql5 varchar(8000), @sql4 varchar(8000)
--multiple strings in case the query gets too long.
--have only used three here, but you get the idea.
--set to '' to avoid concatenating nulls.
select @sql6 = '', @sql5 = '', @sql4 = ''
--define the start of the query string
set @sql_start =
'select
p.prodId,
p.prodName'
--define the end of the query string
set @sql_end =
'
from tblProduct p
join tblProductAttribute pa
on p.prodId = pa.prodId
join tblAttribute a
on a.attribId = pa.attribId
where p.prodId = 0
order by p.prodName
'
--store the combined length of the start and end strings
set @fixed_len = len(@sql_start) + len(@sql_end)
---cursor through all the required attributes.
--The FROM and WHERE clauses in this query should match
--those in @sql_end, except that we can leave out the Product table
--as the WHERE clause uses Product ID rather than Product Name.
--I would recommend NOT using hardcoded key values though.
declare cr cursor
for
select distinct a.attribId, a.attribName
from tblAttribute a
join tblProductAttribute pa
on a.attribId = pa.attribId
where pa.prodId = 0
order by a.attribName
open cr
fetch cr into @attid, @attname
while @@fetch_status = 0
begin
--build the string to populate the next attribute column
set @sql =
','
+ char(10)
+ case a.attribId when '
+ cast(@attid as varchar)
+ ' then attribValue end ['
+ @attname
+ ']'
--if the next string would overflow the variable,
--shift the variables along to make more room.
--otherwise, just add the new string on
if @fixed_len + len(@sql) + len(@sql6) > 8000
select @sql4 = @sql5, @sql5 = @sql6, @sql6 = @sql
else
set @sql6 = @sql6 + @sql
fetch cr into @attid, @attname
end
close cr
deallocate cr
--print out the contents of the variables - for checking only
print @sql_start
print @sql6
print @sql5
print @sql4
print @sql_end
--execute the string to fetch the results.
exec(@sql_start + @sql6 + @sql5 + @sql4 + @sql_end)
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
This link might also help you
http://qa.sqlservercentral.com/forum/link.asp?TOPIC_ID=16321
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I've just used the code for this problem as an example for the junior DBA I am currently training - in the process I fixed a couple of errors. The correct code follows - though I don't know whether prodAttribValue or AttribValue is correct (you specify both). I've used prodAttribValue.
declare @attid int, @attname varchar(255)
declare @sql varchar(8000)
declare @sql_start varchar(1000), @sql_end varchar(1000)
declare @sql6 varchar(8000), @sql5 varchar(8000), @sql4 varchar(8000)
--multiple strings in case the query gets too long.
--have only used three here, but you get the idea.
--set to '' to avoid concatenating nulls.
select @sql6 = '', @sql5 = '', @sql4 = ''
--define the start of the query string
set @sql_start =
'select
p.prodId,
p.prodName'
--define the end of the query string
set @sql_end =
'
from tblProduct p
join tblProductAttribute pa
on p.prodId = pa.prodId
join tblAttribute a
on a.attribId = pa.attribId
--where p.prodId = 0
--group by p.prodId, p.prodName
order by p.prodName
'
---cursor through all the required attributes.
--The FROM and WHERE clauses in this query should match
--those in @sql_end, except that we can leave out the Product table
--as the WHERE clause uses Product ID rather than Product Name.
--I would recommend NOT using hardcoded key values though.
declare cr cursor
for
select distinct a.attribId, a.attribName
from tblAttribute a
join tblProductAttribute pa
on a.attribId = pa.attribId
--where pa.prodId = 0
order by a.attribName
open cr
fetch cr into @attid, @attname
while @@fetch_status = 0
begin
--build the string to populate the next attribute column
set @sql =
','
+ char(10)
+ '--max(
case a.attribId when '
+ cast(@attid as varchar)
+ ' then prodattribValue end
--)
['
+ @attname
+ ']'
--if the next string would overflow the variable,
--shift the variables along to make more room.
--otherwise, just add the new string on
if len(@sql) + len(@sql6) > 8000
select @sql4 = @sql5, @sql5 = @sql6, @sql6 = @sql
else
set @sql6 = @sql6 + @sql
fetch cr into @attid, @attname
end
close cr
deallocate cr
--print out the contents of the variables - for checking only
/*
print @sql_start
print @sql6
print @sql5
print @sql4
print @sql_end
*/
--execute the string to fetch the results.
exec(@sql_start + @sql6 + @sql5 + @sql4 + @sql_end)
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
oops - I don't know if you are reading any of this, but I commented some bits of the code out in the version I posted - the WHERE clause (twice), the group by clause (once), and the 'max' function and closing bracket (once). Remove all comment marks in the code to get the correct results.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Hi folks,
Thanks for all the replies.I am working my way thru them at the minute.(my post was a bit contrived, for the sake of clarity and simplicity)I'll post my progress.
cheers,
yogi
-
Hi folks,
Stax, worked like a charm.I'd say the junior dba is in good hands :-),
the stephen forte weblog in the link is also an eye opener.
cheers.
yogi
-
quote:
This link might also help youhttp://qa.sqlservercentral.com/forum/link.asp?TOPIC_ID=16321
Frank
That method has a real name (Characteristic Functions), has been around for about a decade and has even been copyrighted, according to Transact-SQL Programming (O'Reilly Press). It's completely unnecessary if you have CASE expressions; Forte deserves to get some ripe vegetables thrown at him if he wastes people's time at seminars with this obsolete and opaque technique.--Jonathan
--Jonathan -
Here's a solution I came up with a few weeks ago:
http://qa.sqlservercentral.com/scripts/contributions/936.asp
-
quote:
That method has a real name (Characteristic Functions), has been around for about a decade and has even been copyrighted, according to Transact-SQL Programming (O'Reilly Press). It's completely unnecessary if you have CASE expressions; Forte deserves to get some ripe vegetables thrown at him if he wastes people's time at seminars with this obsolete and opaque technique.
Learn each day something new! Once again, Jonathan, thanks for hooking right in. In this case ripe vegetables are not enough, what about raw ?!? eggs
Wow, while typing this Web Ferret returned 41 hits on 'Characteristic Functions'. Have a great weekend, I have something to learn
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Sure interesting the number of times this type of problem crops up. For problems like this cursors seem the best bet as trying to build a dynamic query can sometimes exceed variable size limits. I did post on another topic a solution using a temp table and small bits of dynamic queries.
Something like this
Find out maximum id of columns (attribId)
Create temp table containing only key info (prodId & prodName)
use while loop to loop thru ids (to max) to use dynamic query to add nullable column (value of attribName) to temp table
insert key info into temp table(prodId & prodName only)
use while loop to loop thru ids (to max) and update each column in turn with data
then select from temp table
Not neccessarily elegant but functional.
Far away is close at hand in the images of elsewhere.
Anon. -
Good evening David,
quote:
Sure interesting the number of times this type of problem crops up. For problems like this cursors seem the best bet as trying to build a dynamic query can sometimes exceed variable size limits. I did post on another topic a solution using a temp table and small bits of dynamic queries.
can't let loose from work?
I agree, it's amazing how often this question or something very similar is asked. And more and more I come to the conclusion:
Do this at the client. The server should be used for data retrieval, not presentation.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
I agree, it's amazing how often this question or something very similar is asked. And more and more I come to the conclusion:Do this at the client. The server should be used for data retrieval, not presentation.
Frank
Yes!
This is the sort of thing report writers are good at. I was helping someone with one of these needs once, and she casually mentioned that the number of columns was dynamic and could easily be in the hundreds.
I asked her who would be able to print and read a report like that and she said it would end up in Excel. I suggested she just write a little Excel VBA to write the values across. She decided I wasn't enough of an expert and reposted her question to another SQL Server list! I understand that Access even has a built-in function for this, so I guess one could also just link into the SQL tables from Access and then do it there. Microsoft has caved and has PIVOT and UNPIVOT operators in Yukon.
quote:
In this case ripe vegetables are not enough, what about raw ?!? eggs
Good pun, Frank! And the reference to raw eggs is very appropriate on Halloween.
I have the book I referenced at home, so I checked it last night. Rozenshtein and his co-authors trademarked (not copyrighted) the name. I suppose the idea interests you because you like history. Here's an article on characteristic functions in ancient T-SQL:
http://www.trio-soft.com/technicalExpertise/downloadSources/bitmap_character.pdf
That concludes with a quote from Andrew Zalensky (author of my book:
quote:
CASE is, in all respects, better than characteristic functions. It shows the same or even better performance. It’s tremendously easier to program and support. I’ve been advising all of my clients to use CASE instead of characteristic functions ever since CASE became available.
--Jonathan
--Jonathan
That method has a real name (Characteristic Functions), has been around for about a decade and has even been copyrighted, according to Transact-SQL Programming (O'Reilly Press). It's completely unnecessary if you have CASE expressions; Forte deserves to get some ripe vegetables thrown at him if he wastes people's time at seminars with this obsolete and opaque technique.
I asked her who would be able to print and read a report like that and she said it would end up in Excel. I suggested she just write a little Excel VBA to write the values across. She decided I wasn't enough of an expert and reposted her question to another SQL Server list! 
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply