You must Sign In to post a response.
  • Category: SQL Server

    How to select only those columns and its values where column values are greater than Zero

    Hi All,

    I have a table where around 40 columns are available but only one row. Now I want to get only those columns and its values where column values are greater than zero.

    My Table Structure:

    Size Color Shade Logo Sharp
    3 0 5 8 0

    My Desired Output is:
    Size Shade Logo
    3 5 8

    Thanks in advance.
  • #756231
    Hi,

    One way to do this is using Scalar Function and Dynamic SQL.

    Create a function that returns non zero columns name in comma separated value (example Size, Shade, Logo).

    Then using this function create a dynamic query to select only non zero column.

    Example:
    Scalar Function:
    CREATE FUNCTION ReturnNonZeroColumns
    (
    )
    RETURNS varchar(max)
    AS
    BEGIN
    -- Declare the return variable here
    DECLARE @ResultVar varchar(max);
    DECLARE @colVal int;
    set @ResultVar = '';
    -- Add the T-SQL statements to compute the return value here
    SELECT @colVal = Size from TableName;
    If @colVal > 0
    begin
    Set @ResultVar = @ResultVar + 'Size '
    End

    SELECT @colVal = Color from TableName;
    If(@colVal >0)
    begin
    Set @ResultVar = @ResultVar + ', Color '
    End

    SELECT @colVal = Shade from TableName;
    If(@colVal >0)
    begin
    Set @ResultVar = @ResultVar + ', Shade '
    End

    -- Return the result of the function
    RETURN @ResultVar

    END
    GO
    ----------------------------------------------------

    example:
    Dynamic Query:

    declare @query varchar(max);
    set @query = 'select ' + dbo.ReturnNonZeroColumns() + ' from TableName';
    exec (@query);


    Hope this helps you.

    Thanks,
    Praveen

  • #756258
    Use below SQL script to get your output

    CREATE TABLE TempTable
    (
    ColumnName VARCHAR(10),
    [Value] INT
    )

    DECLARE @ColumnName_Unpvt1 AS VARCHAR(max),
    @ColumnName_Unpvt2 AS VARCHAR(max),
    @ColumnName_pvt AS VARCHAR(max),
    @SQLQuery_Unpvt AS VARCHAR(max),
    @SQLQuery_pvt AS VARCHAR(max)

    SELECT @ColumnName_Unpvt1 = COALESCE(@ColumnName_Unpvt1 + ', ', '')
    + LTRIM(RTRIM(a.NAME)),
    @ColumnName_Unpvt2 = COALESCE(@ColumnName_Unpvt2 +', ', '')
    + 'CAST(' + LTRIM(RTRIM(a.NAME))
    + ' AS VARCHAR) AS ' + LTRIM(RTRIM(a.NAME))
    FROM (SELECT sc.NAME AS NAME
    FROM sys.columns sc
    JOIN sys.tables st
    ON st.object_id = sc.object_id
    WHERE st.NAME = [Your Table Name]) a

    SET @SQLQuery_Unpvt = 'INSERT INTO TempTable SELECT ColumnName, value FROM (SELECT '
    + @ColumnName_Unpvt2 + ' FROM [Your Table Name]) p'
    + ' UNPIVOT (value FOR ColumnName IN ('
    + @ColumnName_Unpvt1
    + ')) AS unpvt WHERE value>0'

    EXEC (@SQLQuery_Unpvt)

    SELECT @ColumnName_pvt = COALESCE(@ColumnName_pvt + ', ', '')
    + LTRIM(RTRIM(a.ColumnName))
    FROM (SELECT DISTINCT ColumnName
    FROM TempTable) a



    SET @SQLQuery_pvt='SELECT ' + @ColumnName_pvt
    +
    ' FROM ( SELECT [Value], ColumnName FROM TempTable ) d PIVOT ( MAX([Value]) FOR ColumnName IN ('
    + @ColumnName_pvt + ') ) piv'

    EXEC (@SQLQuery_pvt)

    DROP TABLE TempTable

  • #756653
    Try this simple query and let me know -

    SELECT 'SELECT ID, ''' + COLUMN_NAME + ''' AS LessonName,
    [' + COLUMN_NAME + '] AS Lesson ' +
    + 'FROM Lesson WHERE ID = @ID AND LEN([' + COLUMN_NAME + ']) > 2 UNION'

    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'YourTableName'

    Thanks & Regards,
    Abhijith

    Mail - abhijith.pn@gmail.com
    Check My Blog - http://www.solvemytechissue.in/


  • Sign In to post your comments