Solved

How to make the column

Posted on 2014-02-24
4
216 Views
Last Modified: 2014-02-24
I have table fieldstatus where field and flag column are there.

In this table field contains the list of fields from table mstchvs.
Now here there is flag column which contains 1 or 0.


If i write select * from mstchvs
I get all the column names.

But i want to list those columns where field=0 in fieldstatus table.

i,e

select (column names from fieldstatus table where flag=0) from mstchvs
0
Comment
Question by:searchsanjaysharma
  • 2
  • 2
4 Comments
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39882459
Assuming field contains a comma-delimited list of field(s), and using dynamic-SQL:
DECLARE	@SQL varchar(max)

SELECT	@SQL = 'select ' + field + ' from mstchvs'
FROM	fieldstatus
WHERE	flag = 0

EXEC(@SQL)

Open in new window

0
 

Author Comment

by:searchsanjaysharma
ID: 39882493
It picks only the last field
0
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 500 total points
ID: 39882594
Assumes there are multiple rows that have flag = 0, field contains a single value; build a comma-delimited field-list for the dynamic SQL, and execute:
DECLARE	@SQL varchar(max)

SELECT	@SQL =	'select'
		+ Stuff((	SELECT ', ' + field AS [text()] 
				FROM	(	SELECT DISTINCT field
						FROM	fieldstatus
						WHERE	flag = 0
					) x
				For XML PATH ('')),1,1,'') 
		+ ' from mstchvs'

EXEC(@SQL)

Open in new window

0
 

Author Closing Comment

by:searchsanjaysharma
ID: 39882688
tx
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

776 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