SQL Server query

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
newtoperlpgmAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
Tony303Commented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Angelp1ayCommented:
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
 
newtoperlpgmAuthor Commented:
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
 
newtoperlpgmAuthor Commented:
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
 
Tony303Commented:
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
 
newtoperlpgmAuthor Commented:
Am I doing something wrong, I get an invalid column name 'SortOrder'.
????
0
 
Angelp1ayCommented:
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
 
Tony303Commented:
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
 
newtoperlpgmAuthor Commented:
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
 
newtoperlpgmAuthor Commented:
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
 
Tony303Commented:
A couple of effort points would have been nice.... ;-)
0
 
newtoperlpgmAuthor Commented:
Tony303 I actually considered that,  I just didn't know if I could do that.  Next time!  Thank you.
0
All Courses

From novice to tech pro — start learning today.