dynamic columns?
-
hi, i'm wondering if its possible to have a view that takes row values and turns them into columns. for example, i'm capturing data like this:
name | question | value
john | rate product | 5
rob | rate product | 4
joe | rate service | 3
but i would like to have a view like this
question | joe | john | rob
rate product |null | 5 | 4
rate service | 3 | null | null
any help would be greatly appreciated
-
This type of thing is really best handled by a report generator, but you can do it with SQL.
CREATE
TABLE table1 (Name varchar(25), question varchar(25), value int)
GO
INSERT
INTO table1
SELECT
'john', 'rate product', 5
INSERT
INTO table1
SELECT
'rob', 'rate product', 4
INSERT
INTO table1
SELECT
'joe', 'rate service', 3
GO
CREATE
VIEW v_CrossTab
AS
SELECT
question,
SUM(CASE name WHEN 'john' THEN value ELSE NULL END) as 'John',
SUM(CASE name WHEN 'joe' THEN value ELSE NULL END) as 'Joe',
SUM(CASE name WHEN 'rob' THEN value ELSE NULL END) as 'Rob'
FROM
dbo.table1
GROUP
BY question
SELECT
* FROM v_CrossTab
As you can see, this approach is workable only if there are a limited, and fairly stable number of people in the table. It will also fail to work if a given person has more than one value for a given question. So, if we added another record for John like this:
INSERT
INTO table1
SELECT
'john', 'rate product', 4
The view would now report the value for 'rate product' for john as being 9. You could use MIN or MAX instead of SUM, but then your results might not be what you want either...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
thanks! is it possible to dynamically enter the number of columns? for example - if there were 3 respondants there would be 4 columns - the 3 respondants plus the questions, but if there were 5 respondants, there would be 6 columns - the 5 respondants plus the question.
-
Almost anything is possible, but it would get ugly pretty fast. Just off the top of my head, I think you would have to declare a cursor to loop through the table looking for distinct respondants, then use dynamic SQL to build the view defininition on the fly.
SQL was never intended, and is wholly inadequate as a report writing language. Any formatting should ideally be done in the presentation layer of the application.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
yeah i'm definitely abusing what it was originally intended for, but i'm converting a web app from asp over to asp.net, and i'm trying to make HTML tables that used to be created by asp instead be bound to datagrids directly from my sql server. thanks for all of your help, its definitely put me in the right direction
-
Thom,
I tend to agree that you might meet this requirement better on the client side, but if you want to do dynamic crosstabs in SQL, try one of these. The handle indeterminate (non-stable) columns.
There are a number of scripts that will do this dynamically if you can't determine ahead of time what the column set is. Try: http://www.sqlteam.com/item.asp?ItemID=2955
There's even a 3rd party product with a 30-day full eval period:
Hope one of these helps,
Scott Thornburg
-
thanks guys, i'll let you know how it turns out.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply