April 18, 2003 at 10:37 pm
I have a table
org_id activity_id activity_type activity score
-------------------------------------------------
1 345 cert1 12
1 345 cert2 1
1 346 cert1 12
1 346 cert2 1
how can i get a table that looks like this
org_id activity_id cert_1 cert_2
------------------------------------
1 345 12 1
1 346 12 1
any ideas?
April 18, 2003 at 11:19 pm
trinity, could you give us some more info? Specifically, the relationship between (I think this is it) activity and cert_x and/or activity_type and cert_x? Or is it activity and score and/or activity_type and/or score?
Regards,
SJTerrill
April 18, 2003 at 11:37 pm
we3ll i will tell you as much as i can (im under nda)with out showing you real data
basicaly the table is formated as in the first exsample. The cert1, cert2..etc are certifacations. score is the score given
some activites have scores for multiple certs
some do not
i need to see a veiw with one activity per row with all the cert scores listed under a column for each cert type. or should i say i need to cereate a view that looks like the second exsample
April 21, 2003 at 3:47 am
What you are loking for is called a Pivot table. In SQL The way to do this is by using CASE and GROUP BY to move the data sideways.
With a finite number of column possiblities you can simply do.
SELECT
org_id,
activity_id,
SUM(CASE activity_type WHEN 'cert1' THEN activity score ELSE 0 END) AS cert_1,
SUM(CASE activity_type WHEN 'cert2' THEN activity score ELSE 0 END) AS cert_2,
SUM(CASE activity_type WHEN 'cert3' THEN activity score ELSE 0 END) AS cert_3
FROM
tbl_X
GROUP BY
org_id,
activity_id
Or if unknown you might try working this script to your needs (there is an issue I received an email on but don't have in front of me or have corrected yet). http://qa.sqlservercentral.com/scripts/contributions/204.asp
Or the best way you can do if possilbe is to handle this in your presentation layer.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply