This thread discusses TSQL Challenge #10.
Jacob Sebastian, SQL Server MVP
See this post that explains how to format TSQL code listing when posting in this forum.
The solution is quite trick, but cand be achieved using case and derived tables. Check it out:
Please find the solution to the tsql challenge bellow:
temporarily removed and will put back after the challenge is closed
You query also works fine without any derived table.
Software : SQL Server 2008 Express, Windows XP SP3
Hardware : Core2Duo 4GHz / 2Go Ram.
yes indeed Fabien...I just executed the query again without the derived table and to my surprise It worked fine...=)
In my day to day I tend to use derived tables as frequently as possible, and this time it was not necessary...
Your suggestion is ingenious.
One thing you forgot is to keep NULL in mind, unless we change the original schema to not allow nulls. Also, how this suggestion will look like if we put more columns in the table, let us say 10 columns?
Thanks for your post. How are things back in Notre Dame?..No matter how bad things may look, don't hunck your back!...;-)
The implementation was formulated to the presented problem. If the challenge description was "imagine this table could have, 10, 20, 100 columns" I would implement a solution that would consider that.
But thanks for your suggestions. I'LL surely keep the nulls in mind in my future implementations.
One other thing...I'd love to see your implementation of the challenge!...Would you be so kind as to publish that on the blog?
As I said, your suggestion is ingenious, so do not take me wrong for what I said. If we insert another row and one of the columns is null, then it will fail to do what it was intended to, but that is something you can fix with no much effort.
The suggestion I sent is very slow compare with yours. The only thing I would point as a pro, is that you can use it for any number of columns, the change will not be much and we could also tweak it to be dynamic. The approach is not complicated, and this is what it does.
- Assign a subrrogate key using row_number function. We could get rid of it if we had one, anyway every relational table should have a primary key.
- Pivot the data
- Assign a row number to each value (column), partitioned by the subrrogate key, in order to sort the columns that now are rows after the pivoting.
- Pivot the data back
Let us suppose that we do have a primary key and that all columns are not nullable. Then we could use something like:
SELECT DISTINCT  AS c1,  AS c2,  AS c3FROM ( SELECT pk, [value], ROW_NUMBER() OVER(PARTITION BY pk ORDER BY [value]) AS rn FROM @t AS t UNPIVOT ( [value] FOR column_name IN ([c1], [c2], [c3]) ) AS unpvt ) AS r PIVOT ( MIN([value]) FOR rn IN (, , ) ) AS pvtORDER BY c1, c2, c3;
Sorry for the format, but I do not know how to change this editor to work like SSMS.
As you can see, if we have 10 or 20 columns, we can easely change the query. Now, if we have NULL values, then the previous query will fail, because the UNPIVOT operator will not create a row for NULL values, so we will be missing those and you will notice it after we pivot the data to obtain same schema as the original. That tell us that we can not unpivot the data using the UNPIVOT operator, so I decided to use a cross join with a table having same rows as columns in the original table.
WITH unpvtAS(SELECT pk, [value], ROW_NUMBER() OVER(PARTITION BY pk ORDER BY [value]) AS rnFROM ( SELECT pk, CASE n.c1 WHEN 1 THEN t.c1 WHEN 2 THEN t.c2 WHEN 3 THEN t.c3 END AS [value] FROM @t AS t CROSS JOIN (SELECT 1 AS c1 UNION ALL SELECT 2 UNION ALL SELECT 3) AS n ) AS r)SELECT DISTINCT  AS c1,  AS c2,  AS c3FROM unpvt PIVOT ( MIN([value]) FOR rn IN (, , ) ) AS pvtORDER BY c1, c2, c3;
Last, because we do not have a primary key, I had to use a subrrogate one.
WITH unpvtAS(SELECT pk, [value], ROW_NUMBER() OVER(PARTITION BY pk ORDER BY [value]) AS rnFROM ( SELECT pk, CASE n.c1 WHEN 1 THEN t.c1 WHEN 2 THEN t.c2 WHEN 3 THEN t.c3 END AS [value] FROM (SELECT c1, c2, c3, ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS pk FROM @t) AS t CROSS JOIN (SELECT 1 AS c1 UNION ALL SELECT 2 UNION ALL SELECT 3) AS n ) AS r)SELECT DISTINCT  AS c1,  AS c2,  AS c3FROM unpvt PIVOT ( MIN([value]) FOR rn IN (, , ) ) AS pvtORDER BY c1, c2, c3;
As you see now, the performance is far from the performance of your suggestion, because yours need to scan the table just one.
Looking forward to continue learning from you all.
I did not see any evaluation criteria listed in the Challenge. So what will the submissions be judged on? Speed? Cleverness? Shortness?
Thi sis simething that I think should be a standard part of the Challenge announcements, otherwise we do not know how to determine which of several approaches we should use.
What about all of them?
Performance, readability, shortness, ...
But there are two things you will get no matter how bad your suggestion could be.
As Alejandro (Hunchback) mentioned, we should ideally look for all.
I admit that we should add more clarity about this when the challenges are announced/published. We are working on drafting somthing similar and hope to make it public soon.
Thanks for the feedback.