Listing True Boolean Fields as a query result row
-
I wantto list each boolean fields if true as rows.
is it possible via SQL(T-SQL syntax in SQL Server)?
thanks
-
The following code is one way of providing what you asked for...however you may require more flexilibilty in which case you really need to post back.
I would also strongly suggest that you read this:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you are prepared to provide the required CREATE/INSERT statements as the article above recommends and also your desired results you will find that you will receive quick and tested responses from this forum....all of whom give their time freely.
It would also be useful to know the "scale" of your problem...ie how many rows
Heres the code :
USE TEMPDBGO
---=== create a table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TABLE_RECORDS]') AND type in (N'U'))
DROP TABLE TABLE_RECORDS
CREATE TABLE [TABLE_RECORDS](
[ID] [int] IDENTITY(1,1) NOT NULL,
[B1] [bit] NOT NULL,
[B2] [bit] NOT NULL,
[B3] [bit] NOT NULL
)
---=== insert some records
SET IDENTITY_INSERT [dbo].[TABLE_RECORDS] ON;
INSERT INTO [dbo].[TABLE_RECORDS]([ID], [B1], [B2], [B3])
SELECT 1, 1, 1, 1 UNION ALL
SELECT 2, 1, 0, 1 UNION ALL
SELECT 3, 0, 0, 0
GO
SET IDENTITY_INSERT [dbo].[TABLE_RECORDS] OFF;
---=== results
SELECT ID, CASE WHEN B1 = 1 THEN 'B1 SELECTED' ELSE NULL END AS Expr1
FROM dbo.TABLE_RECORDS
WHERE (ID = 2) AND (B1 = 1)
UNION ALL
SELECT ID, CASE WHEN B2 = 1 THEN 'B2 SELECTED' ELSE NULL END AS Expr1
FROM dbo.TABLE_RECORDS
WHERE (ID = 2) AND (B2 = 1)
UNION ALL
SELECT ID, CASE WHEN B3 = 1 THEN 'B3 SELECTED' ELSE NULL END AS Expr1
FROM dbo.TABLE_RECORDS
WHERE (ID = 2) AND (B3 = 1)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
By cross joining gah's TABLE_RECORDS to a virtual tally table, I have arrived at the following
SELECT ID, Expr1FROM
(
SELECT ID, CASE N WHEN 1 THEN CASE WHEN B1 = 1 THEN 'B1 SELECTED' END
WHEN 2 THEN CASE WHEN B2 = 1 THEN 'B2 SELECTED' END
WHEN 3 THEN CASE WHEN B3 = 1 THEN 'B3 SELECTED' END
END AS Expr1
FROM dbo.TABLE_RECORDS
CROSS JOIN
(SELECT 1 AS N UNION ALL SELECT 2 UNION ALL SELECT 3) AS Tally
) AS Z
WHERE NOT Expr1 IS NULL
AND ID = 2
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply