I have the following query that sort of works, but I find it to be kludgy and inefficient.  Is there a better way to write this query?  I know this is a bit of an odd query, else I would probably have found some advice when searching, but it is necessary for our business logic.  We've been doing this in Hibernate queries and using software to output the file but we're needing to improve performance drastically and that means using raw sql.  The output of this query is to be used to partially populate a helper table that we query to figure out which of our 40-odd product tables a particular item is contained in.  Could the following query be replicated using something like GROUP_CONCAT in reverse? Note that the second and third column must be repeated on each line.  The fields all contain the same type of data.  SELECT 'Pn', 'Mfg', 'TableId', 'SourceColumn', 'OtherData' SELECT PN1, Mfg, '26', 'PN1', OtherData FROM Table  UNION ALL SELECT PN2, Mfg, '26', 'PN2', OtherData FROM Table UNION ALL SELECT PN3, Mfg, '26', 'PN3', OtherData FROM Table UNION ALL SELECT PN4, Mfg, '26', 'PN4', OtherData  INTO OUTFILE 'XXX.dat' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' FROM Table d;   Input looks like the following table:       |  PN1  |  PN2  |  PN3    |  PN4    |  MFG  |  TableId  |  OtherData  |     -----------------------------------------------------------------------     |  asdf |  abde |  12354  |  asdfc  |  2    |  26       |  0.2456     |     |  nupo |  1354 |  null   |  null   |  2    |  26       |  1.53       |     |  ...  |  ...  |  ...    |  ...    |  ...  |  ...      |  ...        |     |  ...  |  ...  |  ...    |  ...    |  ...  |  ...      |  ...        |     |  ...  |  ...  |  ...    |  ...    |  ...  |  ...      |  ...        |   I want the output .dat file to look like this:       "Pn","Mfg","TableId","SourceColumn","OtherData"     "asdf",2,26,"PN1",0.2456     "abde",2,26,"PN2",0.2456     "12354",2,26,"PN3",0.2456     "asdfc",2,26,"PN4",0.2456     "nupo",2,26,"PN1",1.53     "1354",2,26,"PN2",1.53     ...     ...     ...   If I take the INTO OUTFILE clause out, I get an output that looks like this:       |  Pn      |  Mfg     |  TableId    |  SourceColumn  | OtherData  |     -----------------------------------------------------------------     |  "Pn"    |  {blob}  |  "TableId"  |  "PN"          |  {blob}    |     |  "asdf"  |  {blob}  |  "PN1"      |  "PN1"         |  {blob}    |     |  "abde"  |  {blob}  |  "PN2"      |  "PN2"         |  {blob}    |     |  "12354" |  {blob}  |  "PN3"      |  "PN3"         |  {blob}    |     |  "asdfc" |  {blob}  |  "PN4"      |  "PN4"         |  {blob}    |     |  "nupo"  |  {blob}  |  "PN1"      |  "PN1"         |  {blob}    |     |  "1354"  |  {blob}  |  "PN2"      |  "PN2"         |  {blob}    |     |  ...     |  ...     |  ...        |   ...          |   ...      |     |  ...     |  ...     |  ...        |   ...          |   ...      |     |  ...     |  ...     |  ...        |   ...          |   ...      |
48977
I have a table with several columns which I want to SELECT:  SELECT his_name , her_name, other_name FROM foo;   Bu, I instead I want to combine the results all into a single column. As an example, I can do this with UNION ALL as   SELECT her_name AS name FROM foo UNION ALL SELECT his_name AS name FROM foo UNION ALL SELECT other_name AS name FROM foo   Is there more elegant way to do this operation?
103055
Please look at this code:  create table #t1(   id int identity (1,1),   val varchar(10) );   insert into #t1 values ('a'); insert into #t1 values ('b'); insert into #t1 values ('c'); insert into #t1 values ('d');   Now, whenever you execute this  select *,      ( select top 1 val from #t1 order by NEWID()) rnd  from #t1 order by 1;   you will get a result with where all rows have the same random value. e.g.  id          val        rnd ----------- ---------- ---------- 1           a          b 2           b          b 3           c          b 4           d          b   I know a way using a cursor to loop throw the rows and get different random values, but that is not performant.   A clever solution to this is   select t1.id, t1.val, t2.val from #t1 t1     join (select *, ROW_NUMBER() over( order by NEWID()) lfd from #t1) as t2 on  t1.id = t2.lfd    But I simplified the query. The real query looks more like  select *,      ( select top 1 val from t2 where t2.x &lt;&gt; t1.y order by NEWID()) rnd  from t1 order by 1;   and the simple solution doesn't fit. I'm looking for a way to force repeated evaluation of  ( select top 1 val from #t1 order by NEWID()) rnd    without the use of cursors.  Edit: Wanted output:  perhaps 1 call  id          val        rnd ----------- ---------- ---------- 1           a          c 2           b          c 3           c          b 4           d          a   and a second call  id          val        rnd ----------- ---------- ---------- 1           a          a 2           b          d 3           c          d 4           d          b   The value for each row just should be a random value independent from the other rows  Here is the cursor version of the code:  CREATE TABLE #res ( id INT, val VARCHAR(10), rnd VARCHAR(10));  DECLARE @id INT DECLARE @val VARCHAR(10) DECLARE c CURSOR FOR SELECT id, val FROM #t1 OPEN c FETCH NEXT FROM c INTO @id, @val WHILE @@FETCH_STATUS = 0 BEGIN     INSERT INTO #res     SELECT @id, @val, ( SELECT TOP 1 val FROM #t1 ORDER BY NEWID()) rnd      FETCH NEXT FROM c INTO @id, @val END CLOSE c DEALLOCATE c  SELECT * FROM #res
71622
I need to do a join with a table/result-set/whatever that has the integers n to m inclusive. Is there a trivial way to get that without just building the table?  (BTW what would that type of construct be called, a "Meta query"?)  m-n is bounded to something reasonable ( &lt; 1000's)
66977
How can I get a PL/SQL block to output the results of a SELECT statement the same way as if I had done a plain SELECT?  For example how to do a SELECT like:  SELECT foo, bar FROM foobar;   Hint :  BEGIN SELECT foo, bar FROM foobar; END;   doesn't work.
9803
This is a canonical question and answer developed by the community to help address common questions. If you've been directed here, or your question has been closed as a duplicate, please look through the resources here and use them to shape more specific questions. To browse all canonical questions and answers, navigate to the  tag. This question and answer is owned by the community! If you can improve it and make it more useful, you're welcome to edit it. I have a collection of records, like a List&lt;sObject&gt; or the Trigger.new context variable. I need to query other records that are related to these records either directly or indirectly, and I want to use the related data to make further updates. When I write a query in a loop, like for (Account a : Trigger.new) {     Account parent = [SELECT Name FROM Account WHERE Id = :a.ParentId];     // ... }  I keep hitting Salesforce query limits, and I know this is not best practice. What can I do to avoid this problem and bulkify my code?
107229
Lately, I have been trying to figure out the most generic query for getting tracking data into one table. As suggested in the , I have been using this join:  ... FROM _Sent s LEFT JOIN _Job as j ON s.JobID = j.JobID  LEFT JOIN _Open o ON s.JobID = o.JobID and s.ListID = o.ListID and s.BatchID = o.BatchID and s.SubscriberID = o.SubscriberID and o.IsUnique = 1 LEFT JOIN _Click c ON s.JobID = c.JobID  and s.ListID = c.ListID and s.BatchID = c.BatchID and s.SubscriberID = c.SubscriberID and c.IsUnique = 1 LEFT JOIN _bounce b ON s.JobID = b.JobID and s.ListID = b.ListID and s.BatchID = b.BatchID and s.SubscriberID = b.SubscriberID and b.IsUnique = 1   My results show the same email send, with the same JobID, sent exactly at the same time, to the same subscriber, but with different batch numbers. On top of that, only the first record has an open date:    According to documentation, BatchID   is "The batch ID number for any batches used in the send" - but what does it mean, really? And how should the  BatchID be treated in queries?   Thanks!
53082
To illustrate, assume that I have two tables as follows:  VehicleID Name 1         Chuck 2         Larry  LocationID VehicleID City 1          1         New York 2          1         Seattle 3          1         Vancouver 4          2         Los Angeles 5          2         Houston   I want to write a query to return the following results:  VehicleID Name    Locations 1         Chuck   New York, Seattle, Vancouver 2         Larry   Los Angeles, Houston   I know that this can be done using server side cursors, ie:  DECLARE @VehicleID int DECLARE @VehicleName varchar(100) DECLARE @LocationCity varchar(100) DECLARE @Locations varchar(4000) DECLARE @Results TABLE (   VehicleID int   Name varchar(100)   Locations varchar(4000) )  DECLARE VehiclesCursor CURSOR FOR SELECT   [VehicleID] , [Name] FROM [Vehicles]  OPEN VehiclesCursor  FETCH NEXT FROM VehiclesCursor INTO   @VehicleID , @VehicleName WHILE @@FETCH_STATUS = 0 BEGIN    SET @Locations = ''    DECLARE LocationsCursor CURSOR FOR   SELECT     [City]   FROM [Locations]   WHERE [VehicleID] = @VehicleID    OPEN LocationsCursor    FETCH NEXT FROM LocationsCursor INTO     @LocationCity   WHILE @@FETCH_STATUS = 0   BEGIN     SET @Locations = @Locations + @LocationCity      FETCH NEXT FROM LocationsCursor INTO       @LocationCity   END   CLOSE LocationsCursor   DEALLOCATE LocationsCursor    INSERT INTO @Results (VehicleID, Name, Locations) SELECT @VehicleID, @Name, @Locations  END      CLOSE VehiclesCursor DEALLOCATE VehiclesCursor  SELECT * FROM @Results   However, as you can see, this requires a great deal of code.  What I would like is a generic function that would allow me to do something like this:  SELECT VehicleID      , Name      , JOIN(SELECT City FROM Locations WHERE VehicleID = Vehicles.VehicleID, ', ') AS Locations FROM Vehicles   Is this possible?  Or something similar?
43671
I have written below inner join query to get the data from my database tables. But  output of this data is coming up with almost 140+ lines. Output is below. I need data in only one row with the most recent value by "TimeSampled"    select DisplayName, ObjectName, CounterName, InstanceName, SampleValue, max(TimeSampled) as TimeSampled from PerformanceDataAllView pdv with (NOLOCK) inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId where ObjectName = 'Processor'  AND TimeSampled &gt;= DATEADD(MINUTE,-5,GETDATE()) AND DisplayName Like 'BLRW1ETST01.jcp.com' AND CounterName = '% Processor Time' GROUP BY DisplayName, ObjectName, CounterName, InstanceName, SampleValue ORDER BY  TimeSampled desc   How I can retrive results with only last Time Sampled row with respect to DisplayName. I have multiple servers (DisplayName) like this to retrieve the data.  Thanks, Ramu Chittiprolu
119219
Conceptual question: Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient?  TL;DR: If my joined query takes longer than running individual queries, is this my fault or is this to be expected?  First of, I am not very database savvy, so it may be just me, but I have noticed that when I have to get information from multiple tables, it is "often" faster to get this information via multiple queries on individual tables (maybe containing a simple inner join) and patch the data together on the client side that to try to write a (complex) joined query where I can get all the data in one query.  I have tried to put one extremely simple example together:    Schema Setup:  CREATE TABLE MASTER  ( ID INT NOT NULL , NAME VARCHAR2(42 CHAR) NOT NULL , CONSTRAINT PK_MASTER PRIMARY KEY (ID) );  CREATE TABLE DATA ( ID INT NOT NULL , MASTER_ID INT NOT NULL , VALUE NUMBER , CONSTRAINT PK_DATA PRIMARY KEY (ID) , CONSTRAINT FK_DATA_MASTER FOREIGN KEY (MASTER_ID) REFERENCES MASTER (ID) );  INSERT INTO MASTER values (1, 'One'); INSERT INTO MASTER values (2, 'Two'); INSERT INTO MASTER values (3, 'Three');  CREATE SEQUENCE SEQ_DATA_ID;  INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.3); INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.5); INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.7); INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 2, 2.3); INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 3, 3.14); INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 3, 3.7);   Query A:  select NAME from MASTER where ID = 1   :  | NAME | -------- |  One |   Query B:  select ID, VALUE from DATA where MASTER_ID = 1   :  | ID | VALUE | -------------- |  1 |   1.3 | |  2 |   1.5 | |  3 |   1.7 |   Query C:  select M.NAME, D.ID, D.VALUE  from MASTER M INNER JOIN DATA D ON M.ID=D.MASTER_ID where M.ID = 1   :  | NAME | ID | VALUE | --------------------- |  One |  1 |   1.3 | |  One |  2 |   1.5 | |  One |  3 |   1.7 |   Of course, I didn't measure any performance with these, but one may observe:   Query A+B returns the same amount of usable information as Query C. A+B has to return 1+2x3==7 "Data Cells" to the client C has to return 3x3==9 "Data Cells" to the client, because with the join I naturally include some redundancy in the result set.   Generalizing from this (as far fetched as it is):   A joined query always has to return more data than the individual queries that receive the same amount of information. Since the database has to cobble together the data, for large datasets one can assume that the database has to do more work on a single joined query than on the individual ones, since (at least) it has to return more data to the client.  Would it follow from this, that when I observe that splitting a client side query into multiple queries yield better performance, this is just the way to go, or would it rather mean that I messed up the joined query?
82651
I have an output of a query as follows:  bid  | code | amount        | ----------------------------- 2915 | BF   |   -10700.00   | 2915 | YQ   |   -300.00     | 2915 | YR   |   0.00        | 2915 | YM   |   0.00        | 2915 | WO   |   -153.00     | 2915 | IN   |   -329.00     | 2915 | K3   |   0.00        | 2915 | CUTE |   -50.00      | -----------------------------   I need to convert the values in code column to individual columns with column name as corresponding code and amount shall be the value.  bid  | BF       | YQ     | ... | CUTE  | ---------------------------------------- 2915 | -10700.00| -300.00| ... | -50.00|   The issue is that the result of my initial query has dynamic codes and hence need to generate columns accordingly.  Already tried the following logic but couldn't achieve the goal:   , ,   All the above links use some expression or the other to generate the columns.   My original resulting rows are result of simple select on multiple joined tables, and has no calculation or expression scope.  Kindly suggest some pointer or hint on how to achieve this.  Many thanks in advance.
27629
I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data:  ╔══════════╦══════════╗ ║ PersonID ║ OptionID ║ ╠══════════╬══════════╣ ║        1 ║ A        ║ ║        1 ║ B        ║ ║        2 ║ C        ║ ║        3 ║ B        ║ ║        4 ║ A        ║ ║        4 ║ C        ║ ╚══════════╩══════════╝   I need to pivot that data so the results are:  ╔══════════╦═════════╦══════════╦══════════╗ ║ PersonID ║ OptionA ║ Option B ║ Option C ║ ╠══════════╬═════════╬══════════╬══════════╣ ║        1 ║       1 ║        1 ║          ║ ║        2 ║         ║          ║        1 ║ ║        3 ║         ║        1 ║          ║ ║        4 ║       1 ║          ║        1 ║ ╚══════════╩═════════╩══════════╩══════════╝   Any suggestions?
33666
I am running a query like   select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d';   on table that looks like                                      Table "public.students"           Column       |            Type             |             Modifiers                   -------------------+-----------------------------+------------------------------------      id                | uuid                        | not null default gen_random_uuid()      school_id        | uuid                        |  Indexes:     "students_pkey" PRIMARY KEY, btree (id)     "students_school_id_idx" btree (school_id)   The query plan for the select statement with just where looks like below-  explain select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d';                                             QUERY PLAN                                             --------------------------------------------------------------------------------------------------  Bitmap Heap Scan on students  (cost=581.83..83357.10 rows=24954 width=16)    Recheck Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)    -&gt;  Bitmap Index Scan on students_school_id_idx  (cost=0.00..575.59 rows=24954 width=0)          Index Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)   This is fairly fast.  Now we add order by to the query with id that degrades the query.(Such a query is generated by Rails like student.first with some condition)  explain select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d' order by id asc limit 1;                                                  QUERY PLAN                                                  ------------------------------------------------------------------------------------------------------------  Limit  (cost=0.43..488.51 rows=1 width=16)    -&gt;  Index Scan using students_pkey on students  (cost=0.43..12179370.22 rows=24954 width=16)          Filter: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)   How can I improve the speed to return the results of this query? Currently there are around 4990731 records in the table and is taking more than 2 minutes! Its running on RDS with db.t2.medium instance.  UPDATE After running Analyze students;  explain select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d' order by id asc limit 1;                                                        QUERY PLAN                                                         -----------------------------------------------------------------------------------------------------------------      Limit  (cost=8.46..8.46 rows=1 width=16)        -&gt;  Sort  (cost=8.46..8.46 rows=1 width=16)              Sort Key: id              -&gt;  Index Scan using students_school_id_idx on students  (cost=0.43..8.45 rows=1 width=16)                    Index Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)      explain analyze select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d' order by id asc limit 1;                                                                           QUERY PLAN                                                                              -----------------------------------------------------------------------------------------------------------------------------------------------------------     Limit  (cost=8.46..8.46 rows=1 width=16) (actual time=1.853..1.855 rows=1 loops=1)      -&gt;  Sort  (cost=8.46..8.46 rows=1 width=16) (actual time=1.851..1.852 rows=1 loops=1)            Sort Key: id            Sort Method: quicksort  Memory: 25kB            -&gt;  Index Scan using students_school_id_idx on students  (cost=0.43..8.45 rows=1 width=16) (actual time=1.841..1.843 rows=1 loops=1)                  Index Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)     Planning time: 0.145 ms     Execution time: 1.874 ms
85275
I have two tables with date and id fields. I want to join on both fields.  I tried   JOIN t2 ON CONCAT(t1.id, t1.date)=CONCAT(t2.id, t2.date)   that works, but it is very slow. is there a better way to do this?
19368
This is on SQL 2005 SP2, but I suspect this is something that applies to all query hints in general.   I've got an adhoc sql statement that gets a different query plan solely because of  OPTION(recompile). The batch statement is never (in practice) being reused as it is using non-parameterized dynamic sql generation where the statement changes with dates / other parameters. I've confirmed this is the case by checking the cached plan_handle, always a different ID as values in batch change.   When I do dbcc freeproccache I expect that the query plan I get for the statement would be exactly the same as one with "option (recompile)" query hint added. But its not, its quite different actually (much much faster).   Does anyone know why addition of a query hint would cause the engine to pick a different query plan?   The statement is something like this  dbcc freeproccache go sp_executesql '  declare @begindate datetime declare @enddate datetime select @begindate='1/1/2011' select @enddate='2/1/2011' select count(*) from tableA where tableA.datecolumn between @begindate and @enddate  and exists(  select A union all select B union all select C )'   all I have to do is change the statement to use recompile query hint and I get different (much better) query plan.  dbcc freeproccache go sp_executesql '  declare @begindate datetime declare @enddate datetime select @begindate='1/1/2011' select @enddate='2/1/2011' select count(*) from tableA where tableA.datecolumn between @begindate and @enddate  and exists( select A union all select B union all select C ) OPTION (RECOMPILE)'   As code is passing in the date values I can't use a plan guide to override the default behavior, looks like code change will be required. But I wish I knew where to look on why recompile would force a different plan to be used.
106137
If we have 2 tables  **user** +----+------+------+-----------+ | id | user | pass | gender_id | +----+------+------+-----------+ | 1  | lisa | good |     11    | | 2  | jack | pass |     10    | | 3  | paul | 5555 |     10    | | 4  | rich | none |     10    | +----+------+------+-----------+  **gender** +----+--------+ | id | gender | +----+--------+ | 11 | female | | 10 | male   | +----+--------+   If we join  them like so  SELECT user.id, user.user, user.pass, user.gender_id, gender.gender FROM `user` JOIN `gender` on user.gender_id   The result of query  array(4) {   [0]=&gt;   array(4) {     ["id"]=&gt;     string(3) "2"     ["type"]=&gt;     string(6) "jack"     ["pass"]=&gt;     string(11) "pass"     ["gender"]=&gt;     string(1) "male"     }   [1]=&gt;   array(4) {     ["id"]=&gt;     string(3) "3"     ["type"]=&gt;     string(6) "paul"     ["pass"]=&gt;     string(11) "5555"     ["gender"]=&gt;     string(1) "male"     }   [2]=&gt;   array(4) {     ["id"]=&gt;     string(3) "4"     ["type"]=&gt;     string(6) "rich"     ["pass"]=&gt;     string(11) "none"     ["gender"]=&gt;     string(1) "male"     }   [3]=&gt;   array(4) {     ["id"]=&gt;     string(3) "1"     ["type"]=&gt;     string(6) "lisa"     ["pass"]=&gt;     string(11) "good"     ["gender"]=&gt;     string(1) "female"     }   The order has now changed even though we did not specify this.(user lisa is last) If we change lisa gender_id to 11, and run the same query, the result will be  array(4) {   [0]=&gt;   array(4) {     ["id"]=&gt;     string(3) "1"     ["type"]=&gt;     string(6) "lisa"     ["pass"]=&gt;     string(11) "good"     ["gender"]=&gt;     string(1) "male"     }   [1]=&gt;   array(4) {     ["id"]=&gt;     string(3) "2"     ["type"]=&gt;     string(6) "jack"     ["pass"]=&gt;     string(11) "pass"     ["gender"]=&gt;     string(1) "male"     }   [2]=&gt;   array(4) {     ["id"]=&gt;     string(3) "3"     ["type"]=&gt;     string(6) "paul"     ["pass"]=&gt;     string(11) "5555"     ["gender"]=&gt;     string(1) "male"     }   [3]=&gt;   array(4) {     ["id"]=&gt;     string(3) "4"     ["type"]=&gt;     string(6) "rich"     ["pass"]=&gt;     string(11) "none"     ["gender"]=&gt;     string(1) "male"     }   This result is how the rows are positioned in the table(user lisa first), which is represented correctly in the results as we did not specify any order.  Why is the first result set being ordered when we are not specifying this. It does not seem correct, I want the results to be pulled exactly how they are positioned in the table.  Thanks
104961
So, I tried to run this query    which has many views (so I assume it's popular), but I get a message saying:     The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.   Now, true, that's not a trivial query, but is it really that bad? There isn't even a Join or anything.
6644
I've got two tables:  TableA ------ ID, Name  TableB ------ ID, SomeColumn, TableA_ID (FK for TableA)   The relationship is one row of TableA - many of TableB.  Now, I want to see a result like this:  ID     Name      SomeColumn  1.     ABC       X, Y, Z (these are three different rows) 2.     MNO       R, S   This won't work (multiple results in a subquery):  SELECT ID,        Name,         (SELECT SomeColumn FROM TableB WHERE F_ID=TableA.ID) FROM TableA   This is a trivial problem if I do the processing on the client side. But this will mean I will have to run X queries on every page, where X is the number of results of TableA.   Note that I can't simply do a GROUP BY or something similar, as it will return multiple results for rows of TableA.   I'm not sure if a UDF, utilizing COALESCE or something similar might work?