Select statement with two - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select statement with two

Author

Topic

mic_lch83
Starting Member

5 Posts

Posted - 2007-02-01 : 10:44:53

I need to make a "select" statement that will create two columns with different requirements.

My Database is like this.
PopTotal Variance
2 1
3 1
4 1
5 2
6 2

I need to create a table from an sql statement that will show.
PopTotal1 PopTotal2
2 5
3 6
4
Meaning two columns from the same column with different requirements. How can I do that with sql statements without CHANGING the database?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-01 : 11:08:49

Is your objective to cut the PopTotal in half, and create two columns with the divied PopTotal in both, starting with lowest number?

-- prepare sample data
declare @sample table (PopTotal int, Variance int)

insert	@sample
select	2, 1 union all
select	6, 1 union all
select	9, 1 union all
select	25, 2 union all
select	3433, 2

-- show the result
SELECT	    c1.PopTotal,
	    c2.PopTotal
FROM	    (
		SELECT	    c1.PopTotal,
			    ROW_NUMBER() OVER (ORDER BY c1.PopTotal) AS Row
		FROM	    (
				SELECT	PopTotal,
					NTILE(2) OVER (ORDER BY PopTotal) AS Col
				FROM	@Sample
			    ) as c1
    		WHERE	    c1.Col = 1
	    ) AS c1
LEFT JOIN   (
		SELECT	    c2.PopTotal,
			    ROW_NUMBER() OVER (ORDER BY c2.PopTotal) AS Row
		FROM	    (
				SELECT	PopTotal,
					NTILE(2) OVER (ORDER BY PopTotal) AS Col
				FROM	@Sample
			    ) as c2
		WHERE	    c2.Col = 2
	    ) AS c2 ON c2.Row = c1.Row
ORDER BY    c1.Row

Peter Larsson
Helsingborg, Sweden

mic_lch83
Starting Member

5 Posts

Posted - 2007-02-01 : 11:13:55

Thank you for your quick response.
My objective is to cut PopTable into whatever the VarID is. But, It do not need to be sorted.

Thanks.

mic_lch83
Starting Member

5 Posts

Posted - 2007-02-01 : 11:13:57

Thank you for your quick response.
My objective is to cut PopTable into whatever the VarID is. But, It do not need to be sorted.

Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-01 : 11:17:50

So Value that has a variance goes into Col1, and variance goes into Col2?

What is the rlationship between the values in Column 1 and Column 2?

It makes no sense

I could "make something up" to produce these results. Before I do, you really need to explain to us why? Because I can tell you right now, the result set is meaningless

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-01 : 11:24:50

Or it is as simple as Variance 1 is the first column, Variance 2 is the second column?

-- prepare sample data
declare @sample table (PopTotal int, Variance int)

insert	@sample
select	2, 1 union all
select	3, 1 union all
select	4, 1 union all
select	5, 2 union all
select	6, 2

-- Show the result
SELECT	    MAX(CASE WHEN Variance = 1 THEN PopTotal END) AS Col1,
	    MAX(CASE WHEN Variance = 2 THEN PopTotal END) AS Col2
FROM	    (
    	        SELECT	PopTotal,
			Variance,
			ROW_NUMBER() OVER (PARTITION BY Variance ORDER BY PopTotal) AS RowID
		FROM	@Sample
	    ) AS s
GROUP BY    RowID
ORDER BY    RowID

Peter Larsson
Helsingborg, Sweden

mic_lch83
Starting Member

5 Posts

Posted - 2007-02-01 : 11:27:25

No, the value with different variance goes into different columns. Let my show you my table 'tblTotPopulation'.

LocID VarID PopTotal
1 2 1.2
1 3 3.4
2 2 5.6
2 3 3.2
3 2 3.5
3 3 3.9

I need to create a table where it shows 2 columns of Poptotal with Different VarID. The results should Be
LocID PopTable1 PopTable2
1 1.2 3.4
2 5.6 3.2
3 3.5 3.9

As you can see that the PopTables have different VarID.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-01 : 11:28:03

mic_lch83
Starting Member

5 Posts

Posted - 2007-02-01 : 11:35:33

Peso,
You really seems to be a great help. However, I am currently using SQL 2000 and some functions like ROW_NUMBER() does not exists.
Do you know how to avoid the functions in SQL 200? Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-01 : 13:45:45

[code]-- prepare sample data
declare @sample table (LocID int, VarID int, PopTotal money)

insert @sample
select 1, 2, 1.2 union all
select 1, 3, 3.4 union all
select 2, 2, 5.6 union all
select 2, 3, 3.2 union all
select 3, 2, 3.5 union all
select 3, 3, 3.9

-- Show the result SQL 2005 style
SELECT MAX(CASE WHEN s.Col = 1 THEN s.PopTotal END) AS Col1,
MAX(CASE WHEN s.Col = 2 THEN s.PopTotal END) AS Col2,
MAX(CASE WHEN s.Col = 3 THEN s.PopTotal END) AS Col3
FROM (
SELECT PopTotal,
DENSE_RANK() OVER (ORDER BY VarID) AS Col,
LocID AS Row
FROM @Sample
) AS s
GROUP BY s.Row
ORDER BY s.Row

-- Show the result SQL 2000 style
SELECT MAX(CASE WHEN s.Col = 1 THEN s.PopTotal END) AS Col1,
MAX(CASE WHEN s.Col = 2 THEN s.PopTotal END) AS Col2,
MAX(CASE WHEN s.Col = 3 THEN s.PopTotal END) AS Col3
FROM (
SELECT s.PopTotal,
(SELECT COUNT(DISTINCT sc.VarID) FROM @Sample AS sc WHERE sc.VarID <= s.VarID) AS Col,
s.LocID AS Row
FROM @Sample AS s
) AS s
GROUP BY s.Row
ORDER BY s.Row[/code]
Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-01 : 14:15:11

is VarID always 2 or 3? If not, what happens when it has other values ?

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-01 : 14:22:17

VarID of 2 and 3 are fixed?

-- Show the result SQL 2000 style
SELECT		MAX(CASE WHEN VarID = 2 THEN PopTotal END) AS Col,
		MAX(CASE WHEN VarID = 3 THEN PopTotal END) AS Co2
FROM		@Sample
GROUP BY	LocID
ORDER BY	LocID

If they are not fixed, my previous suggestion will translate the VarID's into 1-2-3 with the same order as original.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources