How to get these 2 Queries into one single result

Question

This is my query:

select  rc.[race number] AS RaceNumber,
    max(case when seqnum = 1 then title1 end) as title1,
    max(case when seqnum = 1 then [precinct percent] end) as PrecintPercent,
    max(case when seqnum = 1 then [candidate num] end) as Winner,
    max(case when seqnum = 1 then Votes end) as WinningVotes,
    max(case when seqnum = 1 then party end) as WinningParty,
    max(case when seqnum = 1 then leader end) as Winner1,
    max(case when seqnum = 1 then CAST(winner AS tinyint) end) as WinnerSelected,
    max(case when seqnum = 1 then [leader percent] end) as WinnerPercent,
    max(case when seqnum = 2 then [candidate num] end) as Loser,
    max(case when seqnum = 2 then Votes end) as LosingVotes,
    max(case when seqnum = 2 then party end) as LosingParty,
    max(case when seqnum = 2 then leader2 end) as Loser2,
    max(case when seqnum = 2 then [leader2 percent] end) as LoserPercent,
    max(case when seqnum = 2 then CAST(winner AS tinyint) end) as LoserSelected

from 
(
select  
        r.title1,
        r.[precinct percent],
        rc.[race number],
        rc.[candidate num],
        rc.[Votes],
        rc.[winner],
        c.[party],
        r.[leader],
        r.[leader percent],
        r.[leader2],
        r.[leader2 percent],
        c.[first name],
        c.[last name],


            row_number() over (partition by rc.[race number] order by votes desc) as seqnum
    from    dbo.[RACE CANDIDATES] rc
    inner join dbo.[CANDIDATE] c    on  rc.[candidate num]  = c.[candidate number]
    inner join dbo.[RACE] r
     on rc.[race number] = r.[race number] 

) rc
group by rc.[race number]
select  rc.[race number] AS RaceNumber,
    max(case when seqnum = 3 then title1 end) as title1,
    max(case when seqnum = 3 then [precinct percent] end) as PrecintPercent,
    max(case when seqnum = 3 then [candidate num] end) as Winner,
    max(case when seqnum = 3 then Votes end) as WinningVotes,
    max(case when seqnum = 3 then party end) as WinningParty,
    max(case when seqnum = 3 then [first name]+[last name] end) as Winner1,
    max(case when seqnum = 3 then CAST(winner AS tinyint) end) as WinnerSelected,
    max(case when seqnum = 4 then [candidate num] end) as Loser,
    max(case when seqnum = 4 then Votes end) as LosingVotes,
    max(case when seqnum = 4 then party end) as LosingParty,
    max(case when seqnum = 4 then [first name]+[last name] end) as Loser2,
    max(case when seqnum = 4 then CAST(winner AS tinyint) end) as LoserSelected,
    max(case when seqnum = 5 then title1 end) as title1,
    max(case when seqnum = 5 then [precinct percent] end) as PrecintPercent,
    max(case when seqnum = 5 then [candidate num] end) as Winner,
    max(case when seqnum = 5 then Votes end) as WinningVotes,
    max(case when seqnum = 5 then party end) as WinningParty,
    max(case when seqnum = 5 then [first name]+[last name] end) as Winner1,
    max(case when seqnum = 5 then CAST(winner AS tinyint) end) as WinnerSelected,
    max(case when seqnum = 6 then [candidate num] end) as Loser,
    max(case when seqnum = 6 then Votes end) as LosingVotes,
    max(case when seqnum = 6 then party end) as LosingParty,
    max(case when seqnum = 6 then [first name]+[last name] end) as Loser2,
    max(case when seqnum = 6 then CAST(winner AS tinyint) end) as LoserSelected


from 
(
select  
        r.title1,
        r.[precinct percent],
        rc.[race number],
        rc.[candidate num],
        rc.[Votes],
        rc.[winner],
        c.[party],
        r.[leader],
        r.[leader percent],
        r.[leader2],
        r.[leader2 percent],
        c.[first name],
        c.[last name],


            row_number() over (partition by rc.[race number] order by votes desc) as seqnum
    from    dbo.[RACE CANDIDATES] rc
    inner join dbo.[CANDIDATE] c    on  rc.[candidate num]  = c.[candidate number]
    inner join dbo.[RACE] r
     on rc.[race number] = r.[race number] 

) rc
group by rc.[race number]

This results in 2 query windows. I would like to know if I can get them both to be within one single query, while keeping them separate. I believe if I can split the queries in the select statement before the end, I can get the results seperated before I use a FOR XML clause. I am trying all kinds of different methods here to get this accomplsihed.

Thank You

thanks.

Did you try a union? While the columns dont appear to match you can create dummy empty values for the missing columns — Namphibian, Feb 24 '14 at 05:45
To the best of my ability, yes - I did try UNION. I am not skilled enough to put the right syntax in. Many of omy select is redundant, but I do not where to add the union or what to omit from the statement to get that to work. thank you for your response. — user3242661, Feb 24 '14 at 05:47

score 0 · Answer 1 · answered Feb 24 '14 at 05:54

Hard to say if this will run but try this query which will create the missing columns in your first select. If you posted the table structures we would be able to help a little more.

SELECT rc.[race number] AS RaceNumber,
   max(CASE WHEN seqnum = 1 THEN title1 END) AS title1,
   max(CASE WHEN seqnum = 1 THEN [precinct percent] END) AS PrecintPercent,
   max(CASE WHEN seqnum = 1 THEN [candidate num] END) AS Winner,
   max(CASE WHEN seqnum = 1 THEN Votes END) AS WinningVotes,
   max(CASE WHEN seqnum = 1 THEN party END) AS WinningParty,
   max(CASE WHEN seqnum = 1 THEN leader END) AS Winner1,
   max(CASE WHEN seqnum = 1 THEN CAST(winner AS tinyint) END) AS WinnerSelected,
   max(CASE WHEN seqnum = 1 THEN [leader percent] END) AS WinnerPercent,
   max(CASE WHEN seqnum = 2 THEN [candidate num] END) AS Loser,
   max(CASE WHEN seqnum = 2 THEN Votes END) AS LosingVotes,
   max(CASE WHEN seqnum = 2 THEN party END) AS LosingParty,
   max(CASE WHEN seqnum = 2 THEN leader2 END) AS Loser2,
   max(CASE WHEN seqnum = 2 THEN [leader2 percent] END) AS LoserPercent,
   max(CASE WHEN seqnum = 2 THEN CAST(winner AS tinyint) END) AS LoserSelected,
   '' AS title1,
   '' AS PrecintPercent,
   '' AS Winner,
   '' AS WinningVotes,
   '' AS WinningParty,
   '' AS Winner1,
   '' AS WinnerSelected,
   '' AS Loser,
   '' AS LosingVotes,
   '' AS LosingParty,
   '' AS Loser2,
   '' AS LoserSelected
FROM
 (SELECT r.title1,
      r.[precinct percent],
      rc.[race number],
      rc.[candidate num],
      rc.[Votes],
      rc.[winner],
      c.[party],
      r.[leader],
      r.[leader percent],
      r.[leader2],
      r.[leader2 percent],
      c.[first name],
      c.[last name],
      row_number() over (partition BY rc.[race number]
                         ORDER BY votes DESC) AS seqnum
 FROM dbo.[RACE CANDIDATES] rc
 INNER JOIN dbo.[CANDIDATE] c    on  rc.[candidate num]  = c.[candidate number]
 INNER JOIN dbo.[RACE] r ON rc.[race number] = r.[race number]) rc
GROUP BY rc.[race number]
UNION
SELECT rc.[race number] AS RaceNumber,
   max(CASE WHEN seqnum = 3 THEN title1 END) AS title1,
   max(CASE WHEN seqnum = 3 THEN [precinct percent] END) AS PrecintPercent,
   max(CASE WHEN seqnum = 3 THEN [candidate num] END) AS Winner,
   max(CASE WHEN seqnum = 3 THEN Votes END) AS WinningVotes,
   max(CASE WHEN seqnum = 3 THEN party END) AS WinningParty,
   max(CASE WHEN seqnum = 3 THEN [first name]+[last name] END) AS Winner1,
   max(CASE WHEN seqnum = 3 THEN CAST(winner AS tinyint) END) AS WinnerSelected,
   max(CASE WHEN seqnum = 4 THEN [candidate num] END) AS Loser,
   max(CASE WHEN seqnum = 4 THEN Votes END) AS LosingVotes,
   max(CASE WHEN seqnum = 4 THEN party END) AS LosingParty,
   max(CASE WHEN seqnum = 4 THEN [first name]+[last name] END) AS Loser2,
   max(CASE WHEN seqnum = 4 THEN CAST(winner AS tinyint) END) AS LoserSelected,
   max(CASE WHEN seqnum = 5 THEN title1 END) AS title1,
   max(CASE WHEN seqnum = 5 THEN [precinct percent] END) AS PrecintPercent,
   max(CASE WHEN seqnum = 5 THEN [candidate num] END) AS Winner,
   max(CASE WHEN seqnum = 5 THEN Votes END) AS WinningVotes,
   max(CASE WHEN seqnum = 5 THEN party END) AS WinningParty,
   max(CASE WHEN seqnum = 5 THEN [first name]+[last name] END) AS Winner1,
   max(CASE WHEN seqnum = 5 THEN CAST(winner AS tinyint) END) AS WinnerSelected,
   max(CASE WHEN seqnum = 6 THEN [candidate num] END) AS Loser,
   max(CASE WHEN seqnum = 6 THEN Votes END) AS LosingVotes,
   max(CASE WHEN seqnum = 6 THEN party END) AS LosingParty,
   max(CASE WHEN seqnum = 6 THEN [first name]+[last name] END) AS Loser2,
   max(CASE WHEN seqnum = 6 THEN CAST(winner AS tinyint) END) AS LoserSelected
FROM
  (SELECT r.title1,
      r.[precinct percent],
      rc.[race number],
      rc.[candidate num],
      rc.[Votes],
      rc.[winner],
      c.[party],
      r.[leader],
      r.[leader percent],
      r.[leader2],
      r.[leader2 percent],
      c.[first name],
      c.[last name],
      row_number() over (partition BY rc.[race number]
                         ORDER BY votes DESC) AS seqnum
FROM dbo.[RACE CANDIDATES] rc
INNER JOIN dbo.[CANDIDATE] c    on  rc.[candidate num]  = c.[candidate number]
INNER JOIN dbo.[RACE] r ON rc.[race number] = r.[race number]) rc
GROUP BY rc.[race number]

It is close. I got an error: All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. — user3242661, Feb 24 '14 at 05:57
At least I am able to see how to UNION the selects. My actual query will have 21 seqnum's. lol — user3242661, Feb 24 '14 at 05:58
It's a good idea to get into the habit of using `UNION ALL` as opposed to `UNION` — Nick.Mc, Feb 24 '14 at 06:02
Cool hope it works for you. If it does please answer as correct if it does bring you success. Happy hunting. — Namphibian, Feb 24 '14 at 06:02
See this http://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all to understand the difference — Namphibian, Feb 24 '14 at 06:03

How to get these 2 Queries into one single result

1 Answers1