CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012
Testuser
Testuser

Reputation: 1227

How to get the Column Header for the SQL select query with Pipe delimited

I written an SQL Server select Query with Format "|" delimited but I did not get the column Name Header only value is printing. I am new in SQL server tried different way as column alias but it not worked out. Please help on this.

     SELECT ID  + '|' 
          +  LTL + '|' 
          + Safely + '|' 
          + Dock + '|' 
          + Required + '|'
          + Hours + '|'
          + Dock + '|' 
          + Jack + '|' 
          + Dolly + '|'
          + convert(varchar(100), Pallets) + '|'
          + convert(varchar(100), wgPallets) + '|'
          + convert(varchar(100), NoBoxes) + '|'
          + convert(varchar(100), WgBoxes) + '|'
          + convert(varchar(100), Cabinets) + '|'
          + convert(varchar(100), Racks) + '|'
          + Batteries + '|' 
          + Sited + '|' 
          + convert(varchar(23),Date_Enter,121) + '|' 
          FROM sales

How to display the coulmn Header as below along with with value using above query.
ID|LTL|Safely|Dock|Required|Hours|Dock|Jack|Carrier_Dolly|Pallets|WgPallets|NoBoxes|WgBoxes|Cabinets|Racks|Batteries|Sited|Date_Enter

Thanks in Advance

Upvotes: 0

Views: 972

Answers (1)

Knut Boehnert
Knut Boehnert

Reputation: 601

If you want your headers to appear as result line in your SQL you have to tell SQL engine so.

This can be done with a UNION:

SELECT
  'ID|LTL|Safely|Dock|Required|Hours|Dock|Jack|Carrier_Dolly|Pallets|WgPallets|NoBoxes|WgBoxes|Cabinets|Racks|Batteries|Sited|Date_Enter' AS csv_output
UNION
SELECT 
  ID  + '|' 
    +  LTL + '|' 
    + Safely + '|' 
    + Dock + '|' 
    + Required + '|'
    + Hours + '|'
    + Dock + '|' 
    + Jack + '|' 
    + Dolly + '|'
    + convert(varchar(100), Pallets) + '|'
    + convert(varchar(100), wgPallets) + '|'
    + convert(varchar(100), NoBoxes) + '|'
    + convert(varchar(100), WgBoxes) + '|'
    + convert(varchar(100), Cabinets) + '|'
    + convert(varchar(100), Racks) + '|'
    + Batteries + '|' 
    + Sited + '|' 
    + convert(varchar(23),Date_Enter,121)
FROM
  sales
;

Finally:

This is not very elegant. In the export options (which depend on the tool you use) there is normally an option to export the headers of the columns as a separate line and set the delimiter (in your case pipe symbol).

Personally I recommend this approach because I can use the same view for the results and be able to separate the logic for export and data selection from each other.

Upvotes: 1

Related Questions