March 13, 2012 at 12:10 pm
Looking for help on the following query.
Create table tmpUserDetails
(
Userid int,
LocationIDint,
Activityvarchar(200)
)
insert into tmpUserDetails values (1,1,'Test')
insert into tmpUserDetails values (1,1,'Test1')
insert into tmpUserDetails values (2,1,'Loc1')
insert into tmpUserDetails values (1,2,'Test2')
insert into tmpUserDetails values (2,1,'Loc1A')
If there are multiple entries for same userID and LocationID, the activity
should be displayed as 'Multiple', and only one record needs to be displayed.
Desired output is
UseriD | LocationID | Activity
1,1, Multiple
1,2, Test2
2,1, Multiple
March 13, 2012 at 12:19 pm
Nice work posting ddl and sample data plus desired output. That is a skill that all too often get overlooked around here. 😀
Something like this work?
select u.Userid, u.Locationid, case when ActivityCount > 1 then 'Multiple' else Activity end as Activity
from
(
select Userid, LocationID, COUNT(*) as ActivityCount
from tmpUserDetails
group by Userid, LocationID
) x
join tmpUserDetails u on u.Userid = x.Userid and u.LocationID = x.LocationID
group by u.Userid, u.Locationid, case when ActivityCount > 1 then 'Multiple' else Activity end
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2012 at 12:26 pm
I echo Sean. Very nice set-up. Thanks for that!
How about this?
SELECT TUD.Userid , TUD.LocationID
, Ct = CASE WHEN COUNT(*) > 1 THEN 'Multiple'
ELSE MAX(Activity)
END
FROM tmpUserDetails TUD
GROUP BY TUD.Userid , TUD.LocationID
March 13, 2012 at 12:36 pm
Well sure Ron that would work if you want a query that will perform better on larger datasets and is a lot easier to read than the one I came up with. 😀 Nice!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2012 at 1:04 pm
Both solutions worked very well.
Would like to add additional criteria.
Create table tmpUserDetails
(
Userid int,
LocationIDint,
Activityvarchar(200),
OtherDatavarchar(200),
RowDatavarchar(200)
)
truncate table tmpUserDetails
insert into tmpUserDetails values (1,1,'Test','OD1','Row1-1')
insert into tmpUserDetails values (1,1,'Test1','OD1A','Row1-2')
insert into tmpUserDetails values (2,1,'Loc1','OD2','Row 2-1')
insert into tmpUserDetails values (1,2,'Test2','OD1B','Row 1-3')
insert into tmpUserDetails values (2,1,'Loc1A','OD2A','Row 2-2')
The desired result is
Desired output is
UseriD | LocationID | Activity | OtherData | RowData
1,1, Multiple, OD1, Row 1-1
1,2, Test2, OD1B, Row 1-3
2,1, Multiple, OD2, Row 2-1
The top row column value of other data column needs to displayed.
March 13, 2012 at 1:13 pm
I understand what you are saying but honestly that doesn't make sense from a usability point. From your sample data you have lost this row.
insert into tmpUserDetails values (1,1,'Test1','OD1A','Row1-2')
It can be done but you have to have something to order by.
Before I begin working on this....are there are criteria you are going to bring up after a solution is provided? It is best to describe the entire problem up front instead of piece meal.
Given the new criteria you should be able to tweak my first example pretty easily to get the top 1 of each group in the subquery and pull the additional data in the outer query.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2012 at 1:24 pm
Sean Lange (3/13/2012)
Well sure Ron
I'm not Ron 😉
March 13, 2012 at 1:25 pm
My Actual tables has more than 50 columns, of which only 3 columns needs to be distinct, on any row, and has many data rows. Tried to simulate the problem in the example tables provided earlier.
As for the lost row, the result should display the Top row of the user-location ID combination record,
when they have multiple records on same location ID.
March 13, 2012 at 1:26 pm
ColdCoffee (3/13/2012)
Sean Lange (3/13/2012)
Well sure RonI'm not Ron 😉
DOH!!! I am on a roll today. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2012 at 1:27 pm
satishchandra (3/13/2012)
My Actual tables has more than 50 columns, of which only 3 columns needs to be distinct, on any row, and has many data rows. Tried to simulate the problem in the example tables provided earlier.As for the lost row, the result should display the Top row of the user-location ID combination record,
when they have multiple records on same location ID.
What is the top row? You have to have something to order by or there is no "top row". Without an order by you will not always get the same result.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2012 at 1:36 pm
How about using row_number() over (order by Userid) as rownum, and do an order by on this Row Num.
March 13, 2012 at 1:42 pm
Again i echo SEan, without a proper way of defining the order, it is impossible to determine the "first row of every set".
But in the meantime, you can munch on the below code
;WITH CTE AS
(
SELECT TUD.Userid , TUD.LocationID , TUD.OtherData , TUD.RowData
, Activity = CASE WHEN COUNT(*) OVER (PARTITION BY TUD.Userid , TUD.LocationID ) > 1 THEN 'Multiple'
ELSE TUD.Activity
END
-- Key to get the top row is ORDER BY TUD.RowData; use that to control what row it shows
, RN = ROW_NUMBER() OVER (PARTITION BY TUD.Userid , TUD.LocationID ORDER BY TUD.RowData )
FROM tmpUserDetails TUD
)
SELECT *
FROM CTE
WHERE RN = 1
March 13, 2012 at 2:55 pm
Thank you very much. The script worked pretty well, on my actual tables with minor tweaking.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply