Как я могу использовать SQL Pivot для этого?

У меня есть набор данных, который организован следующим образом:

Timestamp|A0001|A0002|A0003|A0004|B0001|B0002|B0003|B0004 ...
---------+-----+-----+-----+-----+-----+-----+-----+-----
2008-1-1 |  1  |  2  | 10  |   6 |  20 |  35 | 300 |  8
2008-1-2 |  5  |  2  |  9  |   3 |  50 |  38 | 290 |  2    
2008-1-4 |  7  |  7  | 11  |   0 |  30 |  87 | 350 |  0
2008-1-5 |  1  |  9  |  1  |   0 |  25 | 100 |  10 |  0
...

Где A0001 — это значение A элемента № 1, а B0001 — значение B элемента № 1. В таблице может быть более 60 различных элементов, и каждый элемент имеет столбец значений A и столбец значений B, то есть всего в таблице более 120 столбцов.

Я хочу получить результат из 3 столбцов (индекс элемента, значение A, значение B), который суммирует значения A и B для каждого элемента:

Index | A Value | B Value
------+---------+--------
 0001 |   14    |   125
 0002 |   20    |   260
 0003 |   31    |   950
 0004 |    9    |    10
 ....

Когда я перехожу от столбцов к строкам, я ожидаю поворота в решении, но я не уверен, как это конкретизировать. Часть проблемы заключается в том, как убрать A и B, чтобы сформировать значения для столбца Index. Другая часть заключается в том, что мне никогда раньше не приходилось использовать Pivot, поэтому я также спотыкаюсь о базовый синтаксис.

Я думаю, что в конечном итоге мне нужно иметь многошаговое решение, которое сначала строит суммирование как:

ColName | Value
--------+------
A0001   |  14
A0002   |  20
A0003   |  31
A0004   |   9
B0001   | 125
B0002   | 260
B0003   | 950
B0004   |  10

Затем измените данные ColName, чтобы удалить индекс:

ColName | Value | Index | Aspect
--------+-------+-------+-------
A0001   |  14   | 0001  |  A
A0002   |  20   | 0002  |  A
A0003   |  31   | 0003  |  A
A0004   |   9   | 0004  |  A
B0001   | 125   | 0001  |  B
B0002   | 260   | 0002  |  B
B0003   | 950   | 0003  |  B
B0004   |  10   | 0004  |  B

Наконец, самообъединение, чтобы переместить значения B рядом со значениями A.

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

Примечание 1) Решение должно быть в T-SQL на MSSQL 2005.

Примечание 2) Формат таблицы изменить нельзя.

Редактировать Другой метод, о котором я думал, использует UNION и отдельные SUM() для каждого столбца:

SELECT '0001' as Index, SUM(A0001) as A, SUM(B0001) as B FROM TABLE
UNION
SELECT '0002' as Index, SUM(A0002) as A, SUM(B0002) as B FROM TABLE
UNION
SELECT '0003' as Index, SUM(A0003) as A, SUM(B0003) as B FROM TABLE
UNION
SELECT '0004' as Index, SUM(A0004) as A, SUM(B0004) as B FROM TABLE
UNION
...

Но и этот подход выглядит не очень красиво

EDIT На данный момент есть 2 отличных ответа. Но я хотел бы добавить в запрос еще два условия :-)

1) Мне нужно выбрать строки на основе диапазона временных меток (minv ‹ timestamp ‹ maxv).

2) Мне также нужно условно выбрать строки в UDF, который обрабатывает метку времени

Используя имена таблиц Бретски, приведенное выше переводится как:

...
(SELECT A0001, A0002, A0003, B0001, B0002, B0003 
 FROM ptest 
 WHERE timestamp>minv AND timestamp<maxv AND fn(timestamp)=fnv) p
unpivot
(val for item in (A0001, A0002, A0003, B0001, B0002, B0003)) as unpvt
...

Учитывая, что я условно добавил требование fn(), я думаю, что мне также нужно пойти по пути динамического SQL, предложенному Джонатаном. Тем более, что мне нужно построить один и тот же запрос для 12 разных таблиц - все в одном стиле.


sql-server tsql pivot sql-server-2005
person Peter M    schedule 21.11.2008    source источник

Ответы (2)


arrow_upward
5
arrow_downward

Такой же ответ здесь, это было весело:

-- Get column names from system table
DECLARE @phCols NVARCHAR(2000)
SELECT @phCols = COALESCE(@phCols + ',[' + name + ']', '[' + name + ']') 
    FROM syscolumns WHERE id = (select id from sysobjects where name = 'Test' and type='U')

-- Get rid of the column we don't want
SELECT @phCols = REPLACE(@phCols, '[Timestamp],', '')

-- Query & sum using the dynamic column names
DECLARE @exec nvarchar(2000)
SELECT @exec =
'
    select
        SUBSTRING([Value], 2, LEN([Value]) - 1) as [Index],
        SUM(CASE WHEN (LEFT([Value], 1) = ''A'') THEN Cols ELSE 0 END) as AValue, 
        SUM(CASE WHEN (LEFT([Value], 1) = ''B'') THEN Cols ELSE 0 END) as BValue
    FROM
    (
        select *
        from (select ' + @phCols + ' from Test) as t
        unpivot (Cols FOR [Value] in (' + @phCols + ')) as p
    ) _temp
    GROUP BY SUBSTRING([Value], 2, LEN([Value]) - 1)
'
EXECUTE(@exec)

Вам не нужно жестко кодировать имена столбцов в этом.

person Jonathan DeMarks    schedule 21.11.2008
comment
Спасибо Джонатан. Я думал об этом способе устранения жесткого кодирования столбцов, но не собрал его вместе. Я ненавидел думать о перечислении 160+ столбцов. :) - person Brettski; 21.11.2008
comment
Вы также можете добавить временную метку where в этот запрос, просто измените [[ from (выберите ' + @phCols + ' from Test) as t]] на [[ from (select ' + @phCols + ' from Test WHERE timestamp› minv И метка времени‹maxv И fn(метка времени)=fnv) as t ]] - person Jonathan DeMarks; 21.11.2008
comment
Это на самом деле хуже, чем это .. намного, намного хуже. 12 наборов таблиц, и каждый набор имеет 123 уникальных имени элемента, всего 1476 *items или 2952 имени столбца. Кошмар обслуживания! - person Peter M; 21.11.2008
comment
И, конечно же, когда я запускал решение для своих реальных таблиц, я достиг предела в 4000 символов, передаваемых в exec! Поэтому мне пришлось разделить решение на две части. - person Peter M; 21.11.2008

arrow_upward
1
arrow_downward

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

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

-- Create the temp table
CREATE TABLE #s (item nvarchar(10), val int)

-- Insert UNPIVOT product into the temp table
INSERT INTO  #s (item, val)
SELECT item, val
FROM
(SELECT A0001, A0002, A0003, B0001, B0002, B0003
FROM ptest) p
unpivot
(val for item in (A0001, A0002, A0003, B0001, B0002, B0003)) as unpvt

-- Query the temp table to get final data set
SELECT RIGHT(item, 4) as item1,
Sum(CASE WHEN LEFT(item, 1) = 'A' THEN val ELSE 0 END) as A,
Sum(CASE WHEN LEFT(item, 1) = 'B' THEN val ELSE 0 END) as B
from #s
GROUP BY RIGHT(item, 4)

-- Delete temp table 
drop table #s

Кстати, спасибо за вопрос, я впервые использовал UNPIVOT. Всегда хотел, просто никогда не было необходимости.

person Brettski    schedule 21.11.2008
comment
Спасибо за это. Я вроде как понимаю, откуда ты, но от этого болит голова. У меня есть одна небольшая проблема, связанная с тестом SELECT FROM, о котором я думаю, что должен был упомянуть. Мне нужно выбрать начальные строки на основе диапазона временных меток (что не является проблемой) - person Peter M; 21.11.2008
comment
Но также мне нужно выбрать на основе функции метки времени. Так что я должен: (ВЫБЕРИТЕ fn(timestamp) as fnv, A001 .. FROM ptest WHERE timestamp‹XX и fnv=yyy), а затем только unpivot на A и B? - person Peter M; 21.11.2008
comment
Да, это немного усложняет ситуацию, не так ли. - person Brettski; 21.11.2008