By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,576 Members | 1,970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,576 IT Pros & Developers. It's quick & easy.

Query Data and Column Names

P: 52
Hello all,

I'm trying to create some reports and I've run into a bit of a tricky query... I need 1 query that will basically return 2 columns...

column1 will be a list of certain column names in the table... like columnA, columnB, columnC etc....

column2 will be a list containing the value of the corresponding column name in column1... so the end result will look like

Expand|Select|Wrap|Line Numbers
  1. column1       column2
  2. --------     ---------
  3. columnA      valueA
  4. columnB      valueB
  5. columnC      valueC 
and so on....

Also, if someone can give me a solution to this I would also like to know if all the values would have to be of the same datatype.

Thanks in advance!!
Oct 14 '09 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
You're going to need to build a dynamic query. You can get the column names from sys.columns or syscolumns and link that to sys.tables or sysobjects.

Happy Coding!!!

--- CK
Oct 14 '09 #2

nbiswas
100+
P: 149
Lets say I have a table MyTable which has 2 columns Attribute and Value both of type varchar.

I will execute the following query

Expand|Select|Wrap|Line Numbers
  1. ;with cte1 as
  2. (
  3. select 
  4.     ROW_NUMBER() over(order by ordinal_position) as rn,  
  5.     column_name 
  6. from information_schema.columns
  7. where table_name = 'MyTable'
  8. )
  9. ,cte2 as
  10. (
  11. select    attribute, 1 as rn1,
  12.         value, 2 as rn2
  13. from MyTable
  14. )
  15. select    c1.column_name as column1
  16.         ,c2.attribute as column2 
  17. from cte1 c1
  18. inner join cte2 c2
  19. on c1.rn = c2.rn1
  20.  
  21. union all
  22.  
  23. select    c1.column_name as column1
  24.         ,c2.value as column2 
  25. from cte1 c1
  26. inner join cte2 c2
  27. on c1.rn = c2.rn2
This query is for SQL SERVER 2005+ .
In the first CTE I am picking up the column names and ensuring their orders via the row_number() function.
In the next part I am picking up the information from the MyTable table and explicity assigning 1 to the 1st column values(i.e. Attribute) here and 2 for the Value(i.e. 2nd column)

Then by using innerjoin followed by union I have accomplishing the task.

Note:- This is only an example and one of the many approaches. Take the idea if you have not get one as of now and put it as per your need.

Hope this helps.
Nov 8 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.