x

column names of a query result

my query resultset has three columns boolean1, boolean2, boolean3

i want to get the names of the columns for which value is true

select where boolean1 or boolean2 or boolean3
more ▼

asked Jul 18, 2011 at 03:39 AM in Default

dileepmgu gravatar image

dileepmgu
11 1 1 1

Have you got sample code that you've used so far?
Jul 18, 2011 at 03:56 AM Mrs_Fatherjack

@dileepmgu If you need to get the list of columns for each record, but that list should only include those columns for which the value is true while selecting a record is based on the condition that any of these is true then (assuming that your columns are actually of a bit data type):

select
    rtrim(
    case when boolean1 = 1 then 'boolean1 ' else '' end +
    case when boolean2 = 1 then 'boolean2 ' else '' end +
    case when boolean3 = 1 then 'boolean3' else '' end) List
    from YourTable
    where boolean1 = 1 or boolean2 = 1 or boolean3 = 1;
The above will return a space-delimited list of those columns per record which personally have the value of 1. Who knows, maybe this is what you are asking, but I am not really sure what is the purpose of such a selection :)
Jul 19, 2011 at 02:03 PM Oleg
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

That is not exactly how TSQL works. You request column names and the values that are stored in those columns where certain predicates are met. You could use something like this to see what values you have in your table(s)

USE [adventureworks]
GO

DECLARE @TestTable TABLE
    (
      BoolOne BIT ,
      BoolTwo BIT ,
      BoolThree BIT
    )

WHILE ( SELECT
            COUNT(*)
        FROM
            @TestTable
      ) < 300 
    BEGIN
        INSERT  INTO @TestTable
                ( [BoolOne] ,
                  [BoolTwo] ,
                  [BoolThree] 
                )
                SELECT
                    ABS(CHECKSUM(NEWID())) % 2 ,
                    ABS(CHECKSUM(NEWID())) % 2 ,
                    ABS(CHECKSUM(NEWID())) % 2 
    END 


SELECT
    [tt].[BoolOne] ,
    [tt].[BoolTwo] ,
    [tt].[BoolThree] ,
    COUNT(*) AS [How many]
FROM
    @TestTable AS tt
GROUP BY
    [tt].[BoolOne] ,
    [tt].[BoolTwo] ,
    [tt].[BoolThree]
more ▼

answered Jul 18, 2011 at 03:59 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.3k 75 78 108

(comments are locked)
10|1200 characters needed characters left

@FatherJack,

My post here doesn't answer the OP's question. I just wanted to show you a trick so you don't have to type as much to make test data nor wait as long while it develops. ;-) It'll also run in a flash compared to the While Loop. The following creates 100,000 rows in the proverbial blink of an eye. Try the same thing with your loop.

--===== Conditionally drop the test table to make reruns in SSMS easier
     IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL DROP TABLE #TestTable;

--===== Now, create and populate the semi-persistant test table on-the-fly.
 SELECT TOP (100000) --<<< Change this number to suit
        BoolOne   = CAST(ABS(CHECKSUM(NEWID())) % 2 AS BIT),
        BoolTwo   = CAST(ABS(CHECKSUM(NEWID())) % 2 AS BIT),
        BoolThree = CAST(ABS(CHECKSUM(NEWID())) % 2 AS BIT)
  INTO #TestTable
  FROM sys.all_columns ac1
 CROSS JOIN sys.all_columns ac2
;
more ▼

answered Jul 18, 2011 at 09:05 PM

Jeff Moden gravatar image

Jeff Moden
1.7k 2 4 8

@Jeff I must admit I read @Fatherjack's query with an eyebrow raised...
Jul 19, 2011 at 02:07 PM ThomasRushton ♦

@Jeff Moden - Thanks Jeff. I know the loop is ugly and I'm not wholly sure why I went with it. I dont generally create more than a few rows and almost always go for GO NNN if I do ... :-/ Will add your method to my templates and endeavour to adopt it as standard, it is certainly swift. Congrats on the Exceptional DBA nomination and good luck in the finals.

@ThomasRushton - today I will do something to raise the other eyebrow and tomorrow we will give them both a workout :)

I'll leave the answer "as is" so that people have the chance to compare the options.
Jul 20, 2011 at 01:01 AM Fatherjack ♦♦

@FatherJack,

Thank you, good Sir, for the congrats on the nomination.

Understood on the loop and thank you for the feedback there.
Jul 26, 2011 at 07:26 PM Jeff Moden
(comments are locked)
10|1200 characters needed characters left

What about the UNPIVOT operator? You can then easily query the column names and the values.

DECLARE @tbl TABLE (
    Boolean1 bit,
    Boolean2 bit,
    Boolean3 bit
);

INSERT INTO @tbl(Boolean1, Boolean2, Boolean3)
SELECT 1,0,0 UNION ALL
SELECT 1,0,1;

WITH Unpivotted AS (
    SELECT
       ColName,
       CAST(BoolValue as tinyint) AS BoolValue
    FROM
       (SELECT Boolean1, Boolean2, Boolean3 FROM @tbl) p
       UNPIVOT
       (BoolValue FOR ColName IN (Boolean1, Boolean2, Boolean3)) AS unpvt
)
SELECT
    ColName
FROM Unpivotted
GROUP BY ColName
HAVING MAX(BoolValue) = 1
more ▼

answered Jul 20, 2011 at 12:12 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x17

asked: Jul 18, 2011 at 03:39 AM

Seen: 1268 times

Last Updated: Jul 18, 2011 at 03:39 AM