August 16, 2012 at 12:21 pm
Hello,
Does anyone know of a handy trick to get the first row of the results of a Select statement to be the names of each column in the Select list?
Example:
Table_1 has columns Col_1, Col_2, Col_3
The table contains one row:
aaa,bbb,ccc (listed as CSV)
I need to query the table and receive
Col_1,Col_2,Col_3
aaa,bbb,ccc
thanks for ideas
August 16, 2012 at 12:28 pm
May I ask why?
August 16, 2012 at 12:35 pm
Quite an odd requirement but it is going to take some work on your end.
Something like this should work.
select 'Col_1' as Col_1, 'Col_2' as Col_2, 'Col_3' as Col_3, 0 as SortOrder
union all
select Col_1, Col_2, Col_3, 1
From YourTable
Order by SortOrder, Some other columns here
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 16, 2012 at 12:39 pm
Sure. A client wants it that way. They want a CSV file with the contents of a view and they say they need the first row to be the column names. Granted, that row will always be the same unless the view structure changes. But they insist. Just trying to find a way to do that in a query.
The following will list the column names but I have not figured out how to "union" that to the query results.
DECLARE @ColumnList varchar(4000)
DECLARE @Table varchar(128)
SET @ColumnList = ''
SET @Table='forsalereportview'
SELECT @ColumnList = @ColumnList + c.name + ', '
FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id
WHERE o.name = @Table
ORDER BY colid
SELECT Substring(@ColumnList, 1, Datalength(@ColumnList) - 2)
August 16, 2012 at 12:52 pm
Ken Davis (8/16/2012)
Sure. A client wants it that way. They want a CSV file with the contents of a view and they say they need the first row to be the column names. Granted, that row will always be the same unless the view structure changes. But they insist. Just trying to find a way to do that in a query.The following will list the column names but I have not figured out how to "union" that to the query results.
DECLARE @ColumnList varchar(4000)
DECLARE @Table varchar(128)
SET @ColumnList = ''
SET @Table='forsalereportview'
SELECT @ColumnList = @ColumnList + c.name + ', '
FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id
WHERE o.name = @Table
ORDER BY colid
SELECT Substring(@ColumnList, 1, Datalength(@ColumnList) - 2)
When using the import/export wizard (or an internally developed SSIS package) to create the csv file, one of the options for the flat file is to include the column headers in the file. No need to do it in a query.
August 16, 2012 at 12:52 pm
Ken Davis (8/16/2012)
Sure. A client wants it that way. They want a CSV file with the contents of a view and they say they need the first row to be the column names. Granted, that row will always be the same unless the view structure changes. But they insist. Just trying to find a way to do that in a query.The following will list the column names but I have not figured out how to "union" that to the query results.
DECLARE @ColumnList varchar(4000)
DECLARE @Table varchar(128)
SET @ColumnList = ''
SET @Table='forsalereportview'
SELECT @ColumnList = @ColumnList + c.name + ', '
FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id
WHERE o.name = @Table
ORDER BY colid
SELECT Substring(@ColumnList, 1, Datalength(@ColumnList) - 2)
You could add the SortOrder logic I showed you above and use this as your first query. This of course assumes you are selecting every column. You will need to alias the columns as the original name for this to work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 16, 2012 at 12:53 pm
I was just about to suggest using SSIS instead but it looks like Lynn beat me to it. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 16, 2012 at 12:55 pm
Sean Lange (8/16/2012)
I was just about to suggest using SSIS instead but it looks like Lynn beat me to it. 😀
Something about using the right tool for the job at hand. 😉
August 16, 2012 at 12:57 pm
Lynn Pettis (8/16/2012)
Sean Lange (8/16/2012)
I was just about to suggest using SSIS instead but it looks like Lynn beat me to it. 😀Something about using the right tool for the job at hand. 😉
Clearly you don't work where I do. If there is a wrong to do something that is the edict here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 16, 2012 at 12:59 pm
Sean Lange (8/16/2012)
Lynn Pettis (8/16/2012)
Sean Lange (8/16/2012)
I was just about to suggest using SSIS instead but it looks like Lynn beat me to it. 😀Something about using the right tool for the job at hand. 😉
Clearly you don't work where I do. If there is a wrong to do something that is the edict here.
I guess the trick you have had to learn is to make it look like you are using the wrong tool when in fact it is the right tool.
Okay, that confused me. :w00t:
August 16, 2012 at 12:59 pm
Thanks for the ideas Sean and Lynn. I'm actually helping another DBA. There are a few other requirments such as stripping out some characters and he wrote a query to accomplish that. So I was trying to make it work with his query.
But *duh* I can drop his query in the export wizard and check that box to include column headers. Going to try that now...
August 16, 2012 at 1:09 pm
I would agree with SSIS in general, but if that's not an option and you must stay in T-SQL the easiest way is to quick-hack the results for BCP, like so (see the second post down):
It's similar to the above discussed union but it's forced into BCP without having to fight with sorting components.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 29, 2014 at 11:15 am
Hi,
I had the same issue from the same type of client.
I sorted it out with a bit of dynamic SQL to create the Union query.
Note: because all the data must be converted to varchar due to the new first line containing column titles, formats need to be taken into account (for dates at least).
[Code="sql"]
Declare @TableName nvarchar(max)
Declare @ListColumns nvarchar(max)
Declare @ListColumnsConverted nvarchar(max)
Declare @QueryColumnNames nvarchar(max)
Declare @QueryTableValues nvarchar(max)
Declare @QueryHeaderAndRows nvarchar(max)
Set @TableName = 'YourTable'
--Get the column hearders from the system table
SELECT @ListColumns = coalesce(@ListColumns + ', ', '') + convert(varchar(500),COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
ORDER BY ORDINAL_POSITION
--Get the same list of column from the system table but adding a convert statement (All columns must be converted to varchar because of the first row containing only alphanumeric)
SELECT @ListColumnsConverted= STUFF((SELECT ',' + Case When DATA_TYPE = 'datetime' Then
'Convert(VARCHAR(11), ' + convert(varchar(max), COLUMN_NAME) + ',103)'
Else
'Convert(varchar(max),' + convert(varchar(max), COLUMN_NAME) + ')'
End
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
ORDER BY ORDINAL_POSITION
FOR XML PATH('')), 1, 1, '')
-- Create the Select statement containing only the headers names
SET @QueryColumnNames = 'Select ''' + Replace(@ListColumns, ', ', ''', ''') + ''''
-- Creating the statement with the values
SET @QueryTableValues = 'Select ' + @ListColumnsConverted + ' From ' + @TableName
-- Union all on both
SET @QueryHeaderAndRows = @QueryColumnNames + ' Union All ' + @QueryTableValues
EXEC (@QueryHeaderAndRows)
[/Code]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply