December 7, 2011 at 3:38 pm
given a fund table with many classifications, how can I build a view that shows the funds, each classification and whether or not that particular fund is classed or not?
fund_id
1
2
3
4
classification
fund_idclassification
1 CASH
1 UK
2 GOLD
3 CASH
3 USA
3 SHORT-TERM
fund_idcashukgoldusaSHORT-TERM
1 truetruefalsefalsefalse
2 falsefalsetruefalsefalse
3 truefalsefalsetruetrue
December 7, 2011 at 3:57 pm
Depending on the classification values being static or not, it's either a CrossTab or a DynamicCrossTab scenario. Both concepts are described in the related links in my signature.
As a side note: such task should be performed at the presentation layer, if possible.
December 7, 2011 at 4:08 pm
Thanks Lutz, reading your article now. very much appreciated.
First time I used this site and very impressed.
Cheers,
Gerry.
December 7, 2011 at 4:19 pm
gerardquinn43 (12/7/2011)
Thanks Lutz, reading your article now. very much appreciated.First time I used this site and very impressed.
Cheers,
Gerry.
Glad I could help and welcome aboard!
If you find the time, you might want to have a glance at the first two articles in my signature. They'll describe how to post questions in the most efficient way (based on the view of the people trying to help you 😉 ).
Some ready to use sample data will not only increase the number of people working on a solution. You'll also get a tested solution. 😉
I agree, this site is simply awesome. There are so many smart people around willing to share their knowledge. I learn something new every time I lurk around.
December 7, 2011 at 4:21 pm
With a pivot table you usually have the Aggregate, with this, I'm not trying to sum or count anything.
I just want to layout and understand for each id, all it's classifications, it so happens these classifications are rows and are dynamic. Most have 2-5 different classifications.
Thanks,
December 7, 2011 at 4:36 pm
Here are some code snippets based on some read to use sample data (do you recognize the data?)
-- sample data in a ready to use format:
DECLARE @tbl TABLE
(
fund_id INT, classification VARCHAR(30)
)
INSERT INTO @tbl
VALUES(1 ,'CASH'),
(1 ,'UK'),
(2 ,'GOLD'),
(3 ,'CASH'),
(3 ,'USA'),
(3 ,'SHORT-TERM')
And this is what I meant with "tested solutions":
-- version 1: without aggregate
-- effect: every row from @tbl will be returned with the related value
SELECT
fund_id,
CASE WHEN classification ='CASH' THEN 'True' ELSE 'False' END AS CASH,
CASE WHEN classification ='UK' THEN 'True' ELSE 'False' END AS UK,
CASE WHEN classification ='GOLD' THEN 'True' ELSE 'False' END AS GOLD,
CASE WHEN classification ='USA' THEN 'True' ELSE 'False' END AS USA,
CASE WHEN classification ='SHORT-TERM' THEN 'True' ELSE 'False' END AS [SHORT-TERM]
FROM @tbl
-- version 2: with aggregate
-- effect: there will only be one row per fund_id showing the max. value for each column ("Max" would here refer to the "greater" value of "True" and "False")
SELECT
fund_id,
MAX(CASE WHEN classification ='CASH' THEN 'True' ELSE 'False' END) AS CASH,
MAX(CASE WHEN classification ='UK' THEN 'True' ELSE 'False' END) AS UK,
MAX(CASE WHEN classification ='GOLD' THEN 'True' ELSE 'False' END) AS GOLD,
MAX(CASE WHEN classification ='USA' THEN 'True' ELSE 'False' END) AS USA,
MAX(CASE WHEN classification ='SHORT-TERM' THEN 'True' ELSE 'False' END) AS [SHORT-TERM]
FROM @tbl
GROUP BY fund_id
-- version 3: with aggregate, similar to version 2, bu this time with Min instead of Max
-- effect: there will only be one row per fund_id showing the min value for each column ("Min" would here refer to the "smaller" value of -1 and 0)
SELECT
fund_id,
MIN(CASE WHEN classification ='CASH' THEN -1 ELSE 0 END) AS CASH,
MIN(CASE WHEN classification ='UK' THEN -1 ELSE 0 END) AS UK,
MIN(CASE WHEN classification ='GOLD' THEN -1 ELSE 0 END) AS GOLD,
MIN(CASE WHEN classification ='USA' THEN -1 ELSE 0 END) AS USA,
MIN(CASE WHEN classification ='SHORT-TERM' THEN -1 ELSE 0 END) AS [SHORT-TERM]
FROM @tbl
GROUP BY fund_id
December 7, 2011 at 4:44 pm
first class, thanks, really good stuff. Can't believe you gave me a few alternatives with precision coding also. Fantastic!!!
Cheers,
Gerry.
December 7, 2011 at 4:56 pm
gerardquinn43 (12/7/2011)
first class, thanks, really good stuff. Can't believe you gave me a few alternatives with precision coding also. Fantastic!!!Cheers,
Gerry.
Like I said before: if there are some ready to sample data, you'll get tested code. Usually more than one alternative. (Unless the folks looking at the solution provided can't come up with a better way).
The alternatives I posted were mainly to demonstrate that an aggregation in the CrossTab method has nothing to do with aggregation of the source data but to aggregate on the target column (fund_id in your case, reducing the result set from 6 to 3 rows).
Edit: Hard to explain using words. Much better with a few code snippets 😀
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply