Solved

Get column names and values from dynamicq list of column names

Posted on 2014-03-10
4
284 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 142

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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

770 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