asked
Select SQL Query
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
Can you give us the SQL you are using for the original select please.
SQL query used: Select * from table where Name='word.' and Region!='Tokyo' and Date >= sysdate -3;
Is it that you want "Word" to be there only for US region, or is there some other requirement?
Hi Thomas, Thanks for the response. My requirement is not to have "word" only for US region. The data in the first column will always be "word". So I want that to be merged (like we do in excel: Merge and centre)
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?
21 People are following this question.
