?
Solved

Get column names and values from dynamicq list of column names

Posted on 2014-03-10
4
Medium Priority
?
297 Views
Last Modified: 2014-03-11
Racking my brains on this. Have tried a number of methods and binned them.

Need to get the column names and field values based on a dynamic build list of columns.

Example. I have MyTable with ID, ColA, ColB...etc.

Lets say the list of columns is ColB, ColC, ColF

So I would want to see the data returned as

ID            ColName        colValue

1              ColB                 100
1              ColC                     0
1              ColF                   Null
2              ColB                  200
2              ColC                   1753-01-01
2              ColF                    100

The list will need to be a variable holding the column names.

thanks in advance.
0
Comment
Question by:BrightRaven
[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
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39917351
0
 

Author Comment

by:BrightRaven
ID: 39917587
Guy,

I have seen this article and it works for fixed column names.
When I use a variable for the column names it tells me that the column types do not match etc.

I want to keep [Client No_] but all other columns to be dynamic! Also, with unpivot/pivot will I loose NULL returned values?

My code as it stands is;
DECLARE
	@ColNames  AS NVARCHAR(MAX),
	@sql AS NVARCHAR (MAX)

SET @ColNames = 'DOA,COI,AO'

SET @sql = 

'
SELECT [Client No_], convert(varchar(100),FieldName) AS DOI, convert(varchar(100),FieldValue) AS COI
FROM 
   (
	SELECT
		[Client No_], convert(varchar(100),[Date of Incorporation]) AS DOI, convert(varchar(100),[Country of Incorporation]) AS COI, convert(varchar(100),[Administrative Office]) AS AO
	FROM
		Client
   ) p
	UNPIVOT
   (
   FieldValule FOR FieldName IN (' + @ColNames + ')
   )AS unpvt;
'

--PRINT @sql
EXECUTE sp_executeSQL @sql

Open in new window


Thanks
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 total points
ID: 39917613
so I understand the different columns from Client you want to unpivot have different data types?
in that case, there is no "simple" solution, as you will need to bring all the columns you want to unpivot to a single data type.
other than that, your code seems fine ...
0
 

Author Closing Comment

by:BrightRaven
ID: 39920740
Although not the final solution it steered me to the solution. Thank you.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

777 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