SolvedPrivate

SQL Pipe Delimited File

Posted on 2014-04-30
3
51 Views
Last Modified: 2016-02-10
I need to get the following results into a Pipe Delimited file format.  I the 'HEADER' columns to be the start of a new row followed by each column pipe delimited even if the column is blank.  

Here are the results:

Authorization	AUN	APN	CAN	AUB	MACHINE_HEADER	MSN	MODEL_HEADER	ESN	ENM	DETAIL_HEADER	FAD	RED	EHU	CAO	FAILURE_HEADER	PPF	FC1	FC2	JOC	MIN	HOU	CSN	RPB	NOTES_HEADER	PRC	WOP	PARTS_HEADER	PAN	QTY	TON	WARRANTY_END_HEADER
<AUTHORIZATION>	Authorization Number	||	||	||	<MACHINE>	5912	<MODEL>	||	||	<DETAILS>	2014-04-04 10:07:24.000	2014-04-09 07:30:00.000	NULL	||	<FAILURE>	Primary Part of Failure	Failure Code	||	Job Code	Labor Minutes	Labor Hours	||	||	<NOTES>	Service Bunker rake....	Work Performed	<PARTS>	Part Number	Quantity	||	<WARRANTY END>
<AUTHORIZATION>	Authorization Number	||	||	||	<MACHINE>	5912	<MODEL>	||	||	<DETAILS>	2014-04-04 10:07:24.000	2014-04-09 13:00:00.000	1099	||	<FAILURE>	Primary Part of Failure	Failure Code	||	Job Code	Labor Minutes	Labor Hours	||	||	<NOTES>	Service Bunker rake....	Work Performed	<PARTS>	Part Number	Quantity	||	<WARRANTY END>
<AUTHORIZATION>	Authorization Number	||	||	||	<MACHINE>	5912	<MODEL>	||	||	<DETAILS>	2014-04-04 10:07:24.000	2014-04-24 09:15:00.000	1123	||	<FAILURE>	Primary Part of Failure	Failure Code	||	Job Code	Labor Minutes	Labor Hours	||	||	<NOTES>	Service Bunker rake....	Work Performed	<PARTS>	Part Number	Quantity	||	<WARRANTY END>

Open in new window


Here is the query:
DECLARE @casenumber VARCHAR(5)
SET @casenumber='14059'
SELECT  [AUTHORIZATION_HEADER]='<AUTHORIZATION>',
		[AUN]='Authorization Number',
		[APN]='||',
		[CAN]='||',
		[AUB]='||',
		[MACHINE_HEADER]='<MACHINE>',
		[MSN]=b.tsp_serialnumber,
		[MODEL_HEADER]='<MODEL>',
		[ESN]='||',
		[ENM]='||',
		[DETAIL_HEADER]='<DETAILS>',
		[FAD]=a.createdon,
		[RED]=c.scheduledstart,
		[EHU]=c.tsp_hours,
		[CAO]='||',
		[FAILURE_HEADER]='<FAILURE>',
		[PPF]='Primary Part of Failure',
		[FC1]='Failure Code',
		[FC2]='||',
		[JOC]='Job Code',
		[MIN]='Labor Minutes',
		[HOU]='Labor Hours',
		[CSN]='||',
		[RPB]='||',
		[NOTES_HEADER]='<NOTES>',
		[PRC]=a.description,
		[WOP]='Work Performed',
		[PARTS_HEADER]='<PARTS>',
		[PAN]='Part Number',
		[QTY]='Quantity',
		[TON]='||',
		[WARRANTY_END_HEADER]='<WARRANTY END>'
		
		
        
FROM    FilteredIncident AS a
        LEFT OUTER JOIN FilteredTSP_WorkOrder AS b ON a.incidentid = b.tsp_casenumberid
        LEFT OUTER JOIN FilteredServiceAppointment AS c ON b.tsp_workorderid = c.regardingobjectid
WHERE   ( a.ticketnumber LIKE '%' + @casenumber + '%' )
ORDER BY c.scheduledstart		

Open in new window


Here is an example format:
<AUTHORIZATION>|DRN001|FAN001|xxxxx|Martyn
<MACHINE>|EA000500
<MODEL>|ESN001|ENM001
<DETAILS>|11-NOV-2007|12-NOV-2007|5|Lorry
<FAILURE>|MBX7024A|Electrical|X1|X2|2|15|CSN001|Owner
<NOTES>|It Broke|I fixed it
<PARTS>|MBA7024A|2|
<PARTS>|MCS12345|1|
<MISCELLANEOUS>|OIL|12.34
<WARRANTY END>

Open in new window

0
Comment
Question by:r270ba
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 8

Expert Comment

by:ProjectChampion
ID: 40033491
I would suggest you use SSIS or (if it's a one off task) SQL Server Import/Export utility and export the data into a flat file.  At the point of defining the Intended file, you can choose the delimiter (e.g. "|" instead of comma).
0
 

Author Comment

by:r270ba
ID: 40034518
ProjectChampion thanks.  Any direction on how to do that?
0
 
LVL 8

Accepted Solution

by:
ProjectChampion earned 500 total points
ID: 40034617
If it's not a one off task the I strongly suggest you use SSIS, otherwise in Management Studio right click on the database, select Tasks > Export Data...
In the dialog box that opens select next, in "Data Source" tab enter name of the server and DB, then go to the destination tab, select "Flat File Destination", chose a file name and location, choose the appropriate code page, and set "Format" to Delimited and if needed a text qualifier (I recommend double quote ") and move to the next page, select a table or query as appropriate and move to the next tab (if you selected query you need to write the actual query in the next tab then...) on the "Configure Flat File Destination" tab you have the option for Row/line delimiter and column delimiter where you can select one of the option or add  a custom delimiter of your choice... and from this point onward everything should be clear. If you wan you can save your package to not have to go through this again next time...

Let me know if I can be of further assistance.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
connection to SQL 2012 error in windows 10 18 47
SQL 2008 - Disappearing Temp Table in Stored Procedure 24 63
T-SQL: Wrong Result 7 39
Rewriting a simple query 2 33
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

734 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