Solved

SQL Server query

Posted on 2013-12-09
14
243 Views
Last Modified: 2013-12-14
I have a SQL Server stored procedure that writes the query results to a .csv file.  My .csv file needs to have as it's first row two columns with data

ROW1 Column1 ThisData   Column2  ThatData
ROW2 and beyond, col1 col2 col3 col4 col5 col6 col7 col8 col9 col10

How can I setup my query to select the first row different than the other rows?  This unique row is a header row that another program will need to process the data in the csv.

Select Row1/Column1data, Row1/Column2data, all other rows/columns
from table
where clause
0
Comment
Question by:newtoperlpgm
  • 6
  • 4
  • 2
  • +2
14 Comments
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 39707572
What is the code you are using to generate the .csv file now?  That way we know what we have to work with as to options and limitations
0
 
LVL 12

Expert Comment

by:Tony303
ID: 39707681
Hi again New,

I had a bit of a brain wave on this.
Can you setup a "Report Order" field.
That way you could say when Row has "This data" the report order is 1, If the Row has "That Data" then the report order is 1 also.
Every other bit of data can have a Report Order of 2 say.

Then Order your export query by "Report Order".

I am hoping that will work with your/ our BCP export option we talked about earlier?

T
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39707784
I'm assuming you just want to add an extra row to the top (rather than displaying the first row of actual data differently).

If so you can create this row however you like and UNION it to the top of the existing query:
SELECT r1.Col1, r1.Col2, '', '', '', '', '', '', '', ''
FROM table r1
UNION
SELECT r2.Col1, r2.Col2, r2.Col3, r2.Col4, r2.Col5, r2.Col6, r2.Col7, r2.Col8, r2.Col9, r2.Col10
FROM table r2
WHERE clause

Open in new window

Note: The 2 SELECTs must have the same number of columns.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 39709124
You could also write out two separate files, then concatenate them into a final result file, using Windows or another OS tool.  The Windows/OS concatenation wouldn't care what format the first two rows were in vs the other rows, it would concatenate them all as data.
0
 

Author Comment

by:newtoperlpgm
ID: 39709527
Angelp1ay,
This almost works, but I need to have my column headers as my second row so how do I accomplish that?

Right now data looks like this:
(No column name)      (No column name)      (No column name)      (No column name)      (No column name)      (No column name)
Header Record      219999                        1/1/1900 0:00
79822      update      219999      79822      INE2014      12/4/2013 0:00
82228      update      219999      82228      INE2014      12/4/2013 0:00
86363      update      219999      86363      INE2014      12/4/2013 0:00

BUT needs to look like this:
Header Record      219999                        
SequenceNumer      RecordType      SponID      EmpID      EventCode      EventDate
79822      update      219999      79822      INE2014      12/4/2013 0:00
82228      update      219999      82228      INE2014      12/4/2013 0:00
86363      update      219999      86363      INE2014      12/4/2013 0:00

Thank you.
0
 

Author Comment

by:newtoperlpgm
ID: 39709554
CGLuttrell, I am using a stored proc to query the table:

select 'Header Record', '2123872','','','',''
from table1
union
select num as [SequenceNumer], 'update' as [RecordType], '219999' as [SponID], EID as [EmpID], 'INE2014' as [EventCode], a.DateObserved as [EventDate]
from table1 a
where
a.IsSubmitted = 1
and a.IsDeleted = 0
AND a.DateObserved > DATEADD(DAY, -7, GETDATE())
group by IED, a.DateObserved

then using bcp to save the .csv file
0
 
LVL 12

Expert Comment

by:Tony303
ID: 39709564
Using my Sort Order idea....

select 1 AS SortOrder,'Header Record', '2123872','','','',''
from table1
union
select 2 as SortOrder,num as [SequenceNumer], 'update' as [RecordType], '219999' as [SponID], EID as [EmpID], 'INE2014' as [EventCode], a.DateObserved as [EventDate]
from table1 a
where 
a.IsSubmitted = 1
and a.IsDeleted = 0
AND a.DateObserved > DATEADD(DAY, -7, GETDATE()) 
group by SortOrder,IED, a.DateObserved
ORder by SortOrder

Open in new window

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:newtoperlpgm
ID: 39710090
Am I doing something wrong, I get an invalid column name 'SortOrder'.
????
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39710151
If you can turn off the column headers in the export then you could just add another row with UNION:
SELECT r1.Col1, r1.Col2, '', '', '', ''
FROM table r1
UNION
SELECT 'SequenceNumber', 'RecordType', 'SponID', 'EmpID', 'EventCode', 'EventDate'
UNION
SELECT r2.Col1, r2.Col2, r2.Col3, r2.Col4, r2.Col5, r2.Col6
FROM table r2
WHERE clause

Open in new window


I don't know how the stored procedure writes to CSV but if you can add an arbitrary row to the top I would manually write the "Header Record      219999" bit and then use a more normal query e.g:
SELECT SequenceNumber = col1, RecordType = col2, SponID = col3, EmpID = col4, EventCode = col5, EventDate = col6
FROM table
WHERE clause

Open in new window

0
 
LVL 12

Expert Comment

by:Tony303
ID: 39710351
Hi New,

Remove the "Sort Order" field from the GROUP BY, sorry my mistake...

Following that....
Here is a completish working example which is semi resembling your data I think...

--CREATE TEMP TABLE

CREATE TABLE #Table1

([SequenceNumer] int NULL,
[RecordType] Varchar(50) NULL,
[SponID] int NULL,
[EmpID] int NULL,
[EventCode]  Varchar(50) NULL,
[EventDate] DateTime NULL)

--POPULATE TEMP TABLE
INSERT INTO #Table1 ([SequenceNumer],[RecordType],[SponID],[EmpID], [EventCode], [EventDate])
VALUES ('2123872','Header Record',NULL,NULL,NULL,NULL), 
(123456, 'Update',219999,987,'INE2014','2013-12-10'),
(123456, 'Update',219999,987,'INE2014','2013-12-09')
 
--SHOW DATA FROM TEMP TABLE
SELECT *
FROM #Table1

--SHOW DATA AS PER QUESTION
--ADD IN THE HEADER WITH SORT ORDER 1
select 1 AS SortOrder, '2123872' AS [SequenceNumer] ,'Header Record' AS [RecordType], NULL AS [SponID],NULL AS  [EmpID],NULL AS [EventCode],NULL AS  [EventDate]
from DimAge

UNION
--ADD IN THE SECOND HEADER WITH SORT ORDER 1
select 1 AS SortOrder, '2123872' ,'Second HeaderLine If Required', NULL,NULL,NULL,NULL
FROM DimAge


union
--GET LINE DATA WITH SORT ORDER 2
select 2 as SortOrder, [SequenceNumer], [RecordType],  [SponID],  [EmpID],  [EventCode],  [EventDate]
from #table1 a
where 
a.[EventDate] > DATEADD(DAY, -7, GETDATE()) 
group by [SequenceNumer], [RecordType],  [SponID],  [EmpID],  [EventCode],  [EventDate]
Order by SortOrder, [RecordType]


DROP TABLE #Table1

Open in new window

0
 

Author Comment

by:newtoperlpgm
ID: 39712950
Unfortunately, it doesn't provide the exact data results that are needed, close, but the second row needs that needs to follow the first unique row are the Column Headings.  Thanks for all your help.
0
 

Author Closing Comment

by:newtoperlpgm
ID: 39712952
The only way that I can get the exact results in the file in the first row is to concatenate two files together.  Thanks for the idea.
0
 
LVL 12

Expert Comment

by:Tony303
ID: 39713022
A couple of effort points would have been nice.... ;-)
0
 

Author Comment

by:newtoperlpgm
ID: 39718818
Tony303 I actually considered that,  I just didn't know if I could do that.  Next time!  Thank you.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now