How to avoid repeating same column value every rows

Question

How to avoid repeating same column value every rows

born2achieve

Hall of Fame

Points: 3182
More actions
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.

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

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

Nevyn SSChampion Points: 14210 More actions · Answer 1

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.

born2achieve Hall of Fame Points: 3182 More actions · Answer 2

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

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

born2achieve Hall of Fame Points: 3182 More actions · Answer 4

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!!!