February 8, 2013 at 3:08 am
Hi
Input
1
2
3
4
5
6
7
8
9
10
11
12
Output i want
1 4 7 10
2 5 8 11
3 6 9 12
February 8, 2013 at 4:07 am
So what do you have so far and where are you stuck.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2013 at 2:50 pm
Here's how I'd do it.
;WITH start AS (
SELECT
rowsort = CASE N % 3 WHEN 0 THEN 3 ELSE N % 3 end,
N,
rc = ROW_NUMBER() OVER (PARTITION BY CASE N % 3 WHEN 0 THEN 3 ELSE N % 3 END ORDER BY N)
FROM tally
WHERE n < 13)
SELECT
[1],[2],[3],[4]
FROM
start a PIVOT (MAX(N) FOR rc IN ([1],[2],[3],[4])) pvt
February 8, 2013 at 4:43 pm
It's funny... my answer to this question would be the following...
SELECT [1],[2],[3],[4]
INTO dbo.SomePermanentTable
FROM (
SELECT 1,4,7,10 UNION ALL
SELECT 2,5,8,11 UNION ALL
SELECT 3,6,9,12
) d ([1],[2],[3],[4])
;
SELECT [1],[2],[3],[4]
FROM dbo.SomePermanentTable
;
Of course, I'd also have to explain the reason why so the interviewer doesn't go vertical with self-righteous indignation. 😛 The reason I'd do it this way is because, as defined, this problem has incredibly limited scope and building a lookup table that will easily cache is frequently one of the better answers. It's like when people ask how I would solve for the factorials from 1 to 20. My answer would be "It's very small and it's not like it's ever going to change... I'd build a lookup table for it."
The other problem is that people sometimes don't think of "what happens if the problem grows" in scale. Even if you remove the WHERE clause in Mark's fine code, it never goes past 12 even though there could be millions of rows in the Tally Table. That's also a shortfall of people that design these bloody interview questions. My other question to the interviewer would be "What happens when you have more than 12 numbers you want to pivot? Do you want to repeat the 12-by pattern or what?"
Speaking of all that, if pressed by the interviewer to write computational code for this problem, I'd probably disappoint the interviewer because I would take the extra bit of time to make it scale past the number 12 by repeating the 12-by pattern. I might even fail the interview with someone that doesn't understand the concept of bullet-proofing your code because they want it real bad and are will to settle for it that way... real bad. 😉
In case anyone is interested, here's a scalable version... my Tally Table goes up to 11,000 but the code will handle much more than that.
WITH
cteEnumerate AS
( --=== Number the rows starting at 0 so we can easily use modulus and integer math
SELECT t.N,
MyCount = ROW_NUMBER() OVER (ORDER BY t.N) - 1
FROM dbo.Tally t
)
,
cteMap AS
( --=== This maps each row to 12 item, 3 row, 4 column groups for pivoting
SELECT N,
GrpNum = MyCount/12,
RowNum = (MyCount%12)%3,
ColNum = (MyCount/3)%4
FROM cteEnumerate
)
--===== This does the actual pivoting in a very high speed fashion and is very
-- easy to convert to dynamic SQL to make a more flexible version
SELECT [1] = MAX(CASE WHEN ColNum = 0 THEN N ELSE 0 END),
[2] = MAX(CASE WHEN ColNum = 1 THEN N ELSE 0 END),
[3] = MAX(CASE WHEN ColNum = 2 THEN N ELSE 0 END),
[4] = MAX(CASE WHEN ColNum = 3 THEN N ELSE 0 END)
FROM cteMap
GROUP BY GrpNum,RowNum
ORDER BY [1]
If you don't have a Tally Table available, now's a good time to make one. See the following article for how to make one and how it can be used to replace certain types of loops.
http://www.sqlservercentral.com/articles/T-SQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
February 11, 2013 at 5:29 am
On interview, I would just go with the following:
SELECT TOP 12 IDENTITY(INT,1,1) N INTO #sample FROM sys.columns
SELECT s1.N, s2.N, s3.N, s4.N
FROM #sample s1
JOIN #sample s2 ON s2.N = s1.N+3
JOIN #sample s3 ON s3.N = s2.N+3
JOIN #sample s4 ON s4.N = s3.N+3
February 11, 2013 at 7:41 am
Thanks Jeff...
I didn't look at it too hard... but then you made me try to make it work for N < 12 and still use my pivot function (which I love when I don't have to do it dynamically).
Talk about a headache... I came up with this.... It drove me nuts because I had to introduce the same kinds of GrpNum, RowNum, and ColNum stuff, but to make the PIVOT function work I had to do it a bit different...
glad I got it to work.
;WITH start AS (
SELECT
N,
GrpNum = N/12 - CASE WHEN N%12=0 THEN 1 ELSE 0 end,
RowNum = CASE N%3 WHEN 0 THEN 3 ELSE N%3 end,
ColNum = CASE WHEN N%12 BETWEEN 1 AND 3 THEN 1
WHEN N%12 BETWEEN 4 AND 6 THEN 2
WHEN N%12 BETWEEN 7 AND 9 THEN 3
WHEN N%12 BETWEEN 10 AND 11 OR N%12 = 0 THEN 4 end
FROM tally
WHERE n <1000)
SELECT
[1],[2],[3],[4]
FROM
start a PIVOT (MAX(N) FOR Colnum IN ([1],[2],[3],[4])) pvt
ORDER BY
GrpNum,
RowNum
February 11, 2013 at 7:44 am
The weird thing is... estimated execution plans puts mine ahead. 🙂
and so did the actual... didn't expect that... I thought PIVOT was not that efficient
February 11, 2013 at 2:57 pm
mtassin (2/11/2013)
The weird thing is... estimated execution plans puts mine ahead. 🙂and so did the actual... didn't expect that... I thought PIVOT was not that efficient
NEVER use comparisons of execution plans to determine the winner of a foot race. It can (and frequently does) lie like a rug. I gave Grant Fritchey an example for one of his books where the execution plan aid that script "A" would take 0% of the time and script "B" would take 100% of the time. During measure performance testing, the exact opposite was true.
About the only thing you really want to use execution plans for is to see big arrow counts, what types of processes are being used, and what type of index usage you're getting.
Also, although Cross tabs are frequently more effecient than pivots, it truly depends. Almost nothing concerning performance can be used as a certainty. The obvious exceptions to that are the many forms of RBAR.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply