I am trying to export table data to excel format.
My present view's select query give result like this ..
IDs Column_A Column_B Column_C Column_D Column_E 1 EDC RFV TGB UJM 14789 2 EDC RFV TGB UJM 22225 But I want to get result like this...
IDs Column_A Column_B Column_C Column_D Column_E 1 EDC RFV TGB UJM 14789 2 22225 So how to achieve this ?
Don't do this in SQL. In fact, I don't even think it is a good idea in Excel. By removing the values, you lose the ability to do things like filter on the column values or create pivot tables.
You can do this in SQL. One simple method uses lag():
select . . ., (case when lag(column_a) over (order by id) = column_a then NULL else column_a end) as column_a, . . . But just because you can do it doesn't mean that you should.
Here's a method to use for SQL 2008, using ROW_NUMBER() with some partitions instead of LAG. Note that the LAG solution should be more efficient (and readable) if you have access to 2012+.
DECLARE @tbl TABLE (IDs int,Column_A char(3), Column_B char(3), Column_C char(3),Column_D char(3),Column_E int) insert @tbl VALUES (1,'EDC','RFV','TGB','UJM',14789) ,(2,'EDC','RFV','TGB','UJM',22225) ,(3,'EDC','RFV','TGB','UJM',22222) ,(4,'ECD','RFV','TGB','UJM',22222) select IDs ,(case when ROW_NUMBER() over (PARTITION BY Column_A order by ids) <> 1 then NULL else column_a end) as column_a ,(case when ROW_NUMBER() over (PARTITION BY Column_B order by ids) <> 1 then NULL else column_B end) as column_b ,(case when ROW_NUMBER() over (PARTITION BY Column_C order by ids) <> 1 then NULL else column_c end) as column_c ,(case when ROW_NUMBER() over (PARTITION BY Column_D order by ids) <> 1 then NULL else column_D end) as column_D ,Column_E FROM @tbl Input:
1 EDC RFV TGB UJM 14789 2 EDC RFV TGB UJM 22225 3 EDC RFV TGB UJM 22222 4 ECD RFV TGB UJM 22222 Output:
1 EDC RFV TGB UJM 14789 2 NULL NULL NULL NULL 22225 3 NULL NULL NULL NULL 22222 4 ECD NULL NULL NULL 22222 But again, this is likely to cause more problems than it solves. I would consider whether it would make more sense to pivot/unpivot or otherwise rearrange the data, perhaps to effectively show a list of IDs for each of Column_A/B/C/D.