Prev: CTE and 2 selects from it
Next: PHP Tutorials
From: NetNewbie on 17 Feb 2010 11:59
Hello,

My requirement is to create a pivot report in which a column called
indexrange is to be displayed as n number of intervals eg: 0-9, 10-19 etc.

The source of data for the pivot is a SQL Server query. I am not sure where
or how can I do it, so I am posting this in both groups.

Is it possible to take a value in a column and calculate and then return
multiple columns for multiple ranges?

example

baseCol values 0-9 10-20 21-30 31-40 etc
15 1
20 1
6 1
33 1
9 1
28 1

Thanks for any help and suggestions
From: Plamen Ratchev on 17 Feb 2010 12:07
You can use CASE expressions:

SELECT basecol,
CASE WHEN basecol BETWEEN 0 AND 9 THEN basecol END AS [0-9],
CASE WHEN basecol BETWEEN 10 AND 20 THEN basecol END AS [10-20],
CASE WHEN basecol BETWEEN 21 AND 30 THEN basecol END AS [21-30],
CASE WHEN basecol BETWEEN 31 AND 40 THEN basecol END AS [31-40]
FROM Foo;

--
Plamen Ratchev
http://www.SQLStudio.com
From: NetNewbie on 17 Feb 2010 12:51
Thanks Plamen.

I was just hoping I don't have to do that since I have to create 200
intervals, or that I can find a more flexible method.

Regards,
Hima

"Plamen Ratchev" wrote:

> You can use CASE expressions:
>
> SELECT basecol,
> CASE WHEN basecol BETWEEN 0 AND 9 THEN basecol END AS [0-9],
> CASE WHEN basecol BETWEEN 10 AND 20 THEN basecol END AS [10-20],
> CASE WHEN basecol BETWEEN 21 AND 30 THEN basecol END AS [21-30],
> CASE WHEN basecol BETWEEN 31 AND 40 THEN basecol END AS [31-40]
> FROM Foo;
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
> .
>
From: Plamen Ratchev on 17 Feb 2010 13:14
You can store the intervals in a table and then join it with your data. But since you want each interval as column you
will still end up with some code to define the column expressions (unless you use dynamic pivoting).

--
Plamen Ratchev
http://www.SQLStudio.com
From: NetNewbie on 18 Feb 2010 09:21
I would still need a table with intervals even if I use dynamic pivoting,
correct?

"Plamen Ratchev" wrote:

> You can store the intervals in a table and then join it with your data. But since you want each interval as column you
> will still end up with some code to define the column expressions (unless you use dynamic pivoting).
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: CTE and 2 selects from it
Next: PHP Tutorials