Solved

Get column names and values from dynamicq list of column names

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now