Solved

How to make the column

Posted on 2014-02-24
4
221 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
[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 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

617 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