CODEWITHSUNDEEP
sql-servert-sql
MalcolmPH
MalcolmPH

Reputation: 19

SQL queries combined into one row

I'm having some difficulty combining the following queries, so that the results display in one row rather than in multiple rows:

SELECT value FROM dbo.parameter WHERE name='xxxxx.name'
SELECT dbo.contest.name AS Event_Name
FROM contest
INNER JOIN open_box on open_box.contest_id = contest.id
GROUP BY dbo.contest.name

SELECT COUNT(*) FROM open_option AS total_people

SELECT SUM(scanned) AS TotalScanned,SUM(number) AS Totalnumber
FROM dbo.open_box
GROUP BY contest_id

SELECT COUNT(*) FROM open AS reff
WHERE refer = 'True'

I would like to display data from the fields in each column similar to what is shown in the image below. Any help is appreciated!

enter image description here

Upvotes: 2

Views: 97

Answers (2)

TT.
TT.

Reputation: 16146

Tab's solution is fine, I just wanted to show an alternative way of doing this. The following statement uses subqueries to get the information in one row:

SELECT
    [xxxx.name]=(SELECT value FROM dbo.parameter WHERE name='xxxxx.name'),
    [Event Name]=(SELECT dbo.contest.name
        FROM contest
        INNER JOIN open_box on open_box.contest_id = contest.id
        GROUP BY dbo.contest.name),
    [Total People]=(SELECT COUNT(*) FROM open_option),
    [Total Scanned]=(SELECT SUM(scanned)
        FROM dbo.open_box
        GROUP BY contest_id),
    [Total Number]=(SELECT SUM(number)
        FROM dbo.open_box
        GROUP BY contest_id),
    Ref=(SELECT COUNT(*) FROM open WHERE refer = 'True');

This requires the Total Scanned and Total Number to be queried seperately.

Update: if you then want to INSERT that into another table there are essentially two ways to do that.

  1. Create the table directly from the SELECT statement:
SELECT
    -- the fields from the first query
INTO
    [database_name].[schema_name].[new_table_name]; -- creates table new_table_name
  1. Insert into a table that already exists from the INSERT
INSERT INTO [database_name].[schema_name].[existing_table_name](
    -- the fields in the existing_table_name
)
SELECT
    -- the fields from the first query

Upvotes: 2

Tab Alleman
Tab Alleman

Reputation: 31785

Just CROSS JOIN the five queries as derived tables:

SELECT * FROM (
  Query1
) AS q1
CROSS JOIN (
  Query2
) AS q2
CROSS JOIN (...

Assuming that each of your individual queries only returns one row, then this CROSS JOIN should result in only one row.

Upvotes: 2

Related Questions