Geek Answers Handbook

What SQL magic is needed to turn one column into several?

I need to print several tickets, each of which has enough space to store one set of customer details, along with codes for the five points ordered by this customer. Customers who order more than five items receive multiple tickets. Therefore, from the order table, for example,

Customer | Item
--------- | ------
Bob | Ftmch
Bob | Zorp
Bob | KLUGE
Carol | Ftmch
Carol | MEEP
Carol | Zorp
Ted | Foon
Ted | Smock
Alice | ORGO
Carol | SQICK
Carol | BLECH
Carol | KLUGE
Carol | GLURP

I need a query that returns this:

Customer | Item1 | Item2 | Item3 | Item4 | Item5
--------- | ------- | ------- | ------- | ------- | ------
Alice | ORGO | null | null | null | null
Bob      | FTMCH | ZORP  | KLUGE | null  | null
Carol    | FTMCH | MEEP  | ZORP  | SQICK | BLECH
Carol    | KLUGE | GLURP | null  | null  | null
Ted      | FOON  | SMOCK | null  | null  | null

- SQL ? HSQL OpenOffice.org, .

+5
5

, :

SELECT
    "Customer",
    MAX(CASE WHEN "Slot" = 0 THEN "Item" END) AS "Item1",
    MAX(CASE WHEN "Slot" = 1 THEN "Item" END) AS "Item2",
    MAX(CASE WHEN "Slot" = 2 THEN "Item" END) AS "Item3",
    MAX(CASE WHEN "Slot" = 3 THEN "Item" END) AS "Item4",
    MAX(CASE WHEN "Slot" = 4 THEN "Item" END) AS "Item5"
FROM (
    SELECT
        l."Customer" AS "Customer",
        l."Item" AS "Item",
        COUNT(r."Item") / 5 AS "Ticket",
        MOD(COUNT(r."Item"), 5) AS "Slot"
    FROM "Orders" AS l
    LEFT JOIN "Orders" AS r
    ON r."Customer" = l."Customer" AND r."Item" < l."Item"
    GROUP BY "Customer", "Item"
)
GROUP BY "Customer", "Ticket"
ORDER BY "Customer", "Ticket"

:

Customer | Item1 | Item2 | Item3 | Item4 | Item5 
---------|-------|-------|-------|-------|-------
Alice    | ORGO  |       |       |       |       
Bob      | FTMCH | KLUGE | ZORP  |       |       
Carol    | BLECH | FTMCH | GLURP | KLUGE | MEEP  
Carol    | SQICK | ZORP  |       |       |       
Ted      | FOON  | SMOCK |       |       |

, , , Metafilter.

(Followup edit:)

, : - (

-, . :

ID  | Customer | Item 
159 | Bob      | FTMCH
264 | Bob      | ZORP 
265 | Bob      | KLUGE
288 | Carol    | FTMCH
314 | Carol    | MEEP 
323 | Carol    | ZORP 
327 | Ted      | FOON 
338 | Ted      | SMOCK
358 | Alice    | ORGO 
419 | Carol    | SQICK
716 | Carol    | MEEP 
846 | Carol    | BLECH
939 | Carol    | MEEP 
950 | Carol    | GLURP
979 | Carol    | KLUGE

MEEP- Carol , :

SELECT
    "Customer",
    MAX(CASE WHEN "Slot" = 0 THEN "Item" END) AS "Item0",
    MAX(CASE WHEN "Slot" = 1 THEN "Item" END) AS "Item1",
    MAX(CASE WHEN "Slot" = 2 THEN "Item" END) AS "Item2",
    MAX(CASE WHEN "Slot" = 3 THEN "Item" END) AS "Item3",
    MAX(CASE WHEN "Slot" = 4 THEN "Item" END) AS "Item4",
    MAX(CASE WHEN "Slot" = 0 THEN "Quantity" END) AS "Qty0",
    MAX(CASE WHEN "Slot" = 1 THEN "Quantity" END) AS "Qty1",
    MAX(CASE WHEN "Slot" = 2 THEN "Quantity" END) AS "Qty2",
    MAX(CASE WHEN "Slot" = 3 THEN "Quantity" END) AS "Qty3",
    MAX(CASE WHEN "Slot" = 4 THEN "Quantity" END) AS "Qty4"
FROM (
    SELECT
        "Customer",
        "Item",
        COUNT("ID") AS "Quantity",
        "Rank" / 5 AS "Ticket",
        MOD("Rank", 5) AS "Slot"
    FROM (
        SELECT
            main."ID" AS "ID",
            main."Customer" AS "Customer",
            main."Item" AS "Item",
            COUNT(less."Item") AS "Rank"
        FROM "Orders" AS main
        LEFT JOIN (
            SELECT DISTINCT
                "Customer",
                "Item"
            FROM "Orders") AS less
        ON less."Customer" = main."Customer" AND less."Item" < main."Item"
        GROUP BY "ID", "Customer", "Item"
    )
    GROUP BY "Customer", "Item", "Rank"
)
GROUP BY "Customer", "Ticket"

:

Customer | Item0 | Item1 | Item2 | Item3 | Item4 | Qty0 | Qty1 | Qty2 | Qty3 | Qty3 | Qty4
Bob      | FTMCH | KLUGE | ZORP  |       |       | 1    | 1    | 1    |      |      |     
Carol    | BLECH | FTMCH | GLURP | KLUGE | MEEP  | 1    | 1    | 1    | 1    | 1    | 3   
Carol    | SQICK | ZORP  |       |       |       | 1    | 1    |      |      |      |     
Ted      | FOON  | SMOCK |       |       |       | 1    | 1    |      |      |      |     
Alice    | ORGO  |       |       |       |       | 1    |      |      |      |      |

, , , ( ).

, . -, , , ? SELECT LEFT JOIN Daily WTF?

+2

, , MySQL, OpenOffice, - :

select
    u.Customer,
    group_concat(u.Item) items
from
    (select
        t.Item,
        @n:=if(@c=t.Customer and @n<4,@n+1,0) c1,
        @m:=if(@n,@m,@m+1) g,
        @c:=t.Customer as Customer
    from
        t1 t, (select @m:=0) init
    order
        by t.Customer
    ) u
group by
    u.g

:

+----------+------------------------------+
| Customer | items                        |
+----------+------------------------------+
| Alice    | ORGO                         | 
| Bob      | FTMCH,ZORP,KLUGE             | 
| Carol    | KLUGE,ZORP,BLECH,SQICK,GLURP | 
| Carol    | MEEP,FTMCH                   | 
| Ted      | FOON,SMOCK                   | 
+----------+------------------------------+
0

, Carol. , - , OrderID OrderDate. ?

select m1.Customer, 
    min(m1.Item) as Item1, 
    min(m2.item) as Item2, 
    min(m3.item) as Item3, 
    min(m4.item) as Item4, 
    min(m5.item) as Item5
from CustomerOrder m1
left outer join CustomerOrder m2 on m1.Customer = m2.Customer 
    and m2.item > m1.item
left outer join CustomerOrder m3 on m1.Customer = m3.Customer 
    and m3.item > m2.item
left outer join CustomerOrder m4 on m1.Customer = m4.Customer 
    and m4.item > m3.item
left outer join CustomerOrder m5 on m1.Customer = m5.Customer 
    and m5.item > m4.item
group by m1.Customer

:

Customer       Item1      Item2      Item3      Item4      Item5
-------------- ---------- ---------- ---------- ---------- ----------
Alice          ORGO       NULL       NULL       NULL       NULL
Bob            FTMCH      KLUGE      ZORP       NULL       NULL
Carol          BLECH      FTMCH      GLURP      KLUGE      MEEP
Ted            FOON       SMOCK      NULL       NULL       NULL
0

SQL. , .

1) SQL, ISO/IEC/ANSI Standard SQL. - (while , , ). ; GROUP BY .. , , ( ). , , , .

2) , .. ; ( / ). . , ( ) .

, ; , , "" . ( SQL) , ( ). Eek, SQL. .

SQL, (HSQL - ), . SQL, , , .

(, "psuedo-SQL".)

ID

, , , - , . , . .

    CREATE TABLE Person (
    PersonId  IDENTITY NOT NULL
        PRIMARY KEY,
    FirstName CHAR(30) NOT NULL,
    LastName  CHAR(30) NOT NULL
    )

    INSERT Person VALUES ("Fred", "Astaire")
1 row(s) affected

    INSERT Person VALUES ("Ginger", "Rogers")
1 row(s) affected

    INSERT Person VALUES ("Fred", "Astaire")
1 row(s) affected

    SELECT * FROM Person

PersonId FirstName                      LastName
======== ============================== ==============================
       1 Fred                           Astaire
       2 Ginger                         Rogers
       3 Fred                           Astaire

3 row(s) affected
That a pure, unarguable duplicate row. The simple fact is. the ID column provides a row number, but does nothing to prevent duplicate rows. For that you need an Unique Index on the columns that determine uniqueness, as identified in the data model, for every relational table in the database (by definition, if the rows are not unique, it is not a Relational table). Otherwise it is just a file storage system.
    CREATE UNIQUE NONCLUSTERED INDEX U_Name
       ON Person (LastName, FirstName)

There is another form of data integrity (duplication) which I might identify while I am at it.

    INSERT Person VALUES ("Fred", "Astair")
1 row(s) affected

    INSERT Person VALUES ("Astaire", "Fred")
1 row(s) affected
All are preventable in SQL.
0

More articles:


All Articles