Insert and reformat data - table a to table b

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 2005 Forums

Transact-SQL (2005)

Insert and reformat data - table a to table b

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2008-06-05 : 04:35:00

Hello,
I have a table called #table1 which is populated as in the example below.
I would like to write a select\insert statement based on #table1 that populates #table2 like in the #table2 example.
Note #table2 is a fixed table that follows the structure below.

Can any of you T-SQL gurus help me with my problem?
Any help will be most appreciated.
Thanks

---------------------------------------------
/*
Please paste T-SQL into query window
*/
---------------------------------------------
CREATE TABLE #table1
--max of 5 orders
(
custID nvarchar(6),
dateorder [datetime] NULL,
order1 nvarchar(2),
order2 nvarchar(2),
order3 nvarchar(2),
order4 nvarchar(2),
order5 nvarchar(2)
)

GO
SET ANSI_PADDING OFF

Insert into #table1
select '012345','2008-04-19 00:00:00.000' , '01', '06', '05', null, null UNION all
select '012345','2008-04-20 00:00:00.000' , '01', '07', '05', '07', '03' UNION all
select '012345','2008-04-21 00:00:00.000' , '01', '06', null, null, null UNION all
select '012345','2008-04-22 00:00:00.000' , '01', '02', '05', '07', null UNION all
select '012345','2008-04-23 00:00:00.000' , '03', '06', null, null, null UNION all
select '987654','2008-04-21 00:00:00.000' , '19', '21', null, null, null UNION all
select '987654','2008-04-22 00:00:00.000' , '01', '02', '05', '16', null UNION all
select '987654','2008-04-23 00:00:00.000' , '03', '06', null, null, null

select * from #table1

--This is the table i would like to insert my data into
CREATE TABLE #table2
--max of 5 orders in 1 day
--it does not matter what date the date order was made the 1st date would appear in dateorder1 and so on...
(
custID nvarchar(6),
dateorder1 [datetime] NULL,
order1_1 nvarchar(2),
order1_2 nvarchar(2),
order1_3 nvarchar(2),
order1_4 nvarchar(2),
order1_5 nvarchar(2),
dateorder2 [datetime] NULL,
order2_1 nvarchar(2),
order2_2 nvarchar(2),
order2_3 nvarchar(2),
order2_4 nvarchar(2),
order2_5 nvarchar(2),
dateorder3 [datetime] NULL,
order3_1 nvarchar(2),
order3_2 nvarchar(2),
order3_3 nvarchar(2),
order3_4 nvarchar(2),
order3_5 nvarchar(2),
dateorder4 [datetime] NULL,
order4_1 nvarchar(2),
order4_2 nvarchar(2),
order4_3 nvarchar(2),
order4_4 nvarchar(2),
order4_5 nvarchar(2),
dateorder5 [datetime] NULL,
order5_1 nvarchar(2),
order5_2 nvarchar(2),
order5_3 nvarchar(2),
order5_4 nvarchar(2),
order5_5 nvarchar(2)
)

Insert into #table2
select '012345','2008-04-19 00:00:00.000' , '01', '06', '05', null, null, '2008-04-20 00:00:00.000' , '01', '07', '05', '07', '03','2008-04-21 00:00:00.000' , '01', '06', null, null, null,'2008-04-22 00:00:00.000' , '01', '02', '05', '07', null,'2008-04-23 00:00:00.000' , '03', '06', null, null, null UNION all
select '987654','2008-04-21 00:00:00.000' , '19', '21', null, null, null ,'2008-04-22 00:00:00.000' , '01', '02', '05', '16', null,'2008-04-23 00:00:00.000' , '03', '06', null, null, null , null, null, null, null, null, null, null, null, null, null, null, null

select * from #table2

drop table #table1
drop table #table2

----------------------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-05 : 04:54:53

Insert into #table2

SELECT CustID,
MAX(CASE WHEN RowNo=1 THEN dateorder ELSE NULL END) AS dateorder1,
MAX(CASE WHEN RowNo=1 THEN order1 ELSE NULL END) AS order1_1,
MAX(CASE WHEN RowNo=1 THEN order2 ELSE NULL END) AS order1_2,
MAX(CASE WHEN RowNo=1 THEN order3 ELSE NULL END) AS order1_3,
MAX(CASE WHEN RowNo=1 THEN order4 ELSE NULL END) AS order1_4,
MAX(CASE WHEN RowNo=1 THEN order5 ELSE NULL END) AS order1_5,
MAX(CASE WHEN RowNo=2 THEN dateorder ELSE NULL END) AS dateorder2,
MAX(CASE WHEN RowNo=2 THEN order1 ELSE NULL END) AS order2_1,
MAX(CASE WHEN RowNo=2 THEN order2 ELSE NULL END) AS order2_2,
MAX(CASE WHEN RowNo=2 THEN order3 ELSE NULL END) AS order2_3,
MAX(CASE WHEN RowNo=2 THEN order4 ELSE NULL END) AS order2_4,
MAX(CASE WHEN RowNo=2 THEN order5 ELSE NULL END) AS order2_5,
.....
MAX(CASE WHEN RowNo=5 THEN dateorder ELSE NULL END) AS dateorder5,
MAX(CASE WHEN RowNo=5 THEN order1 ELSE NULL END) AS order5_1,
MAX(CASE WHEN RowNo=5 THEN order2 ELSE NULL END) AS order5_2,
MAX(CASE WHEN RowNo=5 THEN order3 ELSE NULL END) AS order5_3,
MAX(CASE WHEN RowNo=5 THEN order4 ELSE NULL END) AS order5_4,
MAX(CASE WHEN RowNo=5 THEN order5 ELSE NULL END) AS order5_5

FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY CustID order by dateorder) as RowNo,*
FROM #table1)tmp
GROUP BY CustID

DLTaylor
Posting Yak Master

136 Posts

Posted - 2008-06-05 : 05:25:51

Thanks Visakh16, - works great!
Ok, now to admit to a school boy error - i didnt realise we would use 'ROW_NUMBER' function which i know is in T-SQL 2005 ... and not on T-SQL 2000? is there a way to make it work in T-SQL 2000.
I will take the shame as i know i posted in the T-SQL 2005 forum :( sorry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources