Just a simple "do or don't" question.
I want to select some user data from my database and put it in an Excel (programmatically). So, I get something like SELECT a, b, c, ... (about 60 more columns) FROM Users ... couple of joins ... etc. Done.
Then I have that data in my application and then I need detail rows. Usually I'd just join, except that in this case I need the details as columns and I also don't want the data I previously got as double rows.
I may have over 1000 rows and selecting the details would either be over 1000 roundtrips or do something like SELECT data FROM Details WHERE Id IN (over 1000 values gotten from previous data).
So I thought I'd want my data something like this:
Id SomeField OtherField etc. DetailId DetailField etc.
1 a b ... NULL NULL NULL
1 NULL NULL NULL 1 a ...
1 NULL NULL NULL 2 b ...
2 a b ... NULL NULL NULL
2 NULL NULL NULL 3 a ...
You get the point.
Looks something like this:
WITH CTEA
AS (
SELECT u.Id AS Id
,A AS A
,B AS B
,NULL AS DetailId
,NULL AS DetailField
FROM Users u
),
CTEB
AS (
SELECT u.Id AS Id
,NULL AS A
,NULL AS B
,det.Id AS DetailId
,det.Field AS DetailField
FROM Users u
WHERE u.Id IN (SELECT Id FROM CTEA)
)
SELECT * FROM CTEA
UNION ALL
SELECT * FROM CTEB
ORDER BY Id, DetailId
I guess it doesn't deserve a prize for beauty, but does it deserve a price for performance and efficiency?
I see no compelling reason NOT to do this, but I'm wondering what my peers think about it.
Thanks.