How to get the column names and values? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get the column names and values?

Author

Topic

SQLNEWBIZ
Starting Member

27 Posts

Posted - 2013-04-26 : 13:14:05

Hello All,
Please anyone help me..I want to retrieve column names which has values as "True".First Two Column Names are username and password which is varchar,the rest of them are bit.So I want only the column names,which has values 'True'.
Is it possible?Here is the query that retrieves column names,that i have used.
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SectionsAuthentication'

Thanks in Advance..

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-26 : 13:28:05

Yes, it is possible.

Since you already know the column names, writing a query should be pretty simple. If you are asking for a dynamic query that is a little more compicated, but INFORMATION_SCHEMA.COLUMNS should have all the information you need to build such a query.

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

SQLNEWBIZ
Starting Member

27 Posts

Posted - 2013-04-26 : 13:35:39

Thankyou for your prompt reply..
this is the result of my query..that i used to retreive the column names.in these only some of them have true values..
so can you please help me,to modify my query..?
Thanks In Advance..

COLUMNNAME
username
passwrd
Company
DepartsAndDesgints
CompDetails
SecsAuthentication
Budgeting
BudgetMangmnt
SavingsBudget
BudgetDeptWise
CRM
Managemntcrm
Appointments
Complaints
Inventory
InvMnangement
ProdManger
OrderByproduction
StoreKeeper
InvPlanning
InvCost
InvControl
FinishedGoods
Customers
MaintCustomers
SalesOrder
Suppliers
MaintSuppliers
PurchaseOrder
PurBills
PointOfSale
SalesInvoice
StockOnHand
SalesRefund
Employee
MainEmployee
MaintPayroll
DepartmentsView
Payroll
AttSheet
LeaveForm
Loan
Bonus
OTRecord
getpayroll
advance
empadvance
accounts
accheads
accpayable
accreceivable
accsalaryslip
costing
classifcosting
acctofunction
elementsofcosting
genlegder
reports
reportsmangmnt
compfinancerpts
salesrpts
salessummrpt
salesbillsrpts
dispatchedgoodsrpts
Purchaserpts
purbillsrpts
pursummaryrpts
employeerpts
salarysliprpts
deptsrpts
accspayrpts
accsreceivablerpts
genledgerrpts
bank
bankdetails

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-26 : 13:49:04

What do you want for output of the query?

That is why I posed those link in my response above. If you supply sample data in a consumable format along with expected output, then we can create tested query against your data. The outcom is you get tested code and we don't have to go around and around asking questions (or at least as much).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-29 : 00:39:20

do you mean retrieve list of columns in table with at least one true value in them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SQLNEWBIZ
Starting Member

27 Posts

Posted - 2013-04-29 : 01:27:06

Thankyou for your reply, there both true and false values in it,I want the column names whose values are "TRUE" only. Is it possible?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-29 : 01:31:07

but there are multiple rows isnt it?
So my question was what if there are both true and false value rows?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SQLNEWBIZ
Starting Member

27 Posts

Posted - 2013-04-29 : 01:43:30

.Firstly,Thankyou for reply.The result that i want is, the column names that is validated against username and password and whose values are true.I have tries two queries.,Select * From SectionsAuthentication Where UserName=@UserName And Passwrd=@Passwrd And @Valid='True'
And the second query I tried is ,to return column names
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SectionsAuthentication'
And I want to ask if its possible to validate the conditions in the second query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-29 : 02:21:11

ok. in that case what you could do is to unpivot the data and then do check like


SELECT ColumnName
FROM
(
SELECT *
FROM
(
SELECT *
FROM Yourtable
WHERE username = <your username value>
AND password = <your password>
)t
UNPIVOT (Val FOR COlumnName IN ([Company],
[DepartsAndDesgints],
[CompDetails],
...
[bankdetails]))u
)r
WHERE Val='true'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-29 : 02:24:50

for making column list dynamic use logic like


DECLARE @columnList varchar(3000),@SQL varchar(max)

SET @columnList = STUFF((SELECT ',[' + COLUMN_NAME + ']' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SectionsAuthentication'
AND DATATYPE = 'bit'),1,1,'')

SELECT @SQL='SELECT ColumnName
FROM
(
SELECT *
FROM
(
SELECT *
FROM Yourtable
WHERE username = <your username value>
AND password = <your password>
)t
UNPIVOT (Val FOR COlumnName IN (' + @columnlist + '))u
)r
WHERE Val=1'

--PRINT(@SQL)
EXEC(@SQL)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SQLNEWBIZ
Starting Member

27 Posts

Posted - 2013-04-29 : 02:34:29

THANKYOU Vishak .This one helped me to get my actual result.

SELECT ColumnName
FROM
(
SELECT *
FROM
(
SELECT *
FROM Yourtable
WHERE username = <your username value>
AND password = <your password>
)t
UNPIVOT (Val FOR COlumnName IN ([Company],
[DepartsAndDesgints],
[CompDetails],
...
[bankdetails]))u
)r
WHERE Val='true'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

[/quote]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-29 : 02:47:18

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources