Facebook Sign in | Join
Getting Started with Adobe After Effects - Part 6: Motion Blur

TSQL Challenge #10

rated by 0 users
This post has 9 Replies | 5 Followers

Top 10 Contributor
Male
Posts 1,711
Points 22,489
Jacob Sebastian Posted: 06-23-2009 9:46 AM

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.

  • Post Points: 35
Top 500 Contributor
Posts 3
Points 60

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

*/

 

  • Post Points: 20
Top 75 Contributor
Male
Posts 30
Points 165

You query also works fine without any derived table.

Software : SQL Server 2008 Express, Windows XP SP3

Hardware : Core2Duo 4GHz / 2Go Ram. 

  • Post Points: 20
Top 500 Contributor
Posts 3
Points 60

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

 

thanx dude!

 

  • Post Points: 20
Top 100 Contributor
Male
Posts 18
Points 550

tsqlmaster,

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?

AMB

  • Post Points: 20
Top 500 Contributor
Posts 3
Points 60

Dear Hunchback,

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?

Regards

Rodrigo

www.tsqlmaster.net

  • Post Points: 20
Top 100 Contributor
Male
Posts 18
Points 550

tsqlmaster,

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
 [1] AS c1,
 [2] AS c2,
 [3] AS c3
FROM
 (
 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 ([1], [2], [3])
 ) AS pvt
ORDER 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 unpvt
AS
(
SELECT
 pk,
 [value],
 ROW_NUMBER() OVER(PARTITION BY pk ORDER BY [value]) AS rn
FROM
 (
 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
 [1] AS c1,
 [2] AS c2,
 [3] AS c3
FROM
 unpvt
 PIVOT
 (
 MIN([value])
 FOR rn IN ([1], [2], [3])
 ) AS pvt
ORDER BY
      c1, c2, c3;

Last, because we do not have a primary key, I had to use a subrrogate one.

WITH unpvt
AS
(
SELECT
 pk,
 [value],
 ROW_NUMBER() OVER(PARTITION BY pk ORDER BY [value]) AS rn
FROM
 (
 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
 [1] AS c1,
 [2] AS c2,
 [3] AS c3
FROM
 unpvt
 PIVOT
 (
 MIN([value])
 FOR rn IN ([1], [2], [3])
 ) AS pvt
ORDER 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.

AMB

  • Post Points: 5
Top 150 Contributor
Male
Posts 16
Points 220

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.

  • Post Points: 35
Top 100 Contributor
Male
Posts 18
Points 550

Hi Barry,

What about all of them?

Performance, readability, shortness, ...

But there are two things you will get no matter how bad your suggestion could be.

  1. Hands on lab
  2. Learn from the others

 

AMB

  • Post Points: 5
Top 10 Contributor
Male
Posts 1,711
Points 22,489

Hi Barry,

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.

Jacob Sebastian, SQL Server MVP

See this post that explains how to format TSQL code listing when posting in this forum.

  • Post Points: 5
Page 1 of 1 (10 items)
| RSS
Contact US

Copyright © Rivera Informatic Private Ltd.