1

I dont know is this is possible at all, I´m not even sure how to google it.

Im using SQL Server 2014.

Right now I have a SP that outputs a table with data including MovementID, Vehicle, VehicleType and Total of sales. But it groups all the vehicles ids and vehicles types with a total.

I need to separate vehicles, which I can. The problem is that the totals appear duplicated, or triplicated etc.

So, how can I only select that column only one time per ID (MovementID in the example).

I'll keep trying, of course, but any idea would be appreciated.

[Example (Much more clear by looking at it)

Alfabravo
  • 7,493
  • 6
  • 46
  • 82
FcoLG
  • 52
  • 1
  • 9
  • 3
    We'd like to see the code you're using. Text is heavily preferred to images when posting input/output/desired output. – avery_larry Sep 25 '19 at 19:17
  • That's a front end solution, not a SQL Server solution. It's possible to get what you request, but it's definitively not recommended. – Luis Cazares Sep 25 '19 at 19:19
  • 1
    The biggest issue you are facing is because you don't have properly normalized data. You have delimited lists in your columns. This violates 1NF and causes incredible amounts of anguish. – Sean Lange Sep 25 '19 at 19:41
  • Sean Lange - This is the database of a System in production, so I can't really do much about it. I have to work around what already exists. – FcoLG Sep 25 '19 at 19:51
  • Why do you assume that F goes with V01 and T goes with V02? I would assume that the order of values in VehicleTypes are actually sorted independently of the VehicleID. More to the point, you have a stored procedure that already produces the information you need - so create a new one using that logic and formatting the results the way you want. – SMor Sep 25 '19 at 20:36
  • @ThorstenKettner The ui would stop working and I don't have control over it (we bought the system). I can only read the database, well I should only read... otherwise it would have a lot of errors. – FcoLG Sep 25 '19 at 20:40

3 Answers3

0

You can PARTITION the records by MovementID and then generate a ROW_NUMBER for each row in the partition. Then, you can use IIF to only display the total if the row number is 1, i.e. the row is the first row in the partition. It should look something like this:

SELECT MovementID, VehicleID, VehicleType, IIF(rowno = 1, Total, NULL)
FROM
(
    SELECT MovementID, VehicleID, VehicleType, Total, 
     ROW_NUMBER() OVER (PARTITION BY MovementID ORDER BY MovementID) AS rowno
    FROM <WHAT_I_HAVE_NOW>
) tmp
Hogan
  • 69,564
  • 10
  • 76
  • 117
Rob Streeting
  • 1,675
  • 3
  • 16
  • 27
  • How would this impact the performance? Thank you for answering! – FcoLG Sep 25 '19 at 19:43
  • I wouldn't expect a very noticeable effect on performance since the partitioning is happening on MovementID, which I presume is the primary key on the table? If so it will be indexed and so the partitioning can be done without having to painstakingly scan the rows. – Rob Streeting Sep 25 '19 at 19:50
0

You can use LAG to see values of the previous row.

with q as (your query here)
select 
  movementid, vehicleid, vecicletype, 
  case when movementid = lag(movementid) over (order by movementid, vehicleid)
    then null else total
  end as total
from q
order by movementid, vehicleid;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Well, this works!, so far. Sorry to ask the same, but how much impact does this has in performance? Thank you, very much, I'll try all the solutions so I can use the best one based on performace, but yeah, this works – FcoLG Sep 25 '19 at 20:35
  • Well, it takes the time it needs. It seems kind of weird to ask about performance impact when talking about such an awful database. I don't think scanning through result rows as `LAG` does will be very costly. However, just measure it. Run your query several times, run mine several times, look at the run times. After all, I don't really recommend using my query. This is something that should rather be done in the GUI layer. There the selected data will be read in a loop in order to fill a grid. It would be easy to detect the same movementid in that loop and not fill the total cell in case. – Thorsten Kettner Sep 26 '19 at 07:26
  • Everybody seems to assume I have control over the database structure, that I can change the GUI, that they can find a way to do it somewhere else. I don't. I can't do any of that. That's why I asked the question this way in the first place. Thanks, the performance wasn't noticeable affected, so it works. – FcoLG Sep 27 '19 at 20:52
  • Yes, usually one has control over either the database or the GUI or both. Hence, we are trying to give the best advice, which is not to struggle with the query, but do things where they should be done. Your case where the database and GUI are third-party and you are changing a stored procedure that is again used by the third-party GUI is a very special one. In Oracle one could replace the original tables with views and use instead-of triggers to write normalized tables, but I don't think that SQL Servers features this. So, the query is already the best solution here. Glad, it works for you. – Thorsten Kettner Sep 28 '19 at 08:26
0

You can split and join as below:

;with cte_vids as (
    select * from #tblmove
    cross apply udf_split( vehicleids, ',') 
), cte_vtypes as (
    select * from #tblmove
    cross apply udf_split( vehicletypes, ',')
)
select cid.movementid, cid.[value] as vehichleid, cty.[value] as vehicletype, 
    case when cid.rown = 1 then cid.total else null end as total 
from cte_vids cid join cte_vtypes cty
on cid.movementid = cty.movementid and cid.vehicleids = cty.vehicleids and cid.rown = cty.rown

Answers as below:

+------------+------------+-------------+-------+
| movementid | vehichleid | vehicletype | total |
+------------+------------+-------------+-------+
|          1 | V01        | F           | 200   |
|          1 | V02        | T           | NULL  |
|          2 | V04        | V           | 140   |
|          3 | V03        | F           | 300   |
|          3 | V02        | F           | NULL  |
+------------+------------+-------------+-------+

I used a function which you can create as below:

CREATE Function dbo.udf_split( @str varchar(max), @delimiter as varchar(5) )
RETURNS @retTable Table 
( RowN int,
value varchar(max)
)
AS
BEGIN
DECLARE @xml as xml
    SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
    INSERT INTO @retTable   
    SELECT RowN = Row_Number() over (order by (SELECT NULL)), N.value('.', 'varchar(MAX)') as value FROM @xml.nodes('X') as T(N)
RETURN
END
Kannan Kandasamy
  • 13,405
  • 3
  • 25
  • 38