Pivot Table with Non-Cardinal Values

Pivot table with non-cardinal values

A solution that does not require multiple self-joins:

You can PIVOT on the result of the ROW_NUMBER() analytic function to get your desired output:

SELECT *
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER ( PARTITION BY test ORDER BY ROWNUM ) AS rn
  FROM   your_table t
)
PIVOT ( MAX( Reader ) AS reader, MAX( result ) AS result FOR rn IN ( 1, 2, 3 ) );

Output:

TEST 1_READER 1_RESULT 2_READER 2_RESULT 3_READER 3_RESULT
---- -------- -------- -------- -------- -------- --------
   1 John          1.6 Jack          5.2
   2 Ursula        2.5 Jack          5.1
   3 Albert        3.0

Or, just using aggregation functions:

SELECT test,
       MAX( CASE rn WHEN 1 THEN reader END ) AS reader_1,
       MAX( CASE rn WHEN 1 THEN result END ) AS result_1,
       MAX( CASE rn WHEN 2 THEN reader END ) AS reader_2,
       MAX( CASE rn WHEN 2 THEN result END ) AS result_2,
       MAX( CASE rn WHEN 3 THEN reader END ) AS reader_3,
       MAX( CASE rn WHEN 3 THEN result END ) AS result_3
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER ( PARTITION BY test ORDER BY ROWNUM ) AS rn
  FROM   your_table t
)
GROUP BY test;

SQL Server 2008 - Display row items in column without PIVOT

Can you try this?

WITH A AS (SELECT HMYPERSON, HMYTENANT, SRELATIONSHIP, P.ULASTNAME, p.SFIRSTNAME, ROW_NUMBER() OVER (PARTITION BY HMYTENANT ORDER BY HMYPERSON) AS RN
            FROM ROOM R
            INNER JOIN PERSON P ON P.HMY = R.HMYPERSON  
                                AND (R.SRELATIONSHIP <> 'Guarantor' OR P.IPERSONTYPE<>90)           
            )
SELECT T.SCODE, T.SLASTNAME + ' ' + T.SFIRSTNAME TENANTNAME
        , A1.ULASTNAME + ' ' + A1.SFIRSTNAME ROOMMATENAME_1
        , A2.ULASTNAME + ' ' + A2.SFIRSTNAME ROOMMATENAME_2
        , A3.ULASTNAME + ' ' + A3.SFIRSTNAME ROOMMATENAME_3
        , A4.ULASTNAME + ' ' + A4.SFIRSTNAME ROOMMATENAME_4
        , A5.ULASTNAME + ' ' + A5.SFIRSTNAME ROOMMATENAME_5
FROM TENANT T
LEFT JOIN A A1 ON A1.HMYTENANT = T.HMYPERSON AND A1.RN=1
LEFT JOIN A A2 ON A2.HMYTENANT = T.HMYPERSON AND A2.RN=2
LEFT JOIN A A3 ON A3.HMYTENANT = T.HMYPERSON AND A3.RN=3
LEFT JOIN A A4 ON A4.HMYTENANT = T.HMYPERSON AND A4.RN=4
LEFT JOIN A A5 ON A5.HMYTENANT = T.HMYPERSON AND A5.RN=5
WHERE T.HMYPERSON=1

Output:

SCODE TENANTNAME  ROOMMATENAME_1           ROOMMATENAME_2           ROOMMATENAME_3           ROOMMATENAME_4           ROOMMATENAME_5
----- ----------- ------------------------ ------------------------ ------------------------ ------------------------ ------------------------
t0011 lName fName roommate2 roommate2Fname roommate3 roommate3Fname roommate1 roommate1Fname NULL               NULL

sql pivot character variable

The problem is that, in your data, there is no relationship between each person, so it can't be shown on the same row (what links Juli with Satria that you want to see them on the same row?).

If you can generate a row number by each job, you can mix them together by their cardinal position of each job.

IF OBJECT_ID('tempdb..#Jobs') IS NOT NULL
    DROP TABLE #Jobs

CREATE TABLE #Jobs (
    ID INT,
    Name VARCHAR(100),
    Job VARCHAR(100))

INSERT INTO #Jobs (
    ID,
    Name,
    Job)
VALUES
    (1001, 'Satria', 'Dancer'),
    (1002, 'Juli', 'Actress'),
    (1003, 'Mario', 'Actress'),
    (1004, 'Memet', 'Salesman'),
    (1005, 'Alan', 'Photographer'),
    (1006, 'Kiky', 'Photographer'),
    (1007, 'Chacha', 'Photographer'),
    (1008, 'Joko', 'Actress'),
    (1009, 'Juni', 'Dancer'),
    (1010, 'Putra', 'Salesman')

;WITH JobNumbering AS
(
    SELECT
        J.Job,
        J.Name,
        Ranking = ROW_NUMBER() OVER (PARTITION BY J.Job ORDER BY J.ID)
    FROM
        #Jobs AS J
)
SELECT
    P.Actress,
    P.Dancer,
    P.Photographer,
    P.Salesman
FROM
    JobNumbering AS J
    PIVOT (
        MAX(J.Name) FOR J.Job IN ([Actress], [Dancer], [Photographer], [Salesman])
    ) AS P

Algorithm: Is there a good way of solving a comparison?

Let's experiment, would you.

1. Example

Let's look at {A,B,C,D} and sort it.

Solution 1: By sets

Greater of {A,B,C,D} -> B (thus B>A, B>C and B>D)
Greater of {A,C,D} -> D (thus D>A and D>C)
Greater of {A,C} -> A (thus A>C)

Total order [B,D,A,C]

Solution 2: By pairs

Greater between A and C -> A (thus A>C)
Greater between B and D -> B (thus B>D)
Greater between D and A -> D

Total order [B,D,A,C]

What's the catch ? Obviously it's more difficult by pairs, here I chose them so that the merge would be easy (none).

2. Remarks

a) Total ordering

The > only work so well with a total ordering: ie for two given elements A and B of a set either A>B or B>A. If none of those two relations hold, then A and B are equivalent.

The problem with the Solution 1 approach is that if you present the user with {A,B,C,D} and A and B are equivalent and greater than C and D... what's the answer supposed to be ?

b) Transitivity

The > relationship is transitive, meaning that if A>B and B>C then A>C. It's important to use this fact since you can therefore deduce relationships between two elements without ever asking the user.

c) What's the goal ?

Is the goal supposed to minimize the number of questions to the user or supposed to minimize the user's work ? Because obviously it's more difficult for a user to answer questions from the first solution...

3. Modeling

One can model the problem as a "graph" problem.

You begin with a set of nodes {A,B,C,D} which represent the values you want to test.

Sorting the set is equivalent to computing the minimum set of oriented edges that link these nodes so that given any two nodes, a path leads from one to the other. I do insist of minimum.

For example, if I have 2 edges: B>A and B>C, then if I discover than A>C I shall remove B>C because I can deduce it by transitivity. The important properties is that if no two nodes are equivalent, the cardinal of the resulting set of edges is the cardinal of the set of nodes minus 1. In my example (given 4 nodes) it would be 3.

An oracle (or an extremely lucky guy) would thus be able to only ask 3 questions, and yet build this graph... that's what we should strive for.

4. How to solve this problem ?

Okay, let's suppose that we have no 2 equivalent elements. This means that if A>B is false, then I can deduce that B>A...

For the representation of our little graph, let's take an array:

  A B C D  
D . > .      # . represent the unknown
C . >        
B >          # < and > have their usual meaning...
A

Because of the symmetry we only need a triangle.

By counting the number . we can see the number of unknown relationships, the ideal solution is to get rid of all of those . while asking as few questions as possible to the user.

A good question is therefore one than remove as much . as possible in one swoop.

5. My question

And thus, I have another type of question:

Select the elements lower than "D" in the following {A,B,C}: _

This question feels better that the What is the greater... because I explicitly target those relationships I wish to know (D?A, D?B and D?C) while the greater guarantees that I will obtain as much relationships but I can't know which in advance.

I try to maximize the usefulness of the question

Of course, it's no leeway for laziness: it's still important to take transitivity into account while exploiting the results of the questions.

6. Exploring larger sets

With larger sets, you can group the elements, and then merge them later on, but the merge is always a messy operation: you will probably get answers you already knew. However it's a great practice for my little question:

Given 2 ordered (disjoint) sets: [A,B,C,D,...] and [R,S,T,U,...] let's see the 3 questions of the toolbox:

Which is greater: A or R ? _
Which is the greatest element of {A,B,R,S} ? _
Which elements are greater than A in {R,S,T} ? _
Gives 1 relationship
Gives 2 relationships: 3 of which 1 is already known
Gives 3 relationships

The third question asks for a more elaborate answer, but it is much more suitable for merge situations. In the merge case, it's as efficient as asking to sort the elements, since it asks precisely for the only 3 relationships we don't know in the board.

7. Conclusion

You now have 4 questions in your box:

Sort: Sort the following elements from greater to lower {A,B,C,D} ? _
Pivot: Which elements are greater than A in this set {B,C,D} ? _
Greater: Which element is the greater in this set {A,B,C,D} ? _
Pair: Which is the greater element among A and B ? _

(Pair can be regarded as a degenerated case of either Greater or Pivot)

Given that each question gives n-1 relationships for n nodes, you could try to gauge how much time it takes for a user to answer a question T(n) and then find the n that maximize T(n)/n ;)

NumberFormatter::SPELLOUT spellout-ordinal in russian and italian

NumberFormatter is using ICU formatting.

As you can check here: http://saxonica.com/html/documentation/extensibility/config-extend/localizing/ICU-numbering-dates/ICU-numbering.html

... Russian (ru) has following formatting available:

spellout-cardinal-feminine (scf)
spellout-cardinal-masculine (scm)
spellout-cardinal-neuter (scne)
spellout-numbering (sn)
spellout-numbering-year (sny)

... and Italian (it):

spellout-cardinal-feminine (scf)
spellout-cardinal-masculine (scm)
spellout-numbering (sn)
spellout-numbering-year (sny)
spellout-ordinal-feminine (sof)
spellout-ordinal-masculine (som)

That is why you will not be able to set ordinal format for (ru) and following code:

$nFormat = new NumberFormatter('it', NumberFormatter::SPELLOUT);
$nFormat->setTextAttribute(NumberFormatter::DEFAULT_RULESET, "%spellout-ordinal-feminine");

var_dump($nFormat->format(42));

Will print:

string 'quarantaduesima' (length=17)

Like you (propably) want.

EDIT:

Informations about used formatting with references to ICU: http://php.net/manual/en/numberformatter.create.php

Tested with PHP 5.4.x and ICU version => 51.2; ICU Data version => 51.2.
You can use shell command:

$ php -i | grep ICU

To check what version of ICU you have.

For latest ICU version you should propably install/update php-intl package: http://php.net/manual/en/intl.installation.php

EDIT 2:

I have created extension for NumberFormatter (so far with polish ordinals). Feel free to contribute another languages: https://github.com/arius86/number-formatter

Pivot Table with Non-Cardinal Values