October 4, 2018 at 3:13 pm
hey guys, I have looked pretty much everywhere for a solution to this problem, and have finally come here in hopes that someone who has tackled this problem before may be able to assist. I have a set of data that I would like to re-format. I know I can do this in excel, but for other reasons, I'd like to use SQL exclusively to do this. The end goal here is to null repeat values in the 'Unit' field so that the number is only displayed once. For Example, I want to be able to see only one 0918 and the rest are simply blank. Is this possible? thanks ahead of time for any assistance with this 🙂
October 4, 2018 at 9:20 pm
So what you are looking is to show exactly 1 record with a not null value for AccountingCode keeping all other fields intact.
You can try the following
select case when row_number() over(partition by x.unit order by x.unit)=1 then x.unit end as unit
,x.quantity,x.UOM,x.[Part Number],x.Description
from (
<insert_your_query remove the order by>
)x
order by 1
October 5, 2018 at 6:34 am
george_at_sql - Thursday, October 4, 2018 9:20 PMSo what you are looking is to show exactly 1 record with a not null value for AccountingCode keeping all other fields intact.
You can try the following
select case when row_number() over(partition by x.unit order by x.unit)=1 then x.unit end as unit
,x.quantity,x.UOM,x.[Part Number],x.Description
from (
<insert_your_query remove the order by>
)x
order by 1
Excellent! This worked perfectly! The only change that I needed to make was the 'order by 1' to 'order by x.Unit, x.Quantity' and the result set lined up properly. Thank you very much George!
October 5, 2018 at 7:39 am
One could also not mess with the query and instead use a reporting tool where you can have it not repeat values For this data, that would be a piece of cake in SSRS.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 5, 2018 at 7:42 am
sgmunson - Friday, October 5, 2018 7:39 AMOne could also not mess with the query and instead use a reporting tool where you can have it not repeat values For this data, that would be a piece of cake in SSRS.
Again, the OP knows this, even mentioned it in the original post. He wants to accomplish it in the SQL code.
October 5, 2018 at 7:47 am
Lynn Pettis - Friday, October 5, 2018 7:42 AMsgmunson - Friday, October 5, 2018 7:39 AMOne could also not mess with the query and instead use a reporting tool where you can have it not repeat values For this data, that would be a piece of cake in SSRS.Again, the OP knows this, even mentioned it in the original post. He wants to accomplish it in the SQL code.
Excel was mentioned, not a reporting tool. Excel is often used that way, and it's not all that good at it. Thus I don't really see it as a reporting tool. And as reporting tool was not mentioned, and although he said he looked everywhere, I can't imagine someone having missed that idea and then being able to make such a claim. So I figured that bringing that up was well within common sense.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply