Thanks for all your tips and tricks. The total count is 193787. Now I added another parameter Clientname search that works (please see picture below). However, I want client id...

Thank you for your quick response. The single parameter (client id search) is not a drop down option/filter due to it's huge size; 200K unique ids. Instead, staff already have...

I think I figured it out...testing it now.

Thank you,

Helal

Thank you. More specifically, where do I insert those statements. I tried this with no avail (my inserts are in Bold)

if object_id('tempdb.dbo.##Servicepayment') is not null drop table ##Servicepayment

DECLARE @serviceid int...

Hi Luis,

Now that with your help I got all my 50 columns on one row, I like to store the results into a temp table since this table would be...

Hi Luis,

now that I got my entire data columns in one row how do I store the result of this dynamic pivot into a temp table?

Thank You,

Helal

I figured this one out. There was another place (+ CHAR(9) + N'WHERE serviceid = @serviceid' + CHAR(10)) that I needed to comment out.

Thanks for all your help again.

Helal

oops...I have overlooked that one. Now, it runs with no error but I get no records at all!

Back again, I have commented and un-commented as you advised to get the full table and I get the following error. Here are the changes I made:

1: --DECLARE @serviceid int...

Hi Luic,

This worked like a charm. To be honest, the script is way over my head. However, with the two articles you referenced, I will study and try to redundant...

Let's try this link to Google Drive folder.

The desired outcome should like this and this is only for one serviceid=66

CREATE TABLE #T1 (

serviceid int NOT NULL,

ProgramId int NOT NULL,

Firstbilleddate1...

Not really...what I do need I believe it's called Dynamic Pivoting on multiple columns and rows. Please see attached image for the desired outcome.

Thank you,

Helal