How do you create a view to select data from table & force static data into alternating rows

Question

How do you create a view to select data from table & force static data into alternating rows

dan.manning

SSC Enthusiast

Points: 175
More actions
November 30, 2010 at 2:21 am

#229192

Hi Experts!!
I need to create a view to select data from table & force static data into alternating rows.
Example
1 0 JobNo Ref Contract RDate RTime LDate LTime SDesc Access DescDesc CJob <- Data
2 1 JobNo '24HR' '1' 'MU' <- Static
3 0 JobNo Ref Contract RDate RTime LDate LTime SDesc Access DescDesc CJob
4 1 JobNo '24HR' '1' 'MU'
5 0 JobNo Ref Contract RDate RTime LDate LTime SDesc Access DescDesc CJob
6 1 JobNo '24HR' '1' 'MU'
7 0 JobNo Ref Contract RDate RTime LDate LTime SDesc Access DescDesc CJob
8 1 JobNo '24HR' '1' 'MU'
What I am trying to do is create the view so i can use it in SSIS to extract the data from the view and create a csv.
If anyone has an answer or another solution then i'd be very grateful for your help.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Dan

Copy the following code and paste it into an SSMS window. Correct the DDL to match your table then amend the INSERT script to provide say 10 rows of data which are representative of your actual data. Then post it here. This will provide folks with a small data set which they can use to model your problem. There's a link to a forum etiquette paper in my sig.

CREATE TABLE #Table (

JobNo CHAR(5),

Ref VARCHAR(4),

[Name] VARCHAR(20),

Add1 VARCHAR(20),

Add2 VARCHAR(20),

Add3 VARCHAR(20),

Add4 VARCHAR(20),

Add5 VARCHAR(20),

Postcode VARCHAR(10))

INSERT INTO #Table (JobNo, Ref, [Name], Add1, Add2, Add3, Add4, Add5, Postcode)

SELECT 'JobNo', 'Ref', 'Name', 'Add1', 'Add2', 'Add3', 'Add4', 'Add5', 'Postcode' UNION ALL

SELECT 'JobNo', 'Ref', 'Name', 'Add1', 'Add2', 'Add3', 'Add4', 'Add5', 'Postcode'

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dan.manning SSC Enthusiast Points: 175 More actions · Answer 2

dan.manning

SSC Enthusiast

Points: 175

November 30, 2010 at 2:55 am

#1256658

Data below

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Dan, if you set up the sample data like I suggested, it will be instantly usable. Not many folks will be inclined to do the formatting for you.

SELECT 'JobNo', 'Ref', 'Name', 'Add1', 'Add2', 'Add3', 'Add4', 'Add5', 'Postcode' UNION ALL

SELECT 'JobNo', 'Ref', 'Name', 'Add1', 'Add2', 'Add3', 'Add4', 'Add5', 'Postcode'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dan.manning SSC Enthusiast Points: 175 More actions · Answer 4

Sorry for the confusion!! I have amended what the output is that is needed.

Hi Experts!!

I need to create a view to select data from table & force static data into alternating rows.

0 JobNo Ref Contract RDate RTime LDate LTime SDesc Access DescDesc CJob <- Data

1 JobNo '24HR' '1' 'MU' <- Static

0 JobNo Ref Contract RDate RTime LDate LTime SDesc Access DescDesc CJob

1 JobNo '24HR' '1' 'MU'

0 JobNo Ref Contract RDate RTime LDate LTime SDesc Access DescDesc CJob

1 JobNo '24HR' '1' 'MU'

0 JobNo Ref Contract RDate RTime LDate LTime SDesc Access DescDesc CJob

1 JobNo '24HR' '1' 'MU'

Example Data from table

0 C0123456ABC123456OUTHOURS29/11/201010:57:1529/11/201010:57:15"TEST""TEST""TEST"C0123456

0 C01234567ABC1234567OUTHOURS28/11/201009:22:1228/11/201009:22:12"TEST""TEST""TEST"C01234567

0 C012345678ABC12345678OUTHOURS27/11/201009:22:1227/11/201009:22:12"TEST""TEST""TEST"C012345678

0 C0123456789ABC123456789OUTHOURS26/11/201009:22:1226/11/201009:22:12"TEST""TEST""TEST"C0123456789

0 C01234567890ABC1234567890OUTHOURS25/11/201009:22:1225/11/201009:22:12"TEST""TEST""TEST"C01234567890

Data that needs to be inserted every other row with the job no of the row above.

1 JobNo EA 1 GEN

Output should look like

0 C0123456 ABC123456 OUTHOURS 29/11/2010 10:57:1529/11/201010:57:15"TEST""TEST""TEST"C0123456

1 C0123456 EA 1 GEN

0 C01234567 ABC1234567 OUTHOURS 28/11/2010 09:22:1228/11/201009:22:12"TEST""TEST""TEST"C01234567

1 C01234567 EA 1 GEN

0 C012345678 ABC12345678 OUTHOURS 27/11/2010 09:22:1227/11/201009:22:12"TEST""TEST""TEST"C012345678

1 C012345678 EA 1 GEN

0 C0123456789 ABC123456789 OUTHOURS 26/11/2010 09:22:1226/11/201009:22:12"TEST""TEST""TEST"C0123456789

1 C0123456789 EA 1 GEN

0 C01234567890 ABC1234567890 OUTHOURS 25/11/2010 09:22:1225/11/201009:22:12"TEST""TEST""TEST"C01234567890

1 C01234567890 EA 1 GEN

I hope this better explans the problem.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Try this, untested of course - no sample data to test against!

DROP TABLE #Table

CREATE TABLE #Table (

JobNo CHAR(5),

Ref VARCHAR(4),

[Name] VARCHAR(20),

Add1 VARCHAR(20),

Add2 VARCHAR(20),

Add3 VARCHAR(20),

Add4 VARCHAR(20),

Add5 VARCHAR(20),

Postcode VARCHAR(10))

INSERT INTO #Table (JobNo, Ref, [Name], Add1, Add2, Add3, Add4, Add5, Postcode)

SELECT '1', 'Ref', 'Name', 'Add1', 'Add2', 'Add3', 'Add4', 'Add5', 'Postcode' UNION ALL

SELECT '2', 'Ref', 'Name', 'Add1', 'Add2', 'Add3', 'Add4', 'Add5', 'Postcode'

SELECT *

FROM (

SELECT JobNo, 0 AS Seq, Ref, [Name], Add1, Add2, Add3, Add4, Add5, Postcode

FROM #Table

UNION ALL

SELECT JobNo, 1 AS Seq, '', 'Static', '', '', '', '', '', ''

FROM #Table

) d

ORDER BY JobNo, Seq

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

irobertson Hall of Fame Points: 3695 More actions · Answer 6

Hi Dan,

Chris is giving you good advice - sample data and DDL is the best way to get the best answer. This means providing a create table statement, then an insert sttement that we can just run to populate a work table to play with.

Some air code that might start the ball rolling:

; with cte as (

select <your table data>

, row_number () over (order by <some field>) as Row_Key

from <your table>

)

, cte2 as (

select <your table data>

, Row_Key + Row_Key - 1 as Ordering_Key -- converts row_number to odd number sequence

from cte

union all

select <your static data>

, N * 2 as Ordering_Key -- converts row_number to even number sequence

from <your static data>

join dbo.Tally -- uses a tally table

where N <= (select count(*) from <your data table>)

)

select * from cte2

order by Ordering_Key

Happy to provide an example using your data if you provide the DDL...

Edit: or, you could just use the far simpler solution that Chris has posted 🙂

Cheers, Iain

dan.manning SSC Enthusiast Points: 175 More actions · Answer 7

CREATE TABLE #Table (

Line INT,

JobNo VARCHAR(20),

PropRef VARCHAR(25),

Cont VARCHAR(8),

RepDate VARCHAR(10),

RepTime VARCHAR(8),

LogDate VARCHAR(10),

LogTime VARCHAR(8),

ShortDesc VARCHAR(60),

Access VARCHAR(60),

JobDesc VARCHAR(200),

ClientJob VARCHAR(20)

)

INSERT INTO #Table (Line, JobNo, PropRef, Cont, RepDate, RepTime, LogDate, LogTime, ShortDesc, Access, JobDesc, ClientJob)

SELECT '0', 'C0123456', 'ABC123456', 'OUTHOURS', '29/11/2010', '10:57:15', '29/11/2010', '10:57:15', 'TEST', 'TEST', 'TEST', 'C0123456' UNION ALL

SELECT '0', 'C01234567', 'ABC1234567', 'OUTHOURS', '28/11/2010', '09:22:12', '28/11/2010', '09:22:12', 'TEST', 'TEST', 'TEST', 'C01234567' UNION ALL

SELECT '0', 'C012345678', 'ABC12345678', 'OUTHOURS', '27/11/2010', '09:22:12', '27/11/2010', '09:22:12', 'TEST', 'TEST', 'TEST', 'C012345678' UNION ALL

SELECT '0', 'C0123456789', 'ABC123456789', 'OUTHOURS', '26/11/2010', '09:22:12', '26/11/2010', '09:22:12', 'TEST', 'TEST', 'TEST', 'C0123456789' UNION ALL

SELECT '0', 'C01234567890', 'ABC1234567890', 'OUTHOURS', '25/11/2010', '09:22:12', '25/11/2010', '09:22:12', 'TEST', 'TEST', 'TEST', 'C01234567890'

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Thanks Dan.

SELECT rn = ROW_NUMBER() OVER(ORDER BY JobNo, Line),

Line, JobNo, PropRef, Cont, RepDate, RepTime, LogDate, LogTime, ShortDesc, Access, JobDesc, ClientJob

FROM (

SELECT Line, JobNo, PropRef, Cont, RepDate, RepTime, LogDate, LogTime, ShortDesc, Access, JobDesc, ClientJob

FROM #Table

UNION ALL

SELECT 1 AS Line,

JobNo,

PropRef = '24HR',

Cont = '1',

RepDate = NULL, -- choose a column to accept 'MU'

RepTime = '', -- set "empty" columns to '' or NULL here or in outer select

LogDate = NULL,

LogTime = NULL,

ShortDesc = NULL,

Access = NULL,

JobDesc = NULL,

ClientJob = NULL

FROM #Table

) d

ORDER BY JobNo, Line

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dan.manning SSC Enthusiast Points: 175 More actions · Answer 9

Chris Morris-439714 (11/30/2010)
Thanks Dan.
SELECT rn = ROW_NUMBER() OVER(ORDER BY JobNo, Line),
Line, JobNo, PropRef, Cont, RepDate, RepTime, LogDate, LogTime, ShortDesc, Access, JobDesc, ClientJob
FROM (
SELECT Line, JobNo, PropRef, Cont, RepDate, RepTime, LogDate, LogTime, ShortDesc, Access, JobDesc, ClientJob
FROM #Table
UNION ALL
SELECT 1 AS Line,
JobNo,
PropRef = '24HR',
Cont = '1',
RepDate = NULL, -- choose a column to accept 'MU'
RepTime = '', -- set "empty" columns to '' or NULL here or in outer select
LogDate = NULL,
LogTime = NULL,
ShortDesc = NULL,
Access = NULL,
JobDesc = NULL,
ClientJob = NULL
FROM #Table
) d
ORDER BY JobNo, Line

Awesome Chris!! Works like a charm!! 😀

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

You're welcome Dan, thanks for the generous feedback.

Don't forget in future to always include sample data.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden