How to Show Column Value Only One Time If It Is Repeated and Blank Until Different Value Comes in SQL

how to show column value only one time if it is repeated and blank until different value comes in sql

You could do it like this:

SELECT
    CASE WHEN ROW_NUMBER() OVER(PARTITION BY Category ORDER BY BudgetType) = 1 
    THEN Category ELSE NULL END AS 'Category Caption'
    , Category
    , BudgetType
FROM yourTable
ORDER BY Category, BudgetType

But as Mikael mentioned, this is - in most cases - really not the way it should be done. Just think that the client may, for example, want to change the sorting within the categories, then the 'first' row within each category will be a different one.

Display only first row in SQL

You can do something like this with ROW_NUMBER and CASE expressions.

SELECT  DT_ID,
        CASE WHEN RN = 1 THEN InvNumber
                ELSE ''
        END InvNumber,
        CASE WHEN RN = 1 THEN InvAmount
                ELSE ''
        END InvAmount,
        JobNumber
FROM    (SELECT DT_ID,
                InvNumber,
                InvAmount,
                JobNumber,
                ROW_NUMBER() OVER (PARTITION BY InvNumber,InvAmount ORDER BY DT_ID) RN
            FROM   @TEST_DATA
        ) j

SQL: How to fill empty cells with previous row value?

Faiz,

how about the following query, it does what you want as far as I understand it. The comments explain each step. Take a look at CTEs on Books Online. This example could even be changed to use the new MERGE command for SQL 2008.

/* Test Data & Table */
DECLARE @Customers TABLE
    (Dates datetime,
     Customer integer,
     Value integer) 

    INSERT  INTO @Customers
    VALUES  ('20100101', 1, 12),
        ('20100101', 2, NULL),
        ('20100101', 3, 32),
        ('20100101', 4, 42),
        ('20100101', 5, 15),
        ('20100102', 1, NULL),
        ('20100102', 2, NULL),
        ('20100102', 3, 39),
        ('20100102', 4, NULL),
        ('20100102', 5, 16),
        ('20100103', 1, 13),
        ('20100103', 2, 24),
        ('20100103', 3, NULL),
        ('20100103', 4, NULL),
        ('20100103', 5, 21),
        ('20100104', 1, 14),
        ('20100104', 2, NULL),
        ('20100104', 3, NULL),
        ('20100104', 4, 65),
        ('20100104', 5, 23) ;

/* CustCTE - This gives us a RowNum to allow us to build the recursive CTE CleanCust */
WITH    CustCTE
          AS (SELECT    Customer,
                        Value,
                        Dates,
                        ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Dates) RowNum
              FROM      @Customers),

/* CleanCust - A recursive CTE. This runs down the list of values for each customer, checking the Value column, if it is null it gets the previous non NULL value.*/
        CleanCust
          AS (SELECT    Customer,
                        ISNULL(Value, 0) Value, /* Ensure we start with no NULL values for each customer */
                        Dates,
                        RowNum
              FROM      CustCte cur
              WHERE     RowNum = 1
              UNION ALL
              SELECT    Curr.Customer,
                        ISNULL(Curr.Value, prev.Value) Value,
                        Curr.Dates,
                        Curr.RowNum
              FROM      CustCte curr
              INNER JOIN CleanCust prev ON curr.Customer = prev.Customer
                                           AND curr.RowNum = prev.RowNum + 1)

/* Update the base table using the result set from the recursive CTE */
    UPDATE trg
    SET Value = src.Value
    FROM    @Customers trg
    INNER JOIN CleanCust src ON trg.Customer = src.Customer
                                AND trg.Dates = src.Dates

/* Display the results */
SELECT * FROM @Customers

SQL Server : LAG multiple Nulls

This gives you the output you want with the data you give. In your sample data there is only one "group" in each "class", but it looks like there could maybe be multiple groups per class? If that is the case, it will be a bit more complicated, but the principal will be the same.

CREATE TABLE #data (ID INT, Class VARCHAR(50), [Group] VARCHAR(50), Total INT, Account VARCHAR(50));
INSERT INTO #data(ID, Class, [Group], Total, Account) VALUES
(1, null, 'INCOME', null, 'Fencing'),
(2, null, null, null, 'Crop'),
(3, 'Net Income', null, null, 'Net Income'),
(4, null, 'Farm Expenditure', null, 'Irrigation'),
(5, null, null, null, 'electricity'),
(6, 'Surplus', null, null, 'Surplus'),
(7, null, 'GST', null, 'GST'),
(8, 'Closing Balance', null, null, 'Closing Balance');

-- Find the break points that signify the end of a Class
WITH breaks as(
    SELECT IIF(Class IS NOT NULL, 1, 0) AS breakpoint, ID, Class, [Group], Total, Account 
    FROM #data
),
-- count the breakpoints passed so each group will have a number we can group by
grp AS (
SELECT ISNULL(SUM(breakpoint) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) AS grp, 
    ID,Class,[Group],Total,Account
FROM breaks
)
SELECT MAX(grp.Class) OVER (PARTITION BY grp.grp) AS Class,
    MAX(grp.[Group]) OVER (PARTITION BY grp.grp) AS [Group],
    grp.Total,
    grp.Account
FROM grp

How to select records without duplicate on just one field in SQL?

Try this:

SELECT MIN(id) AS id, title
FROM tbl_countries
GROUP BY title

How to split a comma-separated value to columns

CREATE FUNCTION [dbo].[fn_split_string_to_column] (
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
    )
RETURNS @out_put TABLE (
    [column_id] INT IDENTITY(1, 1) NOT NULL,
    [value] NVARCHAR(MAX)
    )
AS
BEGIN
    DECLARE @value NVARCHAR(MAX),
        @pos INT = 0,
        @len INT = 0

    SET @string = CASE 
            WHEN RIGHT(@string, 1) != @delimiter
                THEN @string + @delimiter
            ELSE @string
            END

    WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
    BEGIN
        SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
        SET @value = SUBSTRING(@string, @pos, @len)

        INSERT INTO @out_put ([value])
        SELECT LTRIM(RTRIM(@value)) AS [column]

        SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
    END

    RETURN
END

How do I efficiently select the previous non-null value?

I found this answer for SQL Server that also works in Postgres. Having never done it before, I thought the technique was quite clever. Basically, he creates a custom partition for the windowing function by using a case statement inside of a nested query that increments a sum when the value is not null and leaves it alone otherwise. This allows one to delineate every null section with the same number as the previous non-null value. Here's the query:

SELECT
  id, value, value_partition, first_value(value) over (partition by value_partition order by id)
FROM (
  SELECT
    id,
    value,
    sum(case when value is null then 0 else 1 end) over (order by id) as value_partition

  FROM p
  ORDER BY id ASC
) as q

And the results:

 id | value | value_partition | first_value
----+-------+-----------------+-------------
  1 |   100 |               1 |         100
  2 |       |               1 |         100
  3 |       |               1 |         100
  4 |       |               1 |         100
  5 |       |               1 |         100
  6 |       |               1 |         100
  7 |       |               1 |         100
  8 |   200 |               2 |         200
  9 |       |               2 |         200
(9 rows)

---

Related Topics