?
Solved

Get column names and values from dynamicq list of column names

Posted on 2014-03-10
4
Medium Priority
?
313 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
  • 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.
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…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

589 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