| Author |
 Topic  |
|
|
SQLNEWBIZ
Starting Member
India
27 Posts |
 Posted - 04/26/2013 : 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
Flowing Fount of Yak Knowledge
4614 Posts |
|
|
SQLNEWBIZ
Starting Member
India
27 Posts |
Posted - 04/26/2013 : 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
Flowing Fount of Yak Knowledge
4614 Posts |
Posted - 04/26/2013 : 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). |
Edited by - Lamprey on 04/26/2013 13:51:06 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
|
|
SQLNEWBIZ
Starting Member
India
27 Posts |
Posted - 04/29/2013 : 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
India
52326 Posts |
Posted - 04/29/2013 : 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
India
27 Posts |
Posted - 04/29/2013 : 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. |
Edited by - SQLNEWBIZ on 04/29/2013 02:19:39 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 04/29/2013 : 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
India
52326 Posts |
Posted - 04/29/2013 : 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
India
27 Posts |
Posted - 04/29/2013 : 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
India
52326 Posts |
|
| |
Topic |
|
How to get the column names and values?