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  |
|