This is my query:
This is the XML output from one of the races:
I am trying to get the output to be like this:
Does anyone think this is possible? I willing to do anything to get this to work. I am not concerned about performance, just as long as the query/xml creating can be done within 60 seconds. It is with great anxiety, I await anyones response. Thank You
Code:
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,
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 Winner,
max(case when seqnum = 3 then CAST(winner AS tinyint) end) as WinnerSelected,
max(case when seqnum = 4 then [precinct percent] end) as PrecintPercent,
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 Loser,
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 Winner,
max(case when seqnum = 5 then CAST(winner AS tinyint) end) as WinnerSelected,
max(case when seqnum = 6 then [precinct percent] end) as PrecintPercent,
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 Loser,
max(case when seqnum = 6 then CAST(winner AS tinyint) end) as LoserSelected,
max(case when seqnum = 7 then title1 end) as title1,
max(case when seqnum = 7 then [precinct percent] end) as PrecintPercent,
max(case when seqnum = 7 then [candidate num] end) as Winner,
max(case when seqnum = 7 then Votes end) as WinningVotes,
max(case when seqnum = 7 then party end) as WinningParty,
max(case when seqnum = 7 then [first name]+[last name] end) as Winner,
max(case when seqnum = 7 then CAST(winner AS tinyint) end) as WinnerSelected,
max(case when seqnum = 8 then [precinct percent] end) as PrecintPercent,
max(case when seqnum = 8 then [candidate num] end) as Loser,
max(case when seqnum = 8 then Votes end) as LosingVotes,
max(case when seqnum = 8 then party end) as LosingParty,
max(case when seqnum = 8 then [first name]+[last name] end) as Loser,
max(case when seqnum = 8 then CAST(winner AS tinyint) end) as LoserSelected,
max(case when seqnum = 9 then title1 end) as title1,
max(case when seqnum = 9 then [precinct percent] end) as PrecintPercent,
max(case when seqnum = 9 then [candidate num] end) as Winner,
max(case when seqnum = 9 then Votes end) as WinningVotes,
max(case when seqnum = 9 then party end) as WinningParty,
max(case when seqnum = 9 then [first name]+[last name] end) as Winner,
max(case when seqnum = 9 then CAST(winner AS tinyint) end) as WinnerSelected,
max(case when seqnum = 10 then [precinct percent] end) as PrecintPercent,
max(case when seqnum = 10 then [candidate num] end) as Loser,
max(case when seqnum = 10 then Votes end) as LosingVotes,
max(case when seqnum = 10 then party end) as LosingParty,
max(case when seqnum = 10 then [first name]+[last name] end) as Loser,
max(case when seqnum = 10 then CAST(winner AS tinyint) end) as LoserSelected,
max(case when seqnum = 11 then title1 end) as title1,
max(case when seqnum = 11 then [precinct percent] end) as PrecintPercent,
max(case when seqnum = 11 then [candidate num] end) as Winner,
max(case when seqnum = 11 then Votes end) as WinningVotes,
max(case when seqnum = 11 then party end) as WinningParty,
max(case when seqnum = 11 then [first name]+[last name] end) as Winner,
max(case when seqnum = 11 then CAST(winner AS tinyint) end) as WinnerSelected,
max(case when seqnum = 12 then [precinct percent] end) as PrecintPercent,
max(case when seqnum = 12 then [candidate num] end) as Loser,
max(case when seqnum = 12 then Votes end) as LosingVotes,
max(case when seqnum = 12 then party end) as LosingParty,
max(case when seqnum = 12 then [first name]+[last name] end) as Loser,
max(case when seqnum = 12 then CAST(winner AS tinyint) end) as LoserSelected,
max(case when seqnum = 13 then title1 end) as title1,
max(case when seqnum = 13 then [precinct percent] end) as PrecintPercent,
max(case when seqnum = 13 then [candidate num] end) as Winner,
max(case when seqnum = 13 then Votes end) as WinningVotes,
max(case when seqnum = 13 then party end) as WinningParty,
max(case when seqnum = 13 then [first name]+[last name] end) as Winner,
max(case when seqnum = 13 then CAST(winner AS tinyint) end) as WinnerSelected,
max(case when seqnum = 14 then [precinct percent] end) as PrecintPercent,
max(case when seqnum = 14 then [candidate num] end) as Loser,
max(case when seqnum = 14 then Votes end) as LosingVotes,
max(case when seqnum = 14 then party end) as LosingParty,
max(case when seqnum = 14 then [first name]+[last name] end) as Loser,
max(case when seqnum = 14 then CAST(winner AS tinyint) end) as LoserSelected,
max(case when seqnum = 15 then title1 end) as title1,
max(case when seqnum = 15 then [precinct percent] end) as PrecintPercent,
max(case when seqnum = 15 then [candidate num] end) as Winner,
max(case when seqnum = 15 then Votes end) as WinningVotes,
max(case when seqnum = 15 then party end) as WinningParty,
max(case when seqnum = 15 then [first name]+[last name] end) as Winner,
max(case when seqnum = 15 then CAST(winner AS tinyint) end) as WinnerSelected,
max(case when seqnum = 16 then [precinct percent] end) as PrecintPercent,
max(case when seqnum = 16 then [candidate num] end) as Loser,
max(case when seqnum = 16 then Votes end) as LosingVotes,
max(case when seqnum = 16 then party end) as LosingParty,
max(case when seqnum = 16 then [first name]+[last name] end) as Loser,
max(case when seqnum = 16 then CAST(winner AS tinyint) end) as LoserSelected,
max(case when seqnum = 17 then title1 end) as title1,
max(case when seqnum = 17 then [precinct percent] end) as PrecintPercent,
max(case when seqnum = 17 then [candidate num] end) as Winner,
max(case when seqnum = 17 then Votes end) as WinningVotes,
max(case when seqnum = 17 then party end) as WinningParty,
max(case when seqnum = 17 then [first name]+[last name] end) as Winner,
max(case when seqnum = 17 then CAST(winner AS tinyint) end) as WinnerSelected,
max(case when seqnum = 18 then [precinct percent] end) as PrecintPercent,
max(case when seqnum = 18 then [candidate num] end) as Loser,
max(case when seqnum = 18 then Votes end) as LosingVotes,
max(case when seqnum = 18 then party end) as LosingParty,
max(case when seqnum = 18 then [first name]+[last name] end) as Loser,
max(case when seqnum = 18 then CAST(winner AS tinyint) end) as LoserSelected,
max(case when seqnum = 19 then title1 end) as title1,
max(case when seqnum = 19 then [precinct percent] end) as PrecintPercent,
max(case when seqnum = 19 then [candidate num] end) as Winner,
max(case when seqnum = 19 then Votes end) as WinningVotes,
max(case when seqnum = 19 then party end) as WinningParty,
max(case when seqnum = 19 then [first name]+[last name] end) as Winner,
max(case when seqnum = 19 then CAST(winner AS tinyint) end) as WinnerSelected,
max(case when seqnum = 20 then [precinct percent] end) as PrecintPercent,
max(case when seqnum = 20 then [candidate num] end) as Loser,
max(case when seqnum = 20 then Votes end) as LosingVotes,
max(case when seqnum = 20 then party end) as LosingParty,
max(case when seqnum = 20 then [first name]+[last name] end) as Loser,
max(case when seqnum = 20 then CAST(winner AS tinyint) end) as LoserSelected,
max(case when seqnum = 21 then title1 end) as title1,
max(case when seqnum = 21 then [precinct percent] end) as PrecintPercent,
max(case when seqnum = 21 then [candidate num] end) as Winner,
max(case when seqnum = 21 then Votes end) as WinningVotes,
max(case when seqnum = 21 then party end) as WinningParty,
max(case when seqnum = 21 then [first name]+[last name] end) as Winner,
max(case when seqnum = 21 then CAST(winner AS tinyint) end) as WinnerSelected
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]
FOR XML PATH ('ELECTION'), ROOT('root')
Code:
<ELECTION>
<RaceNumber>149</RaceNumber>
<title1>Henry County Board Dist. 2</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5213</Winner>
<WinningVotes>5140</WinningVotes>
<WinningParty>D</WinningParty>
<Winner1>Janice May</Winner1>
<WinnerSelected>1</WinnerSelected>
<WinnerPercent>7</WinnerPercent>
<Loser>5215</Loser>
<LosingVotes>4690</LosingVotes>
<LosingParty>D</LosingParty>
<Loser2>Ted Sturtevant</Loser2>
<LoserPercent>7</LoserPercent>
<LoserSelected>1</LoserSelected>
<title1>Henry County Board Dist. 2</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5214</Winner>
<WinningVotes>4564</WinningVotes>
<WinningParty>D</WinningParty>
<Winner>JohnSovanski</Winner>
<WinnerSelected>1</WinnerSelected>
<PrecintPercent>100</PrecintPercent>
<Loser>5221</Loser>
<LosingVotes>4368</LosingVotes>
<LosingParty>R</LosingParty>
<Loser>RogerGradert</Loser>
<LoserSelected>1</LoserSelected>
<title1>Henry County Board Dist. 2</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5220</Winner>
<WinningVotes>4321</WinningVotes>
<WinningParty>D</WinningParty>
<Winner>ThomasMay</Winner>
<WinnerSelected>1</WinnerSelected>
<PrecintPercent>100</PrecintPercent>
<Loser>5225</Loser>
<LosingVotes>4168</LosingVotes>
<LosingParty>R</LosingParty>
<Loser>DennisAnderson</Loser>
<LoserSelected>1</LoserSelected>
<title1>Henry County Board Dist. 2</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5216</Winner>
<WinningVotes>4123</WinningVotes>
<WinningParty>D</WinningParty>
<Winner>JamesFindley</Winner>
<WinnerSelected>1</WinnerSelected>
<PrecintPercent>100</PrecintPercent>
<Loser>5217</Loser>
<LosingVotes>4036</LosingVotes>
<LosingParty>D</LosingParty>
<Loser>ThomasWiley</Loser>
<LoserSelected>1</LoserSelected>
<title1>Henry County Board Dist. 2</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5222</Winner>
<WinningVotes>4031</WinningVotes>
<WinningParty>R</WinningParty>
<Winner>KippyNelson</Winner>
<WinnerSelected>0</WinnerSelected>
<PrecintPercent>100</PrecintPercent>
<Loser>5230</Loser>
<LosingVotes>3937</LosingVotes>
<LosingParty>R</LosingParty>
<Loser>WilliamLesage</Loser>
<LoserSelected>0</LoserSelected>
<title1>Henry County Board Dist. 2</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5228</Winner>
<WinningVotes>3832</WinningVotes>
<WinningParty>R</WinningParty>
<Winner>KelliParsons</Winner>
<WinnerSelected>0</WinnerSelected>
<PrecintPercent>100</PrecintPercent>
<Loser>5227</Loser>
<LosingVotes>3739</LosingVotes>
<LosingParty>R</LosingParty>
<Loser>MarshallJones</Loser>
<LoserSelected>0</LoserSelected>
<title1>Henry County Board Dist. 2</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5229</Winner>
<WinningVotes>3638</WinningVotes>
<WinningParty>R</WinningParty>
<Winner>TerrySears</Winner>
<WinnerSelected>0</WinnerSelected>
<PrecintPercent>100</PrecintPercent>
<Loser>5223</Loser>
<LosingVotes>3531</LosingVotes>
<LosingParty>R</LosingParty>
<Loser>RebekahMcCaw</Loser>
<LoserSelected>0</LoserSelected>
<title1>Henry County Board Dist. 2</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5218</Winner>
<WinningVotes>3418</WinningVotes>
<WinningParty>D</WinningParty>
<Winner>EricHollaway</Winner>
<WinnerSelected>0</WinnerSelected>
<PrecintPercent>100</PrecintPercent>
<Loser>5226</Loser>
<LosingVotes>3184</LosingVotes>
<LosingParty>R</LosingParty>
<Loser>ThomasMillsaps</Loser>
<LoserSelected>0</LoserSelected>
<title1>Henry County Board Dist. 2</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5219</Winner>
<WinningVotes>3102</WinningVotes>
<WinningParty>D</WinningParty>
<Winner>SammieBorst</Winner>
<WinnerSelected>0</WinnerSelected>
<PrecintPercent>100</PrecintPercent>
<Loser>5224</Loser>
<LosingVotes>3003</LosingVotes>
<LosingParty>R</LosingParty>
<Loser>DonaldDedobbelaere</Loser>
<LoserSelected>0</LoserSelected>
<title1>Henry County Board Dist. 2</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5374</Winner>
<WinningVotes>0</WinningVotes>
<WinningParty>D</WinningParty>
<Winner>JamesKursock</Winner>
<WinnerSelected>1</WinnerSelected>
<PrecintPercent>100</PrecintPercent>
<Loser>5375</Loser>
<LosingVotes>0</LosingVotes>
<LosingParty>D</LosingParty>
<Loser>JerryThompson</Loser>
<LoserSelected>1</LoserSelected>
</ELECTION>
Code:
<ELECTION>
<RaceNumber>149</RaceNumber>
<title1>Henry County Board Dist. 2</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5213</Winner>
<WinningVotes>5140</WinningVotes>
<WinningParty>D</WinningParty>
<Winner1>Janice May</Winner1>
<WinnerSelected>1</WinnerSelected>
<WinnerPercent>7</WinnerPercent>
<Loser>5215</Loser>
<LosingVotes>4690</LosingVotes>
<LosingParty>D</LosingParty>
<Loser2>Ted Sturtevant</Loser2>
<LoserPercent>7</LoserPercent>
<LoserSelected>1</LoserSelected>
</ELECTION>
<ELECTION>
<title1>Henry County Board Dist. 2</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5214</Winner>
<WinningVotes>4564</WinningVotes>
<WinningParty>D</WinningParty>
<Winner>JohnSovanski</Winner>
<WinnerSelected>1</WinnerSelected>
<PrecintPercent>100</PrecintPercent>
<Loser>5221</Loser>
<LosingVotes>4368</LosingVotes>
<LosingParty>R</LosingParty>
<Loser>RogerGradert</Loser>
<LoserSelected>1</LoserSelected>
</ELECTION>
<ELECTION>
<title1>Henry County Board Dist. 2</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5220</Winner>
<WinningVotes>4321</WinningVotes>
<WinningParty>D</WinningParty>
<Winner>ThomasMay</Winner>
<WinnerSelected>1</WinnerSelected>
<PrecintPercent>100</PrecintPercent>
<Loser>5225</Loser>
<LosingVotes>4168</LosingVotes>
<LosingParty>R</LosingParty>
<Loser>DennisAnderson</Loser>
<LoserSelected>1</LoserSelected>
</ELECTION>
<ELECTION>
<title1>Henry County Board Dist. 2</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5216</Winner>
<WinningVotes>4123</WinningVotes>
<WinningParty>D</WinningParty>
<Winner>JamesFindley</Winner>
<WinnerSelected>1</WinnerSelected>
<PrecintPercent>100</PrecintPercent>
<Loser>5217</Loser>
<LosingVotes>4036</LosingVotes>
<LosingParty>D</LosingParty>
<Loser>ThomasWiley</Loser>
<LoserSelected>1</LoserSelected>
</ELECTION>
<ELECTION>
<title1>Henry County Board Dist. 2</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5222</Winner>
<WinningVotes>4031</WinningVotes>
<WinningParty>R</WinningParty>
<Winner>KippyNelson</Winner>
<WinnerSelected>0</WinnerSelected>
<PrecintPercent>100</PrecintPercent>
<Loser>5230</Loser>
<LosingVotes>3937</LosingVotes>
<LosingParty>R</LosingParty>
<Loser>WilliamLesage</Loser>
<LoserSelected>0</LoserSelected>
</ELECTION>
<ELECTION>
<title1>Henry County Board Dist. 2</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5228</Winner>
<WinningVotes>3832</WinningVotes>
<WinningParty>R</WinningParty>
<Winner>KelliParsons</Winner>
<WinnerSelected>0</WinnerSelected>
<PrecintPercent>100</PrecintPercent>
<Loser>5227</Loser>
<LosingVotes>3739</LosingVotes>
<LosingParty>R</LosingParty>
<Loser>MarshallJones</Loser>
<LoserSelected>0</LoserSelected>
</ELECTION>
<ELECTION>
<title1>Henry County Board Dist. 2</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5229</Winner>
<WinningVotes>3638</WinningVotes>
<WinningParty>R</WinningParty>
<Winner>TerrySears</Winner>
<WinnerSelected>0</WinnerSelected>
<PrecintPercent>100</PrecintPercent>
<Loser>5223</Loser>
<LosingVotes>3531</LosingVotes>
<LosingParty>R</LosingParty>
<Loser>RebekahMcCaw</Loser>
<LoserSelected>0</LoserSelected>
</ELECTION>
<ELECTION>
<title1>Henry County Board Dist. 2</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5218</Winner>
<WinningVotes>3418</WinningVotes>
<WinningParty>D</WinningParty>
<Winner>EricHollaway</Winner>
<WinnerSelected>0</WinnerSelected>
<PrecintPercent>100</PrecintPercent>
<Loser>5226</Loser>
<LosingVotes>3184</LosingVotes>
<LosingParty>R</LosingParty>
<Loser>ThomasMillsaps</Loser>
<LoserSelected>0</LoserSelected>
</ELECTION>
<ELECTION>
<title1>Henry County Board Dist. 2</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5219</Winner>
<WinningVotes>3102</WinningVotes>
<WinningParty>D</WinningParty>
<Winner>SammieBorst</Winner>
<WinnerSelected>0</WinnerSelected>
<PrecintPercent>100</PrecintPercent>
<Loser>5224</Loser>
<LosingVotes>3003</LosingVotes>
<LosingParty>R</LosingParty>
<Loser>DonaldDedobbelaere</Loser>
<LoserSelected>0</LoserSelected>
</ELECTION>
<ELECTION>
<title1>Henry County Board Dist. 2</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5374</Winner>
<WinningVotes>0</WinningVotes>
<WinningParty>D</WinningParty>
<Winner>JamesKursock</Winner>
<WinnerSelected>1</WinnerSelected>
<PrecintPercent>100</PrecintPercent>
<Loser>5375</Loser>
<LosingVotes>0</LosingVotes>
<LosingParty>D</LosingParty>
<Loser>JerryThompson</Loser>
<LoserSelected>1</LoserSelected>
</ELECTION>