SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL QUERY - FOR XML PATH
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

snots34
Starting Member

12 Posts

Posted - 02/20/2014 :  23:56:50  Show Profile  Reply with Quote
Here's 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,
    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


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')


This is the output:
<root>
          <ELECTION>
            <RaceNumber>101</RaceNumber>
            <title1>President</title1>
            <PrecintPercent>100</PrecintPercent>
            <Winner>5083</Winner>
            <WinningVotes>999877</WinningVotes>
            <WinningParty>D</WinningParty>
            <Winner1>Barack Obama</Winner1>
            <WinnerSelected>1</WinnerSelected>
            <WinnerPercent>53</WinnerPercent>
            <Loser>5077</Loser>
            <LosingVotes>888888</LosingVotes>
            <LosingParty>R</LosingParty>
            <Loser2>Mitt Romney</Loser2>
            <LoserPercent>47</LoserPercent>
            <LoserSelected>0</LoserSelected>
          </ELECTION>
          <ELECTION>
            <RaceNumber>102</RaceNumber>
            <title1>U.S. Congress Dist. 1</title1>
            <PrecintPercent>100</PrecintPercent>
            <Winner>5085</Winner>
            <WinningVotes>216879</WinningVotes>
            <WinningParty>D</WinningParty>
            <Winner1>Bruce Braley</Winner1>
            <WinnerSelected>1</WinnerSelected>
            <WinnerPercent>57</WinnerPercent>
            <Loser>5086</Loser>
            <LosingVotes>159657</LosingVotes>
            <LosingParty>R</LosingParty>
            <Loser2>Ben Lange</Loser2>
            <LoserPercent>42</LoserPercent>
            <LoserSelected>0</LoserSelected>
          </ELECTION>
          <ELECTION>
            <RaceNumber>103</RaceNumber>
            <title1>U.S. Congress Dist. 2</title1>
            <PrecintPercent>100</PrecintPercent>
            <Winner>5089</Winner>
            <WinningVotes>209551</WinningVotes>
            <WinningParty>D</WinningParty>
            <Winner1>Dave Loebsack</Winner1>
            <WinnerSelected>1</WinnerSelected>
            <WinnerPercent>57</WinnerPercent>
            <Loser>5090</Loser>
            <LosingVotes>160717</LosingVotes>
            <LosingParty>R</LosingParty>
            <Loser2>John  Archer</Loser2>
            <LoserPercent>43</LoserPercent>
            <LoserSelected>0</LoserSelected>
          </ELECTION>
      <ELECTION>
        <RaceNumber>148</RaceNumber>
        <title1>Henry County Board Dist. 1</title1>
        <PrecintPercent>100</PrecintPercent>
        <Winner>5205</Winner>
        <WinningVotes>6408</WinningVotes>
        <WinningParty>R</WinningParty>
        <Winner1>Karen Urick</Winner1>
        <WinnerSelected>1</WinnerSelected>
        <WinnerPercent>6</WinnerPercent>
        <Loser>5204</Loser>
        <LosingVotes>6390</LosingVotes>
        <LosingParty>R</LosingParty>
        <Loser2>Tim Wells</Loser2>
        <LoserPercent>6</LoserPercent>
        <LoserSelected>1</LoserSelected>
</ELECTION>
<ELECTION>
        <RaceNumber>148</RaceNumber> 
        <title1>Henry County Board Dist. 1</title1>
        <PrecintPercent>100</PrecintPercent>
        <Winner>5203</Winner>
        <WinningVotes>6362</WinningVotes>
        <WinningParty>R</WinningParty>
        <Winner1>KathyNelson</Winner1>
        <WinnerSelected>1</WinnerSelected>
        <Loser>5212</Loser>
        <LosingVotes>6343</LosingVotes>
        <LosingParty>R</LosingParty>
        <Loser2>MarvinGradert</Loser2>
        <LoserSelected>1</LoserSelected>
</ELECTION>
<ELECTION>
        <RaceNumber>148</RaceNumber>
        <title1>Henry County Board Dist. 1</title1>
        <PrecintPercent>100</PrecintPercent>
        <Winner>5206</Winner>
        <WinningVotes>6281</WinningVotes>
        <WinningParty>R</WinningParty>
        <Winner1>RickLivesay</Winner1>
        <WinnerSelected>1</WinnerSelected>
        <Loser>5207</Loser>
        <LosingVotes>5618</LosingVotes>
        <LosingParty>R</LosingParty>
        <Loser2>LorenRathjen</Loser2>
        <LoserSelected>1</LoserSelected>
      </ELECTION>
        </root>


RACE 148 now has multiple parent nodes for every 2 candidates.

Is this possible to do, with my query?

snots34
Starting Member

12 Posts

Posted - 02/21/2014 :  00:42:31  Show Profile  Reply with Quote
I totally messed up the question, this is the output:

<ELECTION>
<RaceNumber>148</RaceNumber>
<title1>Henry County Board Dist. 1</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5205</Winner>
<WinningVotes>6408</WinningVotes>
<WinningParty>R</WinningParty>
<Winner1>Karen Urick</Winner1>
<WinnerSelected>1</WinnerSelected>
<WinnerPercent>6</WinnerPercent>
<Loser>5204</Loser>
<LosingVotes>6390</LosingVotes>
<LosingParty>R</LosingParty>
<Loser2>Tim Wells</Loser2>
<LoserPercent>6</LoserPercent>
<LoserSelected>1</LoserSelected>
<title1>Henry County Board Dist. 1</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5203</Winner>
<WinningVotes>6362</WinningVotes>
<WinningParty>R</WinningParty>
<Winner1>KathyNelson</Winner1>
<WinnerSelected>1</WinnerSelected>
<Loser>5212</Loser>
<LosingVotes>6343</LosingVotes>
<LosingParty>R</LosingParty>
<Loser2>MarvinGradert</Loser2>
<LoserSelected>1</LoserSelected>
<title1>Henry County Board Dist. 1</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5206</Winner>
<WinningVotes>6281</WinningVotes>
<WinningParty>R</WinningParty>
<Winner1>RickLivesay</Winner1>
<WinnerSelected>1</WinnerSelected>
<Loser>5207</Loser>
<LosingVotes>5618</LosingVotes>
<LosingParty>R</LosingParty>
<Loser2>LorenRathjen</Loser2>
<LoserSelected>1</LoserSelected>
</ELECTION>

This is how I want it to look:

<ELECTION>
<RaceNumber>148</RaceNumber>
<title1>Henry County Board Dist. 1</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5205</Winner>
<WinningVotes>6408</WinningVotes>
<WinningParty>R</WinningParty>
<Winner1>Karen Urick</Winner1>
<WinnerSelected>1</WinnerSelected>
<WinnerPercent>6</WinnerPercent>
<Loser>5204</Loser>
<LosingVotes>6390</LosingVotes>
<LosingParty>R</LosingParty>
<Loser2>Tim Wells</Loser2>
<LoserPercent>6</LoserPercent>
<LoserSelected>1</LoserSelected>
</ELECTION>
<ELECTION>
<RaceNumber>148</RaceNumber>
<title1>Henry County Board Dist. 1</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5203</Winner>
<WinningVotes>6362</WinningVotes>
<WinningParty>R</WinningParty>
<Winner1>KathyNelson</Winner1>
<WinnerSelected>1</WinnerSelected>
<Loser>5212</Loser>
<LosingVotes>6343</LosingVotes>
<LosingParty>R</LosingParty>
<Loser2>MarvinGradert</Loser2>
<LoserSelected>1</LoserSelected>
</ELECTION>
<ELECTION>
<RaceNumber>148</RaceNumber>
<title1>Henry County Board Dist. 1</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5206</Winner>
<WinningVotes>6281</WinningVotes>
<WinningParty>R</WinningParty>
<Winner1>RickLivesay</Winner1>
<WinnerSelected>1</WinnerSelected>
<Loser>5207</Loser>
<LosingVotes>5618</LosingVotes>
<LosingParty>R</LosingParty>
<Loser2>LorenRathjen</Loser2>
<LoserSelected>1</LoserSelected>
</ELECTION>

Geeze!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000