TSQL: Get all rows for given ID

Question

I am trying to output all of my database reports into one report. I'm currently using nested select statements to get each line for each ID (the number of ID's is unknown). Now I would like to return all the rows for every ID (e.g. 1-25 if there are 25 rows) in one query. How would I do this?

SELECT (
    (SELECT ... FROM ... WHERE id = x) As Col1
    (SELECT ... FROM ... WHERE id = x) As Col2
    (SELECT ... FROM ... WHERE id = x) As Col3
)

---

EDIT: Here's an example:

SELECT
(select post_id from posts where report_id = 1) As ID,
(select isnull(rank, 0) from results where report_id = 1 and url like '%www.testsite.com%') As Main,
(select isnull(rank, 0) from results where report_id = 1 and url like '%.testsite%' and url not like '%www.testsite%') As Sub

This will return the rank of a result for the main domain and the sub-domain, as well as the ID for the posts table.

ID           Main           Sub
--------------------------------------
1            5              0

I'd like to loop through this query and change report_id to 2, then 3, then 4 and carry on until all results are displayed. Nothing else needs to change other than the report_id.

---

Here's a basic example of what is inside the tables

POSTS
post_id       post                        report_id
---------------------------------------------------------
1             "Hello, I am..."            1
2             "This may take..."          2
3      "Bla..."                    2 
4      "Bla..."                    3
5      "Bla..."        4

RESULTS
result_id    url             title          report_id
--------------------------------------------------------
1            http://...  "Intro"        1    
2            http://...      "Hello!"       1  
3            http://...  "Question"     2
4            http://...      "Help"         3

REPORTS
report_id    description
---------------------------------
1            Introductions
2            Q&A
3            Starting Questions
4            Beginner Guides
5            Lectures

The query will want to pull the first post, the first result from the main website (www) and the first result from a subdomain by their report_id. These tables are part of a complicated join structure with many other tables but for these purposes these tables are the only ones that are needed.

---

I've managed to solve the problem by creating a table, setting variables to take all the contents and insert them in a while loop, then selecting them and dropping the table. I'll leave this open for a bit to see if anyone picks up a better way of doing it because I hate doing it this way.

Answer 1

That is one query? You've provided your own answer?

If you mean you want to return a series of 'rows' as, for some reason, 'columns', this ability does exist, but I can't remember the exact name. Possible pivot. But it's a little odd.

Answer 2

Hi, see if this is what you are looking

SELECT CASE WHEN reports.id = 1 THEN reports.Name ELSE "" AS Col1, CASE WHEN reports.id = 2 THEN reports.Name ELSE "" AS Col2 .... FROM reports

Best Regards, Iordan

Answer 3

If you need each report id on its own column, take a look at the PIVOT/UNPIVOT commands.

Answer 4

Here's one way of doing it :

SELECT posts.post_id AS ID,
IsNull(tblMain.Rank, 0) AS Main,
IsNull(tblSub.Rank, 0) AS Sub
FROM posts
LEFT JOIN results AS tblMain ON posts.post_id = tblMain.report_id AND tblMain.url like '%www.testsite.com%'
LEFT JOIN results AS tblSub ON posts.post_id = tblSub.report_id AND tblSub.url like '%.testsite%' and tblSub.url not like '%www.testsite%'

Answer 5

Assuming you have a "master" table of IDs (if not I suggest you do so for Foreign Key purposes):

SELECT (
    (SELECT ... FROM ... WHERE id = m.ID) As Col1
    (SELECT ... FROM ... WHERE id = m.ID) As Col2
    (SELECT ... FROM ... WHERE id = m.ID) As Col3
)
FROM MasterIDs m

Depending on how much each report is similar,you may be able to speed that up by moving some of the logic out of the nested statements and into the main body of the query.

Possibly a better way of thinking about this is to alter each report statement to return (ID,value) and do something like:

SELECT
     report1.Id
    ,report1.Value AS Col1
    ,report2.Value AS Col2
FROM (SELECT Id, ... AS Value FROM ...) report1
JOIN (SELECT Id, ... AS Value FROM ...) report2 ON report1.Id = report2.Id

again, depending on the similarity of your reports you could probably combine these in someway.