[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Populating  first  table row with the table’s column names in sql server 2005

Posted on 2011-10-11
8
Medium Priority
?
221 Views
Last Modified: 2012-05-12
I have a stored procedure  in sql server 2005 that creates a temp table #temptable whose column names can vary….
What I want to do is copy the column names of #temptable into row 1 of #temptable, resulting in for example;

#temptable
C1      C2      C3
C1      C2      C3

any help/suggestions  welcome...thank you
0
Comment
Question by:blossompark
8 Comments
 
LVL 10

Accepted Solution

by:
Umar Topia earned 375 total points
ID: 36947930
There is a built in stored procedure called sp_columns that takes a tablename as a parameter. this returns all information for the given table

sp_columns Your table name

Then you can insert the data into your temptable
0
 

Author Comment

by:blossompark
ID: 36948096
Hi umartopia,
thanks for your response,
am trying that now and will update you with results
0
 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 375 total points
ID: 36948159
Hi,

Below query also will help you to get the all details.

select * from tempdb.sys.columns where object_id =
object_id('tempdb..#temptable')

Open in new window


- Bhavesh
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 375 total points
ID: 36948164
may I ask why you want to do this?
0
 
LVL 9

Assisted Solution

by:sachinpatil10d
sachinpatil10d earned 375 total points
ID: 36948318
Try this


select ',' + column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like 'ProviderSettings' for xml path('')

Open in new window

0
 

Author Comment

by:blossompark
ID: 36948666
Hi angellll,
The output of the query is fed to an application that combines the data with an xlsx template.
For some reason, the resultant xlsx spreadsheet is not being populated with the Column Names,,,
as a workaround, I intend putting the column names into Row1 and use that row as the column header.
0
 

Author Comment

by:blossompark
ID: 36960927
Hi umartopia, Brichsoft, sachinpatil10d,
Thanks for your code , however in my case it is returning empty result sets..

Maybe the issue is with the table I am trying to reference.
The code for the table is attached, which I got from Lowfatspread, and it produces the output illustrated…
Just adding this as it may help proceedings, thanks..
 
/* pivot for hardware by Lowfatspread*/
declare @strSQLhw varchar(max)
, @colshw varchar(max)
, @cols varchar(max);

;with CTE as (
	select 	dateadd(m, datediff(m, 0, @start_date_range), 0) as st, 
		dateadd(m, datediff(m, 0, @end_date_range), 0) as et
	union all
	select dateadd(m, 1, st),
			et
	from CTE 
	where dateadd(m, 1, st) <= et
)
select @colshw = stuff((select '],[' + right(convert(varchar, st, 106), 8)+'] as [' + right(convert(varchar, st, 106), 8)+' Hardware'
 from CTE for xml path('')), 1, 2, '') + ']'

;with CTE as (
	select 	dateadd(m, datediff(m, 0, @start_date_range), 0) as st, 
		dateadd(m, datediff(m, 0, @end_date_range), 0) as et
	union all
	select dateadd(m, 1, st),
			et
	from CTE 
	where dateadd(m, 1, st) <= et
)
select @cols = stuff((select '],[' + right(convert(varchar, st, 106), 8)
 from CTE for xml path('')), 1, 2, '') + ']'



set @strSQLhw = '

select server_name, ' + @colshw + '  from (
select server_name, 
right(convert(varchar, dateadd(m, datediff(m, 0, [resolved_date]), 0), 106), 8)
 as eom, PROD_CAT1
		from #extranetHardware
		where [resolved_date] >= ''' + 
convert(varchar, @start_date_range,
 112) + ''' and [resolved_date] < ''' +convert(varchar, @end_date_range+1,112) + ''') o
		pivot (count(PROD_CAT1) for eom in (' + @cols +' )) as p '

print (@strSQLhw)
execute (@strSQLhw)

Open in new window


lfs table
0
 

Author Closing Comment

by:blossompark
ID: 37029897
Hi,
 thanks for all your inputs...have decided top leave this for the time being and am closing this out....thanks again
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 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