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;
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;
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.
How does '+' operator behave in following statement?
select + 'taco'; --Result is 'taco'
Is it doing string concatenation with first string blank ('' + 'taco'), or does it mean something else?
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
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.