SQL exercises
Language 
September 24, 01:31 MSK
Login:

Password:

forgot password?
Registration

Personal page
Developers & Thanks
For employers
References
Feedback
Support SQL-EX.RU







Сколько стоит адвокат по уголовным делам
Адвокат по уголовным делам. Профессиональная помощь. Звоните
zabolotnyy.ru

SQL Exercises news letters, latest issue

§
As the important news can appear and between releases of news letters, I shall use this page for the publication of the information on all changes on the site.
The forum is not so suitable for this purpose since the message there "escapes" and can appear on previous page before it will lose a urgency.
So look in here if you wish to be well informed about last events on the site. The link to this page is in section of news at the main page below a subscription banner.
§

§
The nearest birthdays. Do not forget to congratulate!
Serge Avakimyan (xax) - 24, September
Ramil Batyrshin (Ramses) - 24, September
Alexandr Larionov (mrDS777) - 24, September
Pavel Zelenskiy (p.zelenskiy) - 24, September
Natalia Yurkovets (yna) - 25, September
Kirill Sidorov (KirS) - 26, September
Mariia Antonovich (Masha279) - 27, September
Ruslan Egorov (egoroffrus) - 27, September
Marsel Shaymardanov (xLiSPirit) - 28, September
Volodymyr Datsyuk (.Wizard) - 28, September

§
Last certified:
Stanislav Mihalenkov (black_horse) - 20, September
Aleksandr Savin (mirovingin) - 8, September
Anatoliy Bezgubenko (oWart) - 3, September
Dmitry Yurin (Diman9425) - 31, August
Igor Shkaredny (Gosha) - 10, August
Andrey Nesterkin (nestam) - 5, August
Andrey Smirnov (vvorkandrey) - 1, August
Pavel Zvonarev (pavel1211) - 7, July
Natalia Ksynina (KsNV_) - 26, June
Vladimir Chukanov (VSC) - 21, June

Let's learn SQL

UNPIVOT and NULL values

S.I.Moiseenko

Suppose we need to rotate a row containing a NULL value in one of its fields.

WITH utest AS
(SELECT 1 a, 2 b, NULL c)
SELECT * FROM utest;

I.e., we need to transform

a	b	c
1	2	NULL

to

a	1
b	2
c	NULL

Let’s use UNPIVOT:

WITH utest AS
(SELECT 1 a, 2 b, NULL c)
SELECT col, value FROM utest
UNPIVOT (
value FOR col IN (a,b,c)
) AS unpvt;

The first surprise we’re in for is a compile error:
The type of column "c" conflicts with the type of other columns specified in the UNPIVOT list.

This means the server didn’t implicitly convert the column "c" containing NULL to the type of the first two columns (that can be considered to be of integer type).

Let’s do it explicitly:

WITH utest AS
(SELECT 1 a, 2 b, CAST(NULL AS INT) c)
SELECT col,value FROM utest
UNPIVOT (
value FOR col IN (a,b,c)
) AS unpvt;

col value
a	1
b	2

Now, here’s the second surprise – as it turns out, UNPIVOT ignores NULL values, and doesn’t include them in the result set.

The first thing that comes to one’s mind is, to replace NULL with some valid value definitely not present in the column. Say, if the subject area doesn't allow negative values in column c, we can replace NULL with -1:

Read more...

Useful links

§ Official communities of the site sql-ex in social networks: SQL Exercises at VK.com and SQL Exercises at LinkedIn.com

§     The resource is positioned as a "handbook" for this site SQL-EX.COM, but can be used irrespective of it also.

§ The following DBMS are used on the site for solving exercises: SQL Server 2012 Express, Oracle Database 11g Express Edition, MariaDB-10.2.13 (compatible with MySQL 8), PostgreSQL 10.3.

§ If you like this site, vote for us:
del.icio.us
dzone.com
Digg.com
stumbleupon.com



Home
Week news
Certification
SQL exercises
Forums
Ratings
HELP
Performance
Profile
References
Rambler's Top100
Copyright SQL-EX © 2002-2018. All rights reserved.
contact