Prev: CTE and 2 selects from it
Next: PHP Tutorials
From: NetNewbie on 18 Feb 2010 09:35
Hello Plamen,

I tried and I did not get it. In what way should I define the intervals in a
seperate table and join it to my existing query fetching other columns as
well. Would you be able to give me an example, if your time permits?

Thanks,

"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
> .
>
From: Plamen Ratchev on 18 Feb 2010 10:20
Yes, if you do not want to hardcode the interval ranges.

--
Plamen Ratchev
http://www.SQLStudio.com
From: Plamen Ratchev on 18 Feb 2010 10:20
Here is one example:

CREATE TABLE Foo (
keycol INT NOT NULL PRIMARY KEY,
basecol INT);

INSERT INTO Foo VALUES(1, 15);
INSERT INTO Foo VALUES(2, 20);
INSERT INTO Foo VALUES(3, 6);
INSERT INTO Foo VALUES(4, 33);
INSERT INTO Foo VALUES(5, 9);
INSERT INTO Foo VALUES(6, 28);

CREATE TABLE Ranges (
start_value INT NOT NULL PRIMARY KEY,
end_value INT NOT NULL,
CHECK (start_value <= end_value));

INSERT INTO Ranges VALUES(0, 9);
INSERT INTO Ranges VALUES(10, 20);
INSERT INTO Ranges VALUES(21, 30);
INSERT INTO Ranges VALUES(31, 40);

SELECT basecol, [1], [2], [3], [4]
FROM (
SELECT F.basecol, 1 AS dummy,
DENSE_RANK() OVER(ORDER BY R.start_value) AS bin
FROM Ranges AS R
LEFT JOIN Foo AS F
ON F.basecol BETWEEN R.start_value AND R.end_value) AS T
PIVOT
(MAX(dummy) FOR bin IN ([1], [2], [3], [4])) AS P;

/*

basecol 1 2 3 4
----------- ----------- ----------- ----------- -----------
6 1 NULL NULL NULL
9 1 NULL NULL NULL
15 NULL 1 NULL NULL
20 NULL 1 NULL NULL
28 NULL NULL 1 NULL
33 NULL NULL NULL 1

*/

--
Plamen Ratchev
http://www.SQLStudio.com
First  |  Prev  | 
Pages: 1 2
Prev: CTE and 2 selects from it
Next: PHP Tutorials