T-SQL Cursor Puzzle

Question

Post reply

T-SQL Cursor Puzzle

ktisat 1700

Grasshopper

Points: 22
More actions
March 3, 2011 at 3:42 pm

#247564

Hello,
I am having a problem trying to convert a list:
A001
A003
A004
A005
A006
B101
B105
B106
B107
B108
C205
C207
C209
D300
D301
D302
To the following: A001A001 A003A006 B101B101 B105B108 C205C205 C207C207 C209C209 D300D302
Any help would be greatly appreciated.

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

Your output pattern doesn't seem to have a specific rule to it. Can you provide requirements for the output?

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

ktisat 1700 Grasshopper Points: 22 More actions · Answer 2

If it is a single ID, like A001 then make the string A001A001

If it is a group like:

A003

A004

A005

A006

then concantenate = A003A006

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 3

Got it. Thinking.... 🙂

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

LutzM SSC Guru Points: 107049 More actions · Answer 4

Something like this? (assuming the table is called #t and the column is col)

;

WITH cte AS

(

SELECT col,

CAST(STUFF(col,1,1,'') AS INT) - ROW_NUMBER() OVER(ORDER BY col ) AS grp

FROM #t

)

SELECT

MIN(col) +MAX(col) AS range

FROM cte

GROUP BY grp

If you need it in one row, just concatenate the result using the FOR XML PATH() approach.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

LutzM (3/3/2011)
Something like this? (assuming the table is called #t and the column is col)
;
WITH cte AS
(
SELECT col,
CAST(STUFF(col,1,1,'') AS INT) - ROW_NUMBER() OVER(ORDER BY col ) AS grp
FROM #t
)
SELECT
MIN(col) +MAX(col) AS range
FROM cte
GROUP BY grp
If you need it in one row, just concatenate the result using the FOR XML PATH() approach.

Fantastic.. I was thinking a spaghetti code, but u did it with ease:) Though, ordering by CAST(STUFF(col,1,1,'') AS INT) would be more rigid,IMHO! Cool , Lutz!

LutzM SSC Guru Points: 107049 More actions · Answer 6

Thank you! :blush:

What needs to go into the ORDER BY clause of ROW_NUMBER() is sort of unclear based on the data provided so far:

If there will ever be a chance of having something like

B107, B108, C065

or, even worse

B107, B109, C108

then it would be required to define the order the data need to be sorted.

Both solutions (yours as well as mine) can be right. Or wrong. A classic "it depends" 😀

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 7

LutzM (3/3/2011)
Both solutions (yours as well as mine) can be right. Or wrong. A classic "it depends" 😀

:cool::-P

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 8

LutzM (3/3/2011)
Thank you! :blush:
What needs to go into the ORDER BY clause of ROW_NUMBER() is sort of unclear based on the data provided so far:
If there will ever be a chance of having something like
B107, B108, C065
or, even worse
B107, B109, C108
then it would be required to define the order the data need to be sorted.
Both solutions (yours as well as mine) can be right. Or wrong. A classic "it depends" 😀

Still, a clever solution on your part.

The fix for the sort order problem with the groupings is to simply enumerate the letter...

--===== Create and populate a test table.

-- This is not a part of the solution.

SELECT Col

INTO #t

FROM (

SELECT 'A001' UNION ALL

SELECT 'A003' UNION ALL

SELECT 'A004' UNION ALL

SELECT 'A005' UNION ALL

SELECT 'A006' UNION ALL

SELECT 'A106' UNION ALL

SELECT 'B101' UNION ALL

SELECT 'B105' UNION ALL

SELECT 'B106' UNION ALL

SELECT 'B107' UNION ALL

SELECT 'B108' UNION ALL

SELECT 'C205' UNION ALL

SELECT 'C207' UNION ALL

SELECT 'C209' UNION ALL

SELECT 'D300' UNION ALL

SELECT 'D301' UNION ALL

SELECT 'D302' UNION ALL

SELECT 'D001' UNION ALL

SELECT 'D002' UNION ALL

SELECT 'D003' UNION ALL

SELECT 'A000'

) d (Col)

;

--===== One possible solution enumerates the letter of the Col code.

WITH

cteCreateGroups AS

(

SELECT Col,

ColGroup = (ASCII(LEFT(Col,1))-64)*1000+RIGHT(Col,3) --Enumerate the Letter

- ROW_NUMBER() OVER (ORDER BY Col) --"Natural" sort

FROM #t

)

SELECT MIN(Col)+MAX(Col)

FROM cteCreateGroups

GROUP BY ColGroup

ORDER BY ColGroup

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 9

Jeff, can u please delete some of your old PMs; when i tried sending u one PM, it said the recipient exceeded the number of PMs. Just wanted to inform you one thing 🙂

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 10

Send me an email, CC.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ktisat Grasshopper Points: 23 More actions · Answer 11

Jeff-

Your code works perfect! I could believe that you were able to perform this task with just a few lines of code! GENIUS.

I was struggling trying to perform the tasks with NUMEROUS lines of Cursor code and still could not get through the door.

I can tell you how much I appreciate it.

And thanks to you LutzM & ColdCoffee that joined and freely gave knowledge...

“Destiny grants us our wishes, but in its own way, in order to give us something beyond our wishes.” - Johann Wolfgang von Goethe

KT

ktisat Grasshopper Points: 23 More actions · Answer 12

Jeff,

Your code works perfect! I could not believe you were able to perform this task with just a few lines of code. GENIUS!

I was struggling with numerous lines of cursor could and still could not get through the door!

And, Thanks to LutxM and Cold Coffee for freely sharing knowledge!

I cannot fully express my appreciation!!!!!

“Destiny grants us our wishes, but in its own way, in order to give us something beyond our wishes.” - Johann Wolfgang von Goethe

KT

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 13

Thank you very much for the feedback... but Lutz set the stage to avoid RBAR using some very high performance code. All I did was tweek his code. 🙂

As a side bar, Lutz, CC and I make one hell of a tag team with no sense of "one-up-manship". It would be a joy to work with them on a project in real life. :w00t:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 14

Jeff Moden (3/4/2011)
Thank you very much for the feedback... but Lutz set the stage to avoid RBAR using some very high performance code. All I did was tweek his code. 🙂
As a side bar, Lutz, CC and I make one hell of a tag team with no sense of "one-up-manship". It would be a joy to work with them on a project in real life. :w00t:

Thank you very much, Jeff! :blush:

Regarding the tag team: that would simply be AWESOME!!!!

Unfortunately, there's an ocean in between. But who knows... things may change... (I'm not talking about a massive continent movement though 😉 )

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]