May 30, 2014 at 8:45 pm
Jeff, you are absolutely right. I was so focused on the single row return that I lost track of the fact that the other bits were set. I had eventually concluded that it was never meant to operate on more than one input row, and thus ignored the results that had been staring me in the face. I was so enamored with the "solution" from running individual input rows that I lost track of the actual results when I ran multiple rows. Feel free to celebrate with a beer popsicle... you've earned it...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 30, 2014 at 9:25 pm
Heh... not to worry. I started out by testing the other solutions and then went back to the original solution. I kept looking at it and asking myself why the heck the original coder used MAX. At first, it almost looked like a CROSSTAB but then I decided that a CROSSTAB wasn't needed for this. When the whole shebang returned just one row for multiple rows, I used the values of 1 and 128 as my table source and, boink (head-desk), I knew what it had to be.
Now the next question would be, WHY did the original coder do this?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 30, 2014 at 9:52 pm
Jeff Moden (5/30/2014)
Heh... not to worry. I started out by testing the other solutions and then went back to the original solution. I kept looking at it and asking myself why the heck the original coder used MAX. At first, it almost looked like a CROSSTAB but then I decided that a CROSSTAB wasn't needed for this. When the whole shebang returned just one row for multiple rows, I used the values of 1 and 128 as my table source and, boink (head-desk), I knew what it had to be.Now the next question would be, WHY did the original coder do this?
Yep... time for the original poster to step forward... Wendy Elizabeth? What say you?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 2, 2014 at 2:01 am
Jeff Moden (5/30/2014)
Heh... not to worry. I started out by testing the other solutions and then went back to the original solution. I kept looking at it and asking myself why the heck the original coder used MAX. At first, it almost looked like a CROSSTAB but then I decided that a CROSSTAB wasn't needed for this. When the whole shebang returned just one row for multiple rows, I used the values of 1 and 128 as my table source and, boink (head-desk), I knew what it had to be.Now the next question would be, WHY did the original coder do this?
Heh - I assumed the aggregation was to flatten the results to each row from the input table because of the cross join to the bits table, and that the code was missing the GROUP BY. Good catch, Mr Moden.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 2, 2014 at 2:02 am
ScottPletcher (5/30/2014)
I think the overhead of XML is unwarranted here...
Yes you're right, with a fixed number of rows, FOR XML PATH is probably overkill.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 3, 2014 at 12:21 am
sgmunson (5/30/2014)
Jeff Moden (5/30/2014)
Heh... not to worry. I started out by testing the other solutions and then went back to the original solution. I kept looking at it and asking myself why the heck the original coder used MAX. At first, it almost looked like a CROSSTAB but then I decided that a CROSSTAB wasn't needed for this. When the whole shebang returned just one row for multiple rows, I used the values of 1 and 128 as my table source and, boink (head-desk), I knew what it had to be.Now the next question would be, WHY did the original coder do this?
Yep... time for the original poster to step forward... Wendy Elizabeth? What say you?
@wendy Elizabeth,
Do you understand the explaination of what the code is doing? And, to satisfy our curiosity, do you have any idea why the original coder may have written something like this "Vertical OR"?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 3, 2014 at 12:44 am
ChrisM@Work (6/2/2014)
Jeff Moden (5/30/2014)
Heh... not to worry. I started out by testing the other solutions and then went back to the original solution. I kept looking at it and asking myself why the heck the original coder used MAX. At first, it almost looked like a CROSSTAB but then I decided that a CROSSTAB wasn't needed for this. When the whole shebang returned just one row for multiple rows, I used the values of 1 and 128 as my table source and, boink (head-desk), I knew what it had to be.Now the next question would be, WHY did the original coder do this?
Heh - I assumed the aggregation was to flatten the results to each row from the input table because of the cross join to the bits table, and that the code was missing the GROUP BY. Good catch, Mr Moden.
Oh, don't feel bad about that. Considering the relative complexity of the code, I missed it myself all the way up to when I ran the original code against a table source and it returned only a single row.
The cool part is, lot's of people came up with interesting code for bit slicing.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 3, 2014 at 12:51 am
ChrisM@Work (6/2/2014)
ScottPletcher (5/30/2014)
I think the overhead of XML is unwarranted here...Yes you're right, with a fixed number of rows, FOR XML PATH is probably overkill.
I agree that explicit solutions like Scott's are frequently better (Luhn 10 Check Sums for Credit Card verification is an excellent example) but I'm not sure I'd say one way or the other on this until I saw the results of a million row test with dumps to variables to take display time out of the picture.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 3, 2014 at 12:53 am
On the subject of "overkill"... I won't dumb down code just because someone might not understand it. When I was a contractor, I considered it to be very important to write code that scaled and performed well. This is were a decent set of simple comments in the code come in to play. If you're really concerned about someone being able to maintain code, instead of dumbing down and perpetuating their ignorance, add a link to an article on SQLServerCentral to the comments. Think of it as providing the free service of making the developers smarter. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
June 3, 2014 at 10:23 am
To return a single "OR'd" row, I think you can just add MAX()es to the "standard" & conditions:
SELECT
STUFF(
MAX(CASE WHEN tmask & 1 > 0 THEN ' 1' ELSE '' END) +
MAX(CASE WHEN tmask & 2 > 0 THEN ' 2' ELSE '' END) +
MAX(CASE WHEN tmask & 4 > 0 THEN ' 3' ELSE '' END) +
MAX(CASE WHEN tmask & 8 > 0 THEN ' 4' ELSE '' END) +
MAX(CASE WHEN tmask & 16 > 0 THEN ' 5' ELSE '' END) +
MAX(CASE WHEN tmask & 32 > 0 THEN ' 6' ELSE '' END) +
MAX(CASE WHEN tmask & 64 > 0 THEN ' 7' ELSE '' END) +
MAX(CASE WHEN tmask & 128 > 0 THEN ' 8' ELSE '' END) +
MAX(CASE WHEN tmask & 256 > 0 THEN ' 9' ELSE '' END) +
MAX(CASE WHEN tmask & 512 > 0 THEN ' 10' ELSE '' END) +
MAX(CASE WHEN tmask & 1024 > 0 THEN ' 11' ELSE '' END) +
MAX(CASE WHEN tmask & 2048 > 0 THEN ' 12' ELSE '' END
, 1, 1, '') AS Fields
FROM (
SELECT CAST(18 AS int) AS tmask UNION ALL
SELECT 255
) AS test_data
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 7, 2014 at 11:21 am
Jeff Moden (6/3/2014)
sgmunson (5/30/2014)
Jeff Moden (5/30/2014)
Heh... not to worry. I started out by testing the other solutions and then went back to the original solution. I kept looking at it and asking myself why the heck the original coder used MAX. At first, it almost looked like a CROSSTAB but then I decided that a CROSSTAB wasn't needed for this. When the whole shebang returned just one row for multiple rows, I used the values of 1 and 128 as my table source and, boink (head-desk), I knew what it had to be.Now the next question would be, WHY did the original coder do this?
Yep... time for the original poster to step forward... Wendy Elizabeth? What say you?
@wendy Elizabeth,
Do you understand the explaination of what the code is doing? And, to satisfy our curiosity, do you have any idea why the original coder may have written something like this "Vertical OR"?
@wendy Elizabeth,
Two way street here, Wendy... the courtesy of a reply would be greatly appreciated.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 7, 2014 at 11:27 am
ScottPletcher (6/3/2014)
To return a single "OR'd" row, I think you can just add MAX()es to the "standard" & conditions:
SELECT
STUFF(
MAX(CASE WHEN tmask & 1 > 0 THEN ' 1' ELSE '' END) +
MAX(CASE WHEN tmask & 2 > 0 THEN ' 2' ELSE '' END) +
MAX(CASE WHEN tmask & 4 > 0 THEN ' 3' ELSE '' END) +
MAX(CASE WHEN tmask & 8 > 0 THEN ' 4' ELSE '' END) +
MAX(CASE WHEN tmask & 16 > 0 THEN ' 5' ELSE '' END) +
MAX(CASE WHEN tmask & 32 > 0 THEN ' 6' ELSE '' END) +
MAX(CASE WHEN tmask & 64 > 0 THEN ' 7' ELSE '' END) +
MAX(CASE WHEN tmask & 128 > 0 THEN ' 8' ELSE '' END) +
MAX(CASE WHEN tmask & 256 > 0 THEN ' 9' ELSE '' END) +
MAX(CASE WHEN tmask & 512 > 0 THEN ' 10' ELSE '' END) +
MAX(CASE WHEN tmask & 1024 > 0 THEN ' 11' ELSE '' END) +
MAX(CASE WHEN tmask & 2048 > 0 THEN ' 12' ELSE '' END
, 1, 1, '') AS Fields
FROM (
SELECT CAST(18 AS int) AS tmask UNION ALL
SELECT 255
) AS test_data
Spot on, Scott!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply