Блог

Я работаю над определением sql-запроса для использования в sql server compact edition 3.5 на телефоне Windows Mobile. Мне нужно будет получить результирующий набор из трех таблиц.

Я точно не помню названия всех столбцов, поскольку задаю этот вопрос дома, но вот хороший пример таблиц, с которыми я имею дело.

 Table 1: Customers
Table 2: PresoldOrders
Table 3: CustomerDetails
  

*

  ________________________________________
|                                        |
|--------------- Customers --------------|
|________________________________________|
|                                        |
| PK    int            CustomerNumber    |
|       varchar(125)   FirstName         |
|       varchar(125)   LastName          |
|       varchar(125)   Email             |
|       varchar(200)   Address1          |
|       varchar(200)   Address2          |
|       varchar(200)   City              |
|       varchar(2)     State             |
|       varchar(5)     Zip               |
|________________________________________|
  

*

  ________________________________________
|                                        |
|------------ CustomerDetails -----------|
|________________________________________|
|                                        |
| PK    int            CustomerDetailsId |
| FK    int            CustomerNumber    |
|       varchar(255)   FieldName         |
|       varchar(255)   FieldValue        |
|________________________________________|
  

*

  ________________________________________
|                                        |
|------------ PresoldOrders -------------|
|________________________________________|
|                                        |
| PK    int            PresoldOrderId    |
| FK    int            CustomerNumber    |
|       int            OrderNumber       |
|       int            RouteStopNumber   |
|       datetime       DeliveryDate      |
|       varchar(100)   Other1            |
|       varchar(100)   Other2            |
|________________________________________| 
  

Теперь запрос должен возвращать все записи, которые существуют в customers, даже если они не существуют в таблице ‘Presolderheaders’. Эта часть довольно проста, я планирую просто использовать левое внешнее соединение. Вторая часть запроса немного сложнее.

Вот запрос, который я создал на данный момент.

 SELECT c.CustomerNumber
       c.FirstName
       c.LastName
       c.Email
       c.Address1
       c.Address2
       c.City
       c.State
       c.Zip
       po.OrderNumber
       po.DeliveryDate
       po.Other1
       po.Other2
FROM Customer c
LEFT OUTER JOIN PresoldOrders po on c.CustomerNumber = po.CustomerNumber
ORDER BY po.RouteStopNumber; 
  

Сложная часть — это таблица CustomerDetails. Вот пример некоторых данных

  _________________________________________________________
|       |                 |              |                |
| PK    | CustomerNumber  | FieldName    | FieldValue     |
|-------|-----------------|--------------|----------------|
| 1     | 1               | A            | 125            |
|-------|-----------------|--------------|----------------|
| 2     | 1               | B            | 126            |
|-------|-----------------|--------------|----------------|
| 3     | 1               | C            | 127            |
|-------|-----------------|--------------|----------------|
| 4     | 2               | A            | 138            |
|-------|-----------------|--------------|----------------|
| 5     | 2               | B            | 140            |
|-------|-----------------|--------------|----------------|
| 6     | 2               | C            | 143            |
|-------|-----------------|--------------|----------------|
|_________________________________________________________|
  

Для информации, которую я буду отображать в гибкой сетке Component One, имя поля, указанное в таблице CustomerDetails, будет исправлено.

Вот что я хочу заархивировать:

  _____________________________________________________________________________________________________________________
|                 |           |          |     |                     |                     |                          |
| CustomerNumber  | FirstName | LastName | ... | FieldName A's value | FieldName B's Value | FieldName C's Value      |
|-----------------|-----------|----------|-----|---------------------|---------------------|--------------------------|
| 1               | John      | Someone  | ... | 125                 | 126                 | 127                      |
|-----------------|-----------|----------|-----|---------------------|---------------------|--------------------------|
| 2               | Dan       | Other    | ... | 138                 | 140                 | 143                      |
|-----------------|-----------|----------|-----|---------------------|---------------------|--------------------------|
|_____________________________________________________________________________________________________________________|
  

Обычно у меня были бы имена столбцов для A, B и C, определенные в таблице ‘CustomerDetails’; однако эта таблица не может быть изменена, поэтому я должен работать с тем, что мне дали. Требования в спецификации для моей задачи — иметь более 15 столбцов для отображения в таблице на мобильном устройстве; не то, на что я бы пошел, но это требования.

Хорошо, наконец, вопрос:

Можно ли использовать sql для запроса таблицы пар ключ-значение и отобразить значения этих ключей в столбцах, подобных приведенным выше? Это мое требование, и я думаю, что мне нужно будет создать один запрос с моим соединением в таблице presoldorders, а затем получить список всех сведений для каждого клиента в списке, выполнить итерацию и объединить в таблицу данных в коде на КПК.

Если вы заранее знаете все значения ключа, вы можете свернуть результирующий набор. Я не уверен, поддерживает ли sql server compact PIVOT , но вы можете сделать: select CustomerNumber,
Max(Case when FieldName='A' then FieldValue end) as a_value,
// the same for B, C, all keys.
From CustomerDetails
Group by CustomerNumber
Для простоты я не объединяю другие таблицы, но я надеюсь, что это даст вам представление о том, как превратить строки в столбцы.