Multiple rows from single row; building query help

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

Multiple rows from single row; building query help

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-07-22 : 13:28:04

I have this table:

SAMPLE_DATA(Stores 1 sample for 24 hours) - [SAMPLE_NUM](PK), [SAMPLE_TYPE](FK),[SAMPLE_START_DATE],[SAMPLE_START_TIME],
[SAMPLE_END_DATE],[SAMPLE_END_TIME]

From this table, I need to get 24 rows – 1 for each hour and then Insert them in a new table with identical structure. Start time / End times for the rows should be like "0000/0059", "0100/0159" and so on (last one is "2300/2359").

Sample row(Existing): (1234, '1', '22/07/09', '0000', '22/07/09', '2359')
Expected output rows:
(1, '1', '22/07/09', '0000', '22/07/09', '0059')
(2, '1', '22/07/09', '0100', '22/07/09', '0159')
..
..
(24, '1', '22/07/09', '2300', '22/07/09', '2359')

Any idea? Thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-22 : 13:36:03

using while?

No, you're never too old to Yak'n'Roll if you're too young to die.

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-07-22 : 14:32:03

how?

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-07-27 : 12:24:19

Anybody else up there to help me on this?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-28 : 03:01:45

declare @t table([SAMPLE_NO] int,[SAMPLE_TYPE] char(1),[SAMPLE_START_DATE] datetime,[SAMPLE_START_TIME] char(4),
[SAMPLE_END_DATE] datetime,[SAMPLE_END_TIME] char(4))
insert into @t
select 1, '1', '2009-07-22', '0000', '2009-07-22', '2359'

select [SAMPLE_NO]+number,[SAMPLE_TYPE],[SAMPLE_START_DATE],
[SAMPLE_START_TIME]+number*100,[SAMPLE_END_DATE],'0059'+number*100 from
@t,master..spt_values where type='p' and number between 0 and 23

Madhivanan

Failing to plan is Planning to fail

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-28 : 03:21:16

by using while loop try this


declare @t table([SAMPLE_NO] int,[SAMPLE_TYPE] char(1),[SAMPLE_START_DATE] datetime,[SAMPLE_START_TIME] char(4),
[SAMPLE_END_DATE] datetime,[SAMPLE_END_TIME] char(4))
insert into @t select 1, '1', '2009-07-22', '0000', '2009-07-22', '2359'

declare @t1 table([SAMPLE_NO] int,[SAMPLE_TYPE] char(1),[SAMPLE_START_DATE] datetime,[SAMPLE_START_TIME] char(4),
[SAMPLE_END_DATE] datetime,[SAMPLE_END_TIME] char(4))

DECLARE @s INT,@val INT

SELECT @s  = 0

WHILE (@s <=24)
BEGIN 

SELECT @val = @s * 100

INSERT INTO @t1
select [SAMPLE_NO]+@s,[SAMPLE_TYPE],[SAMPLE_START_DATE],
CAST(@val AS VARCHAR(4)),[SAMPLE_END_DATE],CAST(59+@val AS VARCHAR(4))  from @t

SELECT @s = @s + 1

END

select * from @t1

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-28 : 03:26:32

or use these CSVTable or fnParseList

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033#315323
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-07-30 : 12:22:33

Thanks a lot to Madhivanan and bklr. I got busier with another project, so couldn't test on these. But I will do so and get back to you as soon as possible.

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-30 : 12:44:10

It's your dime

When you get off hold....maybe yyou can explain why...and if all the data acually exists, or if you are creating "placeholders"

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

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-08-05 : 15:50:29

Hello Madhivanan and bklr, following your inputs I tried this:
<code>
DECLARE @s INT,@val INT
SELECT @s = 0
WHILE (@s <= 23)
BEGIN
SET @val = @s * 100
INSERT INTO SAMPLE_DATA_TEMP (TSAMPLE_TYPE,TSAMPLE_SITE,TSAMPLE_PLOT,
TSAMPLE_INTERVAL,TSAMPLE_START_DATE,TSAMPLE_START_TIME,TSAMPLE_END_DATE,
TSAMPLE_END_TIME,TSAMPLE_SUBMIT_DATE)
select [SAMPLE_TYPE],[SAMPLE_SITE],[SAMPLE_PLOT],[SAMPLE_INTERVAL],
[SAMPLE_START_DATE],CAST(@val AS CHAR(4)),[SAMPLE_END_DATE],
CAST(59+@val AS CHAR(4)),[SAMPLE_SUBMIT_DATE] from SAMPLE_DATA_ORA
WHERE SAMPLE_END_DATE <= '31-Dec-1986'
SET @s = @s + 1
END
</code>
It works, but there is a problem - it gives me rows for each date and each start/end time in sequence - instead of giving me rows for 24 start/end times for a day before going to the next date. I tried running a nested query that uses a sdate variable in the outer loop - but not successful so far.
So, I'll highly appreciate if anybody can help,

Thank.

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-08-05 : 17:03:36

I got it done, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources