0

I have a task.

Basically I am selecting from table.

I want to outer apply (which means execute some code for each row) based on dynamic number coming form different table.

In Other words, I want to have dynamic number of outer apply.

Example : 

SELECT  C.ClientName, CS.Cnt
FROM Client C


OUTER APPLY
(
   SELECT  count(*) AS Cnt  from ClientStatementDetail 
   where A= 1 AND B = 2
  AND ClientdId = C.CLientID
) AS CS

-- I want to add another Oute Apply with diffrent condition.

OUTER APPLY
(
   SELECT  count(*) AS Cnt2  from ClientStatementDetail 
   where A= 3 AND B = 9
  AND ClientdId = C.CLientID
) AS CS2

-- Now my problem that I do not how many combination Of A and B I have. 
-- They are coming form diffrent table

Onthing came to my mind is to create a table value function that will return for me a table for all the rows

for that client then select the result. However in order to do so, we have to crate structure table with dayanmic columns.

In Other words, I was trying to implement this :

Dynamically add 50 columns to table variable

But structure tables can not be accessed in table function.

Any Help is appericated.

user123456
  • 2,524
  • 7
  • 30
  • 57
  • Is there any reason at all where creating a table-valued function would work better for you than a few queries? In any case, the only way I can think to return dynamic columns without dynamic sql is to create all columns and then selectively drop them with IF (and maybe a loop) – George Menoutis Jul 12 '18 at 09:07
  • Are you expecting a separate count for each OUTER APPLY or the sum of all of them? In your example you have both OUTER APPLYs aliased as CS, wasn't sure if that was intentional. – George Dando Jul 12 '18 at 09:11
  • @GeorgeDando I am expecting different one. – user123456 Jul 12 '18 at 09:12
  • Any particular query will always produce a result set with a fixed "shape" - the number of columns, their names and their data types. You *cannot* write a query with a variable number of columns. Dynamic SQL is a way of *writing new queries* which is, of course, how they can achieve various different outputs (because you construct different queries), but as noted, you *cannot* use dynamic SQL in a table-valued function. – Damien_The_Unbeliever Jul 12 '18 at 09:14
  • You'll probably need to end up with an output containing just 3 columns - `A`, `B` and `Cnt` and then do any pivotting of that data (so moving each count into a separate column) outside of SQL, in whatever report builder/application is consuming your result set. – Damien_The_Unbeliever Jul 12 '18 at 09:30
  • @Damien_The_Unbeliever I am using simple console application in .Net – user123456 Jul 12 '18 at 09:32
  • Yes, and you can write C# code that can pivot a result and produce an unbounded number of "columns". You cannot do that in T-SQL. – Damien_The_Unbeliever Jul 12 '18 at 09:33
  • I was thinking so, however I wanted to do all the work in the database layer and leave the service layer thin. – user123456 Jul 12 '18 at 09:35

0 Answers0