SQL - For Each loop in a query

Question

I'm not sure what the best title of my question would be, but I'll explain my problem/question the best below:

I'm using the following query, where I filter on a workorder, and I gather all the history of it, which I would need to shown on my report:

select a.name, d.data, h.started, e.name, * from wshhistory h
join asset a
on a.uid = h.assetid 
join wshfld d
on h.uid = d.wshhistid
join fielddefinition f
on d.flddefid = f.uid
join enumlookup e
on h.assetstatus = e.uid
where h.shdid = '43FEB092-D3B1-4008-9C44-A3A249987849' order by a.name, h.started asc

My result is fine, but...

What I would need is only 1 row (the last/top one) of each (unique) asset, and the other rows all as column, so that we only have 1 row for each asset. So that my report looks like:

In vb.net I would fix this with a for each loop going through all the assets, but with SQL my knowledge is limited, and I have no idea on how to solve this in a query.

Any idea?

Thank in advance!

Possible duplicate of [Select first row in each GROUP BY group?](http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — David Rushton, Apr 26 '17 at 13:06
If I would group by name (or asset id), then I get the message: Column 'asset.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. If I would remove all those selection criteria, and go with * then I still get the same error message, but with another column ('wshhistory.uid') — GertDeWilde, Apr 26 '17 at 13:09
If you are grouping you need to group by all columns that are not aggregated. And you should avoid using select * anyway, name the columns explicitly. — Sean Lange, Apr 26 '17 at 13:27

score 2 · Accepted Answer · answered Apr 26 '17 at 13:33

2

To produce the output you stated you want you can use conditional aggregation. Something like this.

select a.name
    , min(case when d.data = 'Pre Use' then d.data end) as PreUse
    , min(case when d.data = 'Connected' then d.data end) as Connected
    , min(case when d.data = 'Let Go' then d.data end) as LetGo
    , min(case when d.data = 'Disconnected' then d.data end) as Disconnected
from wshhistory h
join asset a on a.uid = h.assetid 
join wshfld d on h.uid = d.wshhistid
join fielddefinition f on d.flddefid = f.uid
join enumlookup e on h.assetstatus = e.uid
where h.shdid = '43FEB092-D3B1-4008-9C44-A3A249987849' 
group by a.name
order by a.name

answered Apr 26 '17 at 13:33

Sean Lange

33,028
3
25
40

This works for his mock up to the results he wants, but it doesn't work with the select all (*) in his query. I like using windows functions for this (i.e. row_number), so you can bring back all the columns for the first record. – KeithL Apr 26 '17 at 13:40
@KeithL I agree but in the desired results it is pretty clear they don't need every column. – Sean Lange Apr 26 '17 at 13:43
The * was for some extra info, but not for what I wanted on the report. So this is indeed what I want/needed. Sorry for the confusion. Thanks a bunch, you don't want to know how much time I waisted on this, and how much time you've just saved me! :) – GertDeWilde Apr 26 '17 at 13:44

score 0 · Answer 2 · answered Apr 26 '17 at 14:52

Try a pivot:

WITH A AS(
SELECT a.name , d.data , h.started 
FROM wshhistory h
    join asset a
    on a.uid = h.assetid 
    join wshfld d
    on h.uid = d.wshhistid
    join fielddefinition f
    on d.flddefid = f.uid
    join enumlookup e
    on h.assetstatus = e.uid

WHERE h.shdid = '43FEB092-D3B1-4008-9C44-A3A249987849' 
)
SELECT * FROM a

PIVOT(MAX(startdate) FOR data IN ([Pre Use],[Connected],[Let Go],[Disconnected])) AS MaxStarted

Since you haven't created some data for us I can't test it out but it should probably work :)

SQL - For Each loop in a query

2 Answers2