column names of a query result

Question

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

Answer 1

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]

Answer 2

0

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

answered Jul 18 '11 at 09:05 PM

Jeff Moden
1k ● 1 ● 3

@Jeff I must admit I read @Fatherjack's query with an eyebrow raised...

Jul 19 '11 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 '11 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 '11 at 07:26 PM Jeff Moden

(comments are locked)

Answer 3

0

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

answered Jul 20 '11 at 12:12 AM

Pavel Pawlowski
20.3k ● 5 ● 10 ● 20

(comments are locked)

column names of a query result

Follow this question

Related Questions