Hi, I've a SQL query which gives output in below format. Name Region Date WORD. UK. 30-SEP-15 WORD. US. 29-SEP-15 WORD. SG. 30-SEP-15 I would like to modify the SQL query in such a way that it produces result in below format. May I know the modified SQL query? Name Region Date UK. 30-SEP-15 WORD. US. 29-SEP-15 SG. 30-SEP-15
If you only want to display the Name field when the Region is US, then something like the below would work; otherwise, you're probably best off doing it in the presentation layer. SELECT CASE Region WHEN 'US' THEN Name ELSE '' END AS Name, Region, Date FROM... --edit-- Seriously, do this in the presentation layer, not at the database layer... ...but, if you must, this seems to do the job: DECLARE @word TABLE ( Name VARCHAR(50) , Region CHAR(3) , dt DATETIME ); INSERT INTO @word SELECT 'Word' , 'UK' , '2015 - 09 - 30' UNION ALL SELECT 'Word' , 'US' , '2015 - 09 - 29' UNION ALL SELECT 'Word' , 'SG' , '2015 - 09 - 28' ; WITH cte AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY Name ORDER BY Name ) AS RN , COUNT(1) OVER ( PARTITION BY Name ORDER BY Name ) AS CN , * FROM @word ) SELECT CASE WHEN RN = ( 1 + CN ) / 2 THEN Name ELSE '' END AS Name , Region, dt FROM cte; But note that, if you have multiple different values in the "Name" field, how are you going to know where to put any distinguishing line breaks?