none
Get distinct Users from one table and loop through to get the count from other table. RRS feed

  • Question

  • HI,

    I need to get the distinct users from one table and loop through the other table to get the count with each distinct user. Here is my final result of the SP should look like:

    Select distinct users from TableA

    Result:
    Users
    1 (let's say User1)
    2 (let's say User2)
    3 (let's say User3)
    4 (let's say User4)

    Foreach User, I need to loop through the below statement to get the count 

    Select @Users as 'Users', (Select count(*) from TableA where dev like '%abc%' and userid = @Users) as 'Wearable', () as 'Gss', (Select count(*) from TableB where mdl like '%def%' and emp_id = @Users) as 'both match'

    Final Result for the SP should look like:
    Users  Wearable  Gss   both match
    1          45657     123       435
    2            768       456       891
    3           6458     1456      6891
    4           9768     2456      3891

    I would appreciate any help.


    Friday, March 29, 2019 8:46 PM

Answers

All replies

  • HI,

    I need to get the distinct users from one table and loop through the other table to get the count with each distinct user. Here is my final result of the SP should look like:

    Select distinct users from TableA

    Result:
    Users
    1 (let's say User1)
    2 (let's say User2)
    3 (let's say User3)
    4 (let's say User4)

    Foreach User, I need to loop through the below statement to get the count 

    Select @Users as 'Users', (Select count(*) from TableA where dev like '%abc%' and userid = @Users) as 'Wearable', () as 'Gss', (Select count(*) from TableB where mdl like '%def%' and emp_id = @Users) as 'both match'

    Final Result for the SP should look like:
    Users  Wearable  Gss   both match
    1          45657     123       435
    2            768       456       891
    3           6458     1456      6891
    4           9768     2456      3891

    I would appreciate any help.

    Good day varsha,

    >> and loop through the other table

    SQL Server like other tabular database is designed to work on SET of data and not looping row-by-row. It had smart and complex algorithms to work with entire SET of data together.  It can (if must) use loops but in most case this is a very bad idea.

    >> Please provide:

    1) Queries to CREATE your table(s) including indexes
    2) Queries  to INSERT sample data.
    3) The desired result given the sample, as text or image of excel for example.
    4) A short description of the business rules, and how you got 1-2 of the results
    5) Which version of SQL Server you are using (this will help to fit the query to your version).


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Saturday, March 30, 2019 4:50 AM
    Moderator
  • Hi,

    Thank you for responding back. I actually tried the below query in my local database which is working fine:

    DECALRE @abc varchar(max)
    Set @abc = (SELECT DISTINCT STUFF((SELECT  ',' + EMP_ID FROM [Test_SPSF].[dbo].[Employees] FOR XML PATH ('')), 1, 1, '') FROM [Test_SPSF].[dbo].[Employees])
    Print @abc  ---- printing as 2345, 2345, 4567, 45657

    But if I use the same in production sql server, I am not  returning the same result, can you help me.

    Thanks,

    Varsha

    Monday, April 1, 2019 7:35 PM
  • Thank you everyone, I found the issue.
    • Marked as answer by sha_15 Friday, July 19, 2019 6:04 PM
    Friday, July 19, 2019 6:04 PM