Problem in sub query - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Problem in sub query

Author

Topic

~J~
Starting Member

2 Posts

Posted - 2005-12-15 : 12:59:04

Hi,

I was wondering if someone could help me achieve a view I'm after creating.

I currently have a table which is as follows...

[CODE]
P 44100 SAL ADM 2005 11 1000 200
P 44100 SAL ADM 2005 10 500 90
P 44100 SAL ADM 2005 9 75 34
P 44100 SAL ADM 2004 11 800 1300
P 44100 SAL ADM 2004 10 500 400
P 44100 SAL ADM 2004 9 50 100
[/CODE]

The last 2 columns are of interest to me. What I want to achieve (using the above as an example) are 3 rows which read as follows:

[CODE]
11 1000 200 800 1300
10 500 90 500 400
9 75 34 50 100
[/CODE]

What I'm basically trying to achieve is a view that has the last 2 columns for the current year and all periods, with the last 2 columns of the previous year (and relating period) added to the end.

I really can't get my head around how to do this, but I'm sure it can be done!

Any help I'd really appreciate it.

TIA

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-15 : 13:31:06

Here's one way to do it:


set nocount on
select
	'P' c1,	44100 c2,	'SAL' c3,	'ADM' c4,	2005 c5,	11 c6,	1000 c7,	200 c8
into #temp
insert #temp
select	'P',	44100,	'SAL',	'ADM',	2005,	10,	500,	90 union all
select	'P',	44100,	'SAL',	'ADM',	2005,	9,	75,	34 union all
select	'P',	44100,	'SAL',	'ADM',	2004,	11,	800,	1300 union all
select	'P',	44100,	'SAL',	'ADM',	2004,	10,	500,	400 union all
select	'P',	44100,	'SAL',	'ADM',	2004,	9,	50,	100

select	c.c6
	,c.c7
	,c.c8
	,p.c7
	,p.c8
from	#temp c
left join #temp p
		on p.c6 = c.c6
		and p.c5 + 1 = c.c5
where	p.c6 is not null
order by c.c6 desc

/*
output:

c6          c7          c8          c7          c8          
----------- ----------- ----------- ----------- ----------- 
11          1000        200         800         1300
10          500         90          500         400
9           75          34          50          100
*/

Be One with the Optimizer
TG

X002548
Not Just a Number

15586 Posts

Posted - 2005-12-15 : 14:08:32

[code]

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(
Col1 char(1)
, Col2 int
, Col3 char(3)
, Col4 char(3)
, [year] char(4)
, Col5 int
, Col6 int
, Col7 int)
GO

INSERT INTO myTable99(Col1, Col2, Col3, Col4, [year], Col5, Col6, Col7)
SELECT 'P', 44100, 'SAL', 'ADM', '2005', 11, 1000, 200 UNION ALL
SELECT 'P', 44100, 'SAL', 'ADM', '2005', 10, 500, 90 UNION ALL
SELECT 'P', 44100, 'SAL', 'ADM', '2005', 9, 75, 34 UNION ALL
SELECT 'P', 44100, 'SAL', 'ADM', '2004', 11, 800, 1300 UNION ALL
SELECT 'P', 44100, 'SAL', 'ADM', '2004', 10, 500, 400 UNION ALL
SELECT 'P', 44100, 'SAL', 'ADM', '2004', 9, 50, 100
GO

SELECT Keys.Col5, Col6_2004, Col7_2004, Col6_2005, Col7_2005
FROM (SELECT DISTINCT Col5 FROM myTable99) AS Keys
LEFT JOIN (SELECT Col5, Col6 AS Col6_2004, Col7 AS Col7_2004 FROM myTable99 WHERE [year] = '2004') AS Y2004
ON Keys.Col5 = Y2004.Col5
LEFT JOIN (SELECT Col5, Col6 AS Col6_2005, Col7 AS Col7_2005 FROM myTable99 WHERE [year] = '2005') AS Y2005
ON Keys.Col5 = Y2005.Col5
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

[/code]

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

~J~
Starting Member

2 Posts

Posted - 2005-12-15 : 15:44:07

That's great, many thanks for the help, really appreciate it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources