Solved

SQL Server query

Posted on 2013-12-09
14
246 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 69

Accepted Solution

by:
Scott Pletcher 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
 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

825 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