To my mind, this is not pretty:

SELECT
	P.ImageId,
	Upvotes = ISNULL(P.[1], 0),
	Downvotes = ISNULL(-P.[-1], 0)
FROM
(
	SELECT
		U.ImageId,
		U.Score
	FROM dbo.Upvotes AS U
) AS S
PIVOT
(
	SUM(Score)
	FOR Score IN ([-1], [1])
) AS P;

@TomV stop feigning humility. of frickin' course you're good enough for that team

@PaulWhite pivot is always pretty

Compared with:

SELECT
	U.ImageId,
	VotesUp = SUM(CASE WHEN U.Score = +1 THEN 1 ELSE 0 END),
	VotesDown = SUM(CASE WHEN U.Score = -1 THEN 1 ELSE 0 END),
	NumRows = COUNT_BIG(*)
FROM dbo.Upvotes AS U
GROUP BY
	U.ImageId;

@PaulWhite i find this more beautifuller

@bluefeet The concept? Or the specific syntax?

B - E - A - Utiful

PIVOT is great until you need more than one aggregate (say SUM and MAX)

Or MAX and AVG

@PaulWhite I agree with @swasheck but on readability

@PaulWhite both ;)

never seen column = +1 in SQL before

In that example, I would like to write SUM(ABS(Score)) in the PIVOT and not have to use ISNULL/COALESCE. The [-1], [1]) also bugs me. Oh, and the derived table.

+ is always next to an integer

But I use pivot or case depending on situation

SELECT 1 + 3, 1 + (+3); ==> both 4

I'm sure that it works, it just never crossed my mind as something to do

@JoeObbish I do it sometimes to draw attention to an easily missed unary minus.

Just useful in some cases to make it more clear when comparing +ve to -ve

SELECT +1 + (+3)

SELECT +++1 + (+++3)

this is great thanks

It's unfortunate that T-SQL overloads + for at least three things

There's a good question on main about a bunch of plus signs.

I know other languages use different characters for concatenation and sanity

@AaronBertrand what's the third?

Addition, string concatenation, unary sign

SELECT 1 + 3, '1' + '3', +3;

would you prefer a different symbol for the third use?

of course I agree on string concatenation

I was thinking of that Q I think.

No, just string concat. Overloads are unavoidable in general, though WITH is a little over the top.

@AaronBertrand Incorrect syntax near 'WITH'...

@PaulWhite perhaps I'll ask one about the maximum possible number of unary signs

Is || standard SQL for concat?

@JoeObbish Closed as too negative.

:(

@PaulWhite yes

@AaronBertrand So we have + and CONCAT, but no ||` :-/

I will investigate

Right. I can't imagine that allowing || would be any harder than +=/-= was, or declare and set on same line

Strange eh.

@PaulWhite Sometimes we need to write code that works as is for both SQL Server and Oracle

CONCAT support in SQL Server 2012 was a very big deal for us

Seems a very simple thing compared to the other nonsense they've added to be friendlier to people coming from other systems, like the dumpster fire that is EOMONTH

@JoeObbish Does that work on Oracle too then?

@JoeObbish You reminded me of dba.stackexchange.com/a/165616/1192

@PaulWhite You can make it work

I think one takes as many arguments as you want

the other doesn't

@PaulWhite first you need to throw a 100 bill to the server

@Lamak @Lamak I've use Apex Sql Doc

@Lamak Ha true.

@McNets thanks

@Lamak 100? LOL, Oracle would just use that to light the cigar that starts the licensing conversation

@AaronBertrand yup, don't know what I was thinking

If you had said yacht full of 100 bills it would have been more believable

@JoeObbish I get 759 as the maximum number of + signs in SELECT ++++1

@Lamak apexsql.com/sql_tools_doc.aspx

@Forrest Thanks, I tried to test it but I messed up and now SSMS might crash

@AaronBertrand I meant more like throwing 100 bills for every time you want to use CONCAT

@Lamak so did I

I know ;)

@Lamak apexsql?

(great. now i have to wash my mouth out with soap)

@swasheck yeah, thanks :)

@Lamak ... what's the context?

thanks....for helping me?

i meant ... why did @mcnets mention it?

@swasheck because I asked people here if they knew any tools to document SQL Server databases

ah

dammit

design requirements

@s

@swasheck scroll up, @Lamak asked for a SQL documentation tool

@swasheck I asked for a documentation tool

;)

@McNets so much effort

Hey did Lamak ask for a documentation tool earlier?

i dont know. dont ask me

I asked for it

Cue the Benny Hill music

I'd link to it but it's probably hosted on AWS/S3

Because we're going in comedic circles :-)

BURN

@AaronBertrand i know. i was trying to take it to a new level but realized it looked idiotic

I think my sarcasm meter caps out at 5 layers

i'm only half-engaged. still fighting with GraphEngine. looks like microsoft has embraced the open-source documentation model as well. "good luck"

@swasheck did you tell them first that you would find them and would kill them?

never saw the movie. sorry.

@swasheck I suppose you are only kidding

@ypercubeᵀᴹ i am. documentation on open-source projects is a lot better than this

@swasheck but you knew it anyway

@Lamak is this graphengine similar to graphDB?

@Lamak yeah. but couldnt specifically relate it to the situation

@ypercubeᵀᴹ graphengine.io

@ypercubeᵀᴹ wut?

although ... sqlservercode.blogspot.dk/2016/10/…

amazon.co.uk/product-reviews/604016994X/…

Hah! I turned the SELECT ++++++1 into an algorithmic script that would return the largest replication to compile. I was a bit surprised though at the results for a minus sign, until I remembered I was just putting a really long comment into dynamic sql.

@Forrest That's what I was trying to do when I managed to crash SSMS

you could try SELECT - - - 1

Oooh, a space afterwards? Trying now.

758

Adding a SET PARSEONLY ON drops the max replication from 759 to 758 for some reason

mind throwing your code in pastebin? curious to see if I get the same result

pastebin.com/N6jLBex6

Feel free to point out flaws too

@Forrest seems good to me. Although in the unreal case that this fails for @NumToTest = 2, for instance, it will run like...forever?

@Lamak It appears to work (tried replicating a SELECT instead)

I am really really suspicious there's a way to make an infinite loop here, I just haven't found it yet

@Forrest I understand....but if this would fail for numToTest = 2, then it would run forever

or @numtotest = 1

because it would keep reassigning the value to @numtotest = 1

Yeah, if the Minimum Success isn't really a success, then it will infiniloop

yeah

I'm...not running this one on production ;)

let's do it!

I get 758 too

I ran on 2016 Dev. You?

2014 something

probably dev

wow it's actually patched

@JoeObbish older versions seem to get lower values

490 in SQL Server 2008R2

Adam Machanic should add this to his "hidden gems in SQL Server" presentation

Does adding the SET PARSEONLY ON before the statement make a difference for you too?

yep

@Forrest without it I got 491

SQL Server 2016 @ dbfiddle.uk: this works (1015), this doesn't (1016).

@AndriyM Very interesting. I wonder why it's different.

@Forrest ah, typical british problems

@Forrest I'm curious myself.

@AaronBertrand Here is the repro! dbfiddle.uk/…

(decided that it shouldn't be a comment since it's obviously not related to the answer)

I fully understand that ANSI_NULLS can change output, but I didn't see how it could in this case, and the OP confirmed:

It would be nice for him to confirm that ALL set settings are identical, but I'm over it.

@AaronBertrand Don't get me wrong, I agree. Just trying to make light of the situation

Some days I feel like yelling "MCVE!" to just about every question in my discipline.

asking a good question unfortunately appears to be an art

ah but ending up with a good question is a process :)

imgur is back up.