Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Get column names and values from dynamicq list of column names

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
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 …
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

604 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