I almost posted a question today to a forum

Question

I almost posted a question today to a forum

Lowell

SSC Guru

Points: 323518
More actions
May 20, 2010 at 10:07 am

#144925

So I almosted posted a topic today,but as i was typing the question, putting in the sample code to demonstrate my mental block, I stopped because my own quesiton broke the mental logjam, tried one more thing and got my answer.
does that happen a lot to you guys? if you have a question, and you start to post the sample data, you end up answering it before you even post?
If you care, here was my issue: i was using some CTE's to try and format my list of all tables so that it presented the list in five columns instead of just one; the end results would be going to a web admin function, so wasting whitespace and over-scrolling were what i tried to address.
my original CTE limited my results to 24 rows of 5 tables...about 120 tables, but my db has more than a thousand tables.
I could not see my mistake.
anyway, i started with this:
--select count(*) from sys.tables
--1573 tables total
with baseCTE AS (select ROW_NUMBER() over (order by name) As RW,name from sys.tables),
--the / 25 limits me to 24 rows of 5 accross
firstCTE AS (select RW / 25 as RW1, name from baseCTE),
G1 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 0),
G2 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 1),
G3 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 2),
G4 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 3),
G5 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 4)
SELECT
G1.name,
G2.name,
G3.name,
G4.name,
G5.name
FROM G1
LEFT OUTER JOIN G2 ON G1.RW = G2.RW
LEFT OUTER JOIN G3 ON G1.RW = G3.RW
LEFT OUTER JOIN G4 ON G1.RW = G4.RW
LEFT OUTER JOIN G5 ON G1.RW = G5.RW
and ended with this, which gives me what i was after:
--select count(*) from sys.tables
--1573 tables total
with baseCTE AS (select ROW_NUMBER() over (order by name) As RW,name from sys.tables),
--the / 25 limits me to 24 rows of 5 accross
firstCTE AS (select ROW_NUMBER() over (partition by RW / 5 order by name) as RW1, RW,name from baseCTE),
G1 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 IN (0,5)),
G2 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 1),
G3 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 2),
G4 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 3),
G5 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 4)
SELECT
G1.name,
G2.name,
G3.name,
G4.name,
G5.name
FROM G1
LEFT OUTER JOIN G2 ON G1.RW = G2.RW
LEFT OUTER JOIN G3 ON G1.RW = G3.RW
LEFT OUTER JOIN G4 ON G1.RW = G4.RW
LEFT OUTER JOIN G5 ON G1.RW = G5.RW
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 8 posts - 1 through 7 (of 7 total)

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

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 1

I used to work for an organization that had a rule that if you got stuck on a problem for more than 10 minutes, you were required to grab someone else and explain the problem. This was because in the process of organizing a description of the problem, you would usually discover the solution. If not, you would get a different viewpoint.

Converting oxygen into carbon dioxide, since 1955.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Lowell (5/20/2010)
So I almosted posted a topic today,but as i was typing the question, putting in the sample code to demonstrate my mental block, I stopped because my own quesiton broke the mental logjam, tried one more thing and got my answer.
does that happen a lot to you guys?

Heh... why do you think I wrote the article on "Forum Etiquette"? It's a reminder to ... ME. 😛

Shifting gears to your problem, will this do it for you?

WITH

baseCTE AS

(

SELECT TOP(24*5)

(ROW_NUMBER() OVER (ORDER BY Name)-1)/5+1 AS RW,

(ROW_NUMBER() OVER (ORDER BY Name)-1)%5+1 AS CL,

Name

FROM sys.tables

)

SELECT MAX(CASE WHEN CL = 1 THEN Name ELSE '' END) AS Col1TableName,

MAX(CASE WHEN CL = 2 THEN Name ELSE '' END) AS Col2TableName,

MAX(CASE WHEN CL = 3 THEN Name ELSE '' END) AS Col3TableName,

MAX(CASE WHEN CL = 4 THEN Name ELSE '' END) AS Col4TableName,

MAX(CASE WHEN CL = 5 THEN Name ELSE '' END) AS Col5TableName

FROM baseCTE

GROUP BY RW

;

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Lowell (5/20/2010)
So I almosted posted a topic today,but as i was typing the question, putting in the sample code to demonstrate my mental block, I stopped because my own quesiton broke the mental logjam, tried one more thing and got my answer.
does that happen a lot to you guys? if you have a question, and you start to post the sample data, you end up answering it before you even post?

I have had it happen on occasion. It makes you think about the question in a different light.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

It's definitely happened to me. Just reorganizing your thought process to explain it sometimes helps you to review the question in a new light. It's one reason I blog/write as well. It helps me ensure that I actually understand things.

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 5

This happens to me all the time. I regularly start a question to the forums and have it solved before I hit the post button. I usually also do a final google search before hitting post.

Another thing that helps is to get up and walk away from the problem for a bit. Smokers taught me this trick. I've never been a smoker but early in my career I used to go on "Smoke Breaks" with them. We'd chat about anything but my problem and 1/2 way through the break the solution would usually just pop into my mind.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

At my current project, when I'm stuck with a problem for more than 15 minutes, I call an (older) colleague with much more experience than me. Mostly, when I'm halfway explaining the problem, I find my error/solution without he saying anything. My colleague usually says that it is due to his "wise aura". 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Terry Low-350996 SSC Rookie Points: 48 More actions · Answer 7

It happens on a regular basis. In fact, I now use it as a tool.

I have a co-worker who brings along a cup of coffee, leans back and listens. When, during my description of the problem, the eureka moment comes, I stop, thank him profusely, we both grin and wait for the next time.