SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to get the column names and values?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQLNEWBIZ
Starting Member

India
27 Posts

Posted - 04/26/2013 :  13:14:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/26/2013 :  13:28:05  Show Profile  Reply with Quote
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
Go to Top of Page

SQLNEWBIZ
Starting Member

India
27 Posts

Posted - 04/26/2013 :  13:35:39  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/26/2013 :  13:49:04  Show Profile  Reply with Quote
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).

Edited by - Lamprey on 04/26/2013 13:51:06
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/29/2013 :  00:39:20  Show Profile  Reply with Quote
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
Go to Top of Page

SQLNEWBIZ
Starting Member

India
27 Posts

Posted - 04/29/2013 :  01:27:06  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/29/2013 :  01:31:07  Show Profile  Reply with Quote
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
Go to Top of Page

SQLNEWBIZ
Starting Member

India
27 Posts

Posted - 04/29/2013 :  01:43:30  Show Profile  Reply with Quote
.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.

Edited by - SQLNEWBIZ on 04/29/2013 02:19:39
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/29/2013 :  02:21:11  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/29/2013 :  02:24:50  Show Profile  Reply with Quote
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
Go to Top of Page

SQLNEWBIZ
Starting Member

India
27 Posts

Posted - 04/29/2013 :  02:34:29  Show Profile  Reply with Quote
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]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/29/2013 :  02:47:18  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000