Post reply

How to avoid repeating same column value every rows

  • December 17, 2013 at 8:17 am

    #302464

    Hi

    I am working in sqlserver 2008 r2. Below is my table structure

    Table Name : Details

    Client, company, division, active

    google, abc, finance, 1

    google, abc, management, 1

    google, def, HR,1

    my query is

    select Client, company, division, active from details where client = 'google'

    My desired output should be :

    google, abc, finance, 1

    abc, management, 1

    def, HR,1

    How to avoid the client name repeating in every rows.

    if possible please show me some sample query to achieve this,

    Thanks in advance.

  • December 17, 2013 at 8:39 am

    #1674104

    Does this have to be done at the query level? This is basically a formatting requirement and something you would usually do at the client side.

    There are ways to do it in the query if you need them, but they would be overcomplicating a very simple query just to get the formatting you want.

  • December 17, 2013 at 8:57 am

    #1674112

    it would be great if i have the query. can you show me the sample query please

  • December 17, 2013 at 9:11 am

    #1674122

    I do believe that doing it in the aplication layer is the best option. If you really need it on SQL Server, here's an option:

    WITH SampleData(Client, company, division, active) AS(

    SELECT 'google', 'abc', 'finance', 1 UNION ALL

    SELECT 'google', 'abc', 'management', 1 UNION ALL

    SELECT 'google', 'def', 'HR',1),

    Cubed AS(

    SELECT *,

    ROW_NUMBER() OVER( PARTITION BY Client ORDER BY company, division) rn

    FROM SampleData

    )

    SELECT CASE WHEN rn = 1 THEN Client ELSE '' END,

    company,

    division,

    active

    FROM Cubed

    ORDER BY rn

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • December 17, 2013 at 9:17 am

    #1674128

    Thanks a lot Louis, just now i did using the row_number function.

    select case when rn = 1 then Client else null end as Client

    ,company

    , division

    , active

    from (

    select Client

    , company

    , division

    , active

    , row_number() over (partition by client order by company, division, active) as rn

    from details

    where client = 'google'

    ) d

    Thanks for your time on this post and happy coding!!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply