Data from 7 columns to 3 colums with no temp table

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

SQL Server Development (2000)

Data from 7 columns to 3 colums with no temp table

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2009-04-15 : 22:02:00

I have a complicated select from 10 tables. One of the tables has 3 columns. We are using temp tables and a query to get the data from 7 date columns into 3 columns. There are Date1 to Date7 columns which will have dates in any order. Now we have date1, date2 and date3 in the report which are suppose to have the 3 maximum values.

If 2 values are same then they still get different rank and if all dates are same then 3 columns should get poplulated with same date.

CREATE TABLE #temp
(person_id int, date1 datetime,date2 datetime,date3 datetime,date4 datetime,date5 datetime,date6 datetime,date7 datetime)

INSERT INTO #temp
VALUES
(111, '1/1/2009',null,null,null,'1/1/2009','1/1/2009','1/1/2009')
INSERT INTO #temp
VALUES
(222, '1/1/2007',null,null,null,'1/1/2008','1/1/2009','1/1/2009')
INSERT INTO #temp
VALUES
(333, null,'2/2/2007','3/3/3006',null,'1/1/2008','1/1/2008','1/1/2009')
INSERT INTO #temp
VALUES
(444, '2/1/2009',null,null,null,'4/1/2009','3/1/2009','1/1/2009')

So the result we should get is

PERSON_ID, date1, date2, date3
111,'1/1/2009','1/1/2009','1/1/2009'
222,'1/1/2008','1/1/2009','1/1/2009'
333,'1/1/2008','1/1/2008','1/1/2009'
444,'2/1/2009','3/1/2009','4/1/2009'

There is no specific order in which 7 dates are populated and temp tables or table variable are crashing our systems. due to some memory issues. Can this be done wihout temp tables.

Each person should have only 1 row.

Help appreciated. THANKS

-----------------------------------------------------------------------------------------------
Ashley Rhodes

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-15 : 22:37:17

is this u want check it once.
select person_id,max(isnull(date1,date7)),max(isnull(date6,date2)),max(isnull(date5,date3)) from #temp
group by person_id

drop table #temp

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2009-04-16 : 10:25:30

no this will not work
all 3 columns should have different values how will you tell with this query

-----------------------------------------------------------------------------------------------
Ashley Rhodes

dsindo
Starting Member

45 Posts

Posted - 2009-04-16 : 15:50:30

if object_id('tempdb..#temp') is not null
DROP TABLE #temp
CREATE TABLE #temp
(person_id int, date1 datetime,date2 datetime,date3 datetime,date4 datetime,date5 datetime,date6 datetime,date7 datetime)

INSERT INTO #temp
VALUES
(111, '1/1/2009',null,null,null,'1/1/2009','1/1/2009','1/1/2009')
INSERT INTO #temp
VALUES
(222, '1/1/2007',null,null,null,'1/1/2008','1/1/2009','1/1/2009')
INSERT INTO #temp
VALUES
(333, null,'2/2/2007','3/3/2006',null,'1/1/2008','1/1/2008','1/1/2009')
INSERT INTO #temp
VALUES
(444, '2/1/2009',null,null,null,'4/1/2009','3/1/2009','1/1/2009')

if object_id('tempdb..#temp2') is not null
drop table #temp2
create table #temp2 (per_id int,datex datetime)

insert into #temp2
select person_id, date1 from #temp
union all
select person_id, date2 from #temp
union all
select person_id, date3 from #temp
union all
select person_id, date4 from #temp
union all
select person_id, date5 from #temp
union all
select person_id, date6 from #temp
union all
select person_id, date7 from #temp

drop table #temp3
create table #temp3 (id int identity(1,1),per_id int)
insert into #temp3
select distinct person_id from #temp

declare @col int, @cols int,@i int,@y int
select @col=min(id), @cols=max(id) from #temp3

if object_id('tempdb..#temp6') is not null
drop table #temp6
create table #temp6 (x_id int , id int, per_id int, date1 datetime)
while @cols >= @col

begin

drop table #temp5
create table #temp5 (x_id int identity(1,1), id int, per_id int, date1 datetime)
insert into #temp5
select top 3 id, a.per_id,datex from #temp2 a
inner join #temp3 b on a.per_id=b.per_id
where datex is not null and id = @col
order by id,datex desc

insert into #temp6
select * from #temp5

set @col=@col+1
end

select per_id,
max(case when x_id=3 then date1 else null end) as date1,
max(case when x_id=2 then date1 else null end) as date2,
max(case when x_id=1 then date1 else null end) as date3
from #temp6
group by per_id

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2009-04-17 : 14:26:01

Hi

You are using TEMP tables

I am already doing this with TEMP TABLES.
Can not use temp tables due to memory issues

-----------------------------------------------------------------------------------------------
Ashley Rhodes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-17 : 15:31:48


SELECT person_id,
dateval INTO Unpivotted_Table
FROM
(
SELECT person_id,date1 AS dateval
FROM yourtable
WHERE date1 IS NOT NULL
UNION ALL
SELECT person_id,date2
FROM yourtable
WHERE date2 IS NOT NULL
UNION ALL
SELECT person_id,date3
FROM yourtable
WHERE date3 IS NOT NULL
...
SELECT person_id,date7
FROM yourtable
WHERE date7 IS NOT NULL
)t



SELECT u.person_id,
(SELECT TOP 1 dateval FROM
(SELECT TOP 3 dateval FROM Unpivotted_Table  WHERE person_id=u.person_id ORDER BY dateval DESC)p ORDER BY dateval) AS date1,
(SELECT TOP 1 dateval FROM
(SELECT TOP 2 dateval FROM Unpivotted_Table  WHERE person_id=u.person_id ORDER BY dateval DESC)p ORDER BY dateval) AS date2,
(SELECT TOP 1 dateval FROM Unpivotted_Table  WHERE person_id=u.person_id ORDER BY dateval DESC) AS date3
FROM Unpivotted_Table u

DROP TABLE Unpivotted_Table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources