locked
Display columns where value=x RRS feed

  • Question

  • Hi,

    Is there a way I can display column names from a table where 'column' = 'value'

    E.g I have a table with column names 'column1','column2','column3' .

    I then insert a record : insert into mytable (column1,column2,column3) values ('x','x','y')

    How can I display the record in such a way that only columns with value 'x' are shown?

    Please help

    Tuesday, August 28, 2012 8:51 AM

Answers

  • You can do that as follow:

    CREATE TABLE #table (
    	c1 CHAR(1)
    	,c2 CHAR(1)
    	,c3 CHAR(1)
    	)
    INSERT INTO #table
    VALUES (
    	'x'
    	,'x'
    	,'y'
    	)
    INSERT INTO #table
    VALUES (
    	'x'
    	,'x'
    	,'y'
    	)
    INSERT INTO #table
    VALUES (
    	'x'
    	,'x'
    	,'y'
    	)
    INSERT INTO #table
    VALUES (
    	'y'
    	,'y'
    	,'y'
    	)
    INSERT INTO #table
    VALUES (
    	'x'
    	,'y'
    	,'x'
    	)
    SELECT CASE 
    		WHEN c1 <> 'x'
    			THEN '--'
    		ELSE c1
    		END AS c1
    	,CASE 
    		WHEN c2 <> 'x'
    			THEN '--'
    		ELSE c2
    		END AS c2
    	,CASE 
    		WHEN c3 <> 'x'
    			THEN '--'
    		ELSE c3
    		END AS c3
    FROM #table
    WHERE c1 IN ('x')
    	OR c2 IN ('x')
    	OR c3 IN ('x')

    out put as fellow:


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Basit Farooq Tuesday, August 28, 2012 9:35 AM
    • Marked as answer by Iric Wen Thursday, September 6, 2012 8:12 AM
    Tuesday, August 28, 2012 9:34 AM
  • Hi Jeff,

    this continuation of Farooq as per ur requirements.Please let me know if that wrks as per ur req


    DECLARE @string VARCHAR(500)

    ;WITH CTE AS
    (
    SELECT CASE 
    WHEN c1 <> 'x'
    THEN NULL
    ELSE c1
    END AS c1
    ,CASE 
    WHEN c2 <> 'x'
    THEN NULL
    ELSE c2
    END AS c2
    ,CASE 
    WHEN c3 <> 'x'
    THEN NULL
    ELSE c3
    END AS c3
    FROM #table
    WHERE c1 IN ('x')
    OR c2 IN ('x')
    OR c3 IN ('x')

    SELECT @string = (
    SELECT New1+',' FROM 
    (
    SELECT NEW1,COUNT(1) AS CNT FROM
    (

    SELECT * FROM CTE
    UNPIVOT
    (New FOR New1 IN (c1,c2,c3))AS pc
    ) AS NR
    GROUP BY NEW1
     )AS N
    INNER JOIN (SELECT COUNT(*) AS CNT FROM CTE ) AS N1 ON N.CNT = N1.CNT
    FOR XML PATH('') 

    SELECT @string  = SUBSTRING(@string,1,LEN(@string)-1)
    EXEC ('select '+ @String + ' From #table')


    Please have look on the comment

    • Proposed as answer by Santhosh H Tuesday, August 28, 2012 12:09 PM
    • Marked as answer by Iric Wen Thursday, September 6, 2012 8:12 AM
    Tuesday, August 28, 2012 11:05 AM

All replies

  • are you looking for comparing single column or x value in any columns?

    if its simple one column then use this query

    insert into mytable (column1,column2,column3) values ('x','x','y')
    
    Select * from mytable where column1='X'

    Otherwise you can have a look at this gallery item 

    http://gallery.technet.microsoft.com/scriptcenter/c0c57332-8624-48c0-b4c3-5b31fe641c58

    Regards
    Satheesh

    Tuesday, August 28, 2012 9:25 AM
  • You can do that as follow:

    CREATE TABLE #table (
    	c1 CHAR(1)
    	,c2 CHAR(1)
    	,c3 CHAR(1)
    	)
    INSERT INTO #table
    VALUES (
    	'x'
    	,'x'
    	,'y'
    	)
    INSERT INTO #table
    VALUES (
    	'x'
    	,'x'
    	,'y'
    	)
    INSERT INTO #table
    VALUES (
    	'x'
    	,'x'
    	,'y'
    	)
    INSERT INTO #table
    VALUES (
    	'y'
    	,'y'
    	,'y'
    	)
    INSERT INTO #table
    VALUES (
    	'x'
    	,'y'
    	,'x'
    	)
    SELECT CASE 
    		WHEN c1 <> 'x'
    			THEN '--'
    		ELSE c1
    		END AS c1
    	,CASE 
    		WHEN c2 <> 'x'
    			THEN '--'
    		ELSE c2
    		END AS c2
    	,CASE 
    		WHEN c3 <> 'x'
    			THEN '--'
    		ELSE c3
    		END AS c3
    FROM #table
    WHERE c1 IN ('x')
    	OR c2 IN ('x')
    	OR c3 IN ('x')

    out put as fellow:


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Basit Farooq Tuesday, August 28, 2012 9:35 AM
    • Marked as answer by Iric Wen Thursday, September 6, 2012 8:12 AM
    Tuesday, August 28, 2012 9:34 AM
  • Farooq, is it possible to do a select of record number1 and not display the column containing '--' ?

    i.e Still have the column in the CASE statements but it will not be displayed if value returned is '--'


    Tuesday, August 28, 2012 10:23 AM
  • Hi Jeff,

    this continuation of Farooq as per ur requirements.Please let me know if that wrks as per ur req


    DECLARE @string VARCHAR(500)

    ;WITH CTE AS
    (
    SELECT CASE 
    WHEN c1 <> 'x'
    THEN NULL
    ELSE c1
    END AS c1
    ,CASE 
    WHEN c2 <> 'x'
    THEN NULL
    ELSE c2
    END AS c2
    ,CASE 
    WHEN c3 <> 'x'
    THEN NULL
    ELSE c3
    END AS c3
    FROM #table
    WHERE c1 IN ('x')
    OR c2 IN ('x')
    OR c3 IN ('x')

    SELECT @string = (
    SELECT New1+',' FROM 
    (
    SELECT NEW1,COUNT(1) AS CNT FROM
    (

    SELECT * FROM CTE
    UNPIVOT
    (New FOR New1 IN (c1,c2,c3))AS pc
    ) AS NR
    GROUP BY NEW1
     )AS N
    INNER JOIN (SELECT COUNT(*) AS CNT FROM CTE ) AS N1 ON N.CNT = N1.CNT
    FOR XML PATH('') 

    SELECT @string  = SUBSTRING(@string,1,LEN(@string)-1)
    EXEC ('select '+ @String + ' From #table')


    Please have look on the comment

    • Proposed as answer by Santhosh H Tuesday, August 28, 2012 12:09 PM
    • Marked as answer by Iric Wen Thursday, September 6, 2012 8:12 AM
    Tuesday, August 28, 2012 11:05 AM