0

I have a table with data like this

[![enter image description here][1]][1]

I have tried this but I only get a count of client id in this SQL statement 

from temp.npidoctorregistry 
where InformationSourceId = 3
and isactive = 1
user1098924
  • 79
  • 7
  • 1
    Please tag your dbms – HoneyBadger Aug 16 '19 at 20:39
  • 1
    [This will help](https://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) – S3S Aug 16 '19 at 20:47
  • I followed the above example but i still cant return the client id in the same row. – user1098924 Aug 16 '19 at 21:20
  • I tried the above example but it groups by an id column which works but i dont have an id column to group by. the group by should be lastname + first Name – user1098924 Aug 16 '19 at 21:25
  • Read what you wrote and think. You said (paraphrasing) " group by names, npi, client id> and then said you expect a single row for rows 1 and 3 for a CSV value containing the client IDs. That doesn't match - you can't group by client ID but expect to aggregate/concatenate them into a single value. And no - your last query does not give a count of client ID. And this is exactly the same issue as a [previous question of yours](https://stackoverflow.com/questions/57082651/group-by-name-to-return-all-phone-numbers) – SMor Aug 16 '19 at 22:06
  • Please show some example expected data. – Dale K Aug 17 '19 at 05:05

1 Answers1

1

Maybe I'm not understanding the question correctly, but it sounds like you might need to remove the clientID from your GROUP BY clause. If you're doing this in a stored procedure you could iterate over the results of your initial query and add another to get the clientIDs and CONCAT them together.

Wilco
  • 374
  • 1
  • 11
  • could you please give me an example on how to do this? – user1098924 Aug 16 '19 at 21:01
  • Group by will group them for those that have the same values, so you must deleted the column that has an identifier different than the others. For the procedure, search for Fetch (Transact-SQL) and learn about cursor and fetch. Microsoft has a lot of documentation about the subject – luturol Aug 16 '19 at 21:08