транспонировать строки в столбцы в sql

У меня проблема с получением желаемого результата с помощью SQL-запроса.

Мои данные sql следующие:

TOTAL   Charge      PAYMNET      A         B        C          D       E      MonthYear
------- ----------- ----------- --------- -------- ---------- ------- ------- ----------
661     157832.24   82967.80    700.00    10.70    58329.33   0.00    0.00    Oct-2013
612     95030.52    17824.28    850.00    66.10    53971.41   0.00    0.00    Nov-2013
584     90256.35    16732.91    700.00    66.10    52219.87   0.00    0.00    Dec-2013
511     72217.32    12336.12    285.00    53.17    42951.12   0.00    0.00    Jan-2014

Мне нужен вывод следующим образом,

Data            Jan-2013            Feb-2013            Mar-2013

TOTALCOUNT      761                 647                 671
Charge          126888              119995              151737.5
Payment         25705.4             26235.47            28704.41
A               1089.08             1020                745
B               2100.4              1947.25             1868.22
C               94246.55            84202.15            115673.7
D               0                   0                   0
E               0                   0                   0

Я видел примеры pivot и unpivot, в pivot я не получаю заголовки столбцов в виде данных строки, а в unpivot я не нашел примера, где я могу транспонировать несколько столбцов. У меня есть еще один вариант получить этот результат в коде. Но я хочу знать, можно ли получить такой результат в sql?

Изменить

Результат даст только на 3-4 месяца, не больше.

Обновление. Первый образец данных — это фактические данные, которые я получу в результате нескольких объединений и группировки в нескольких таблицах, которые я буду хранить во временной таблице. Я попытался получить требуемый результат, изменив запрос, что невозможно из-за структуры таблицы. Мне удалось получить результат как в данных первого примера, но это не то, что хочет видеть клиент!!! Поэтому мне нужно обработать данные временной таблицы, которые будут состоять всего из 3-4 строк, в требуемый вывод. Запрос для получения первого результата: select * from temp. Обработка должна быть выполнена с результатом таблицы temp.

Обновление-2

Я пробовал следующий запрос

declare @cols varchar(max)
select @cols = STUFF((select ', ' + MonthYear
                      from #tmp for xml path('')),1,1,'')

declare @query varchar(max)
set @query = 
        'select ''TOTAL'' as Data,' +@cols+' from
        (select MonthYear,TOTALCLAIMS from #tmp)st
        pivot
        (
            MAX(TOTAL) for MonthYear in (' + @cols + ')
        )pt;'

Что дало мне первую строку правильно!!! Но я пытался использовать union как

set @query = 
        'select ''TOTAL'' as Data,' +@cols+' from
        (select MonthYear,TOTALCLAIMS from #tmp)st
        pivot
        (
            MAX(TOTAL) for MonthYear in (' + @cols + ')
        )pt;
        union
        select ''CHARGES'' as Data,' +@cols+' from
        (select MonthYear,TOTALCLAIMS from #tmp)st
        pivot
        (
            MAX(CHARGES) for MonthYear in (' + @cols + ')
        )pt;'

Что дает ошибку как incorrect syntax near union. Кто-нибудь знает, как объединить результаты pivot? Или есть лучший способ сделать это?

Спасибо.

Bharadwaj 27.03.2014 источник

comment

Будете ли вы иметь несколько записей для одного и того же месяца и года? Если да, то что с ними делать? Пожалуйста уточни. - samar 27.03.2014

comment

@samar Нет. У меня не будет нескольких записей за один и тот же месяц и год. - Bharadwaj 27.03.2014

comment

Это может помочь, но не тривиально stackoverflow.com/questions/18023479/ - jwize 27.03.2014

comment

Другой способ - перебрать таблицу и создать нужную таблицу. Но это будет нелегко, как с точки зрения кодирования, так и с точки зрения производительности. - samar 27.03.2014

comment

убрать точку с запятой перед каждым объединением - samar 27.03.2014

Ответы (3)

arrow_upward
1
arrow_downward

Я пробовал этот код. Пожалуйста, проверьте и дайте мне знать, если это работает

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

--Can have more columns like A,B,...
DECLARE @tbl TABLE
(
TOTAL INT,
CHARGE FLOAT,
PAYMENT FLOAT,
MONTHYEAR VARCHAR(50)
)


--Test data
INSERT INTO @tbl SELECT 661, 157832.24, 82967.80, 'Oct2013'
INSERT INTO @tbl SELECT 612,     95030.52,    17824.28, 'Nov2013'
INSERT INTO @tbl SELECT 584     ,90256.35,    16732.91, 'Dec2013'

--Can be a physical table
CREATE TABLE #FinalTbl 
(
DATA VARCHAR(100)
)

--inserted hardcode records in data column. To add it dynamically you would need to loop through information_schema.columns
--SELECT *
--FROM information_schema.columns
--WHERE table_name = 'tbl_name'
INSERT INTO #FinalTbl
VALUES ('TOTAL')

INSERT INTO #FinalTbl
VALUES ('CHARGE')

INSERT INTO #FinalTbl
VALUES ('PAYMENT')

DECLARE @StartCount INT, @TotalCount INT, @Query VARCHAR(5000), @TOTAL INT,@CHARGE FLOAT,@PAYMENT FLOAT,@MONTHYEAR VARCHAR(50)

SELECT @TotalCount = COUNT(*) FROM @tbl;
SET @StartCount = 1;

WHILE(@StartCount <= @TotalCount)
BEGIN
    SELECT @TOTAL = TOTAL, 
    @CHARGE = CHARGE,
    @PAYMENT = PAYMENT,
    @MONTHYEAR = MONTHYEAR  
    FROM
    (SELECT ROW_NUMBER() over(ORDER BY MONTHYEAR) AS ROWNUM, * FROM @tbl) as tbl
    WHERE ROWNUM = @StartCount

    SELECT @Query = 'ALTER TABLE #FinalTbl ADD ' + @MONTHYEAR + ' VARCHAR(1000)'
    EXEC (@Query)

    SELECT @Query = 'UPDATE #FinalTbl SET ' + @MONTHYEAR + ' = ''' + CONVERT(VARCHAR(50), @TOTAL) + ''' WHERE DATA = ''TOTAL'''
    EXEC (@Query)

    SELECT @Query = 'UPDATE #FinalTbl SET ' + @MONTHYEAR + ' = ''' + CONVERT(VARCHAR(50), @CHARGE) + ''' WHERE DATA = ''CHARGE'''
    EXEC (@Query)

    SELECT @Query = 'UPDATE #FinalTbl SET ' + @MONTHYEAR + ' = ''' + CONVERT(VARCHAR(50), @PAYMENT) + ''' WHERE DATA = ''PAYMENT'''
    EXEC (@Query)

    SELECT @StartCount = @StartCount + 1
END

SELECT * FROM #FinalTbl

DROP TABLE #FinalTbl

Надеюсь это поможет

samar 27.03.2014

comment

Этот запрос работает нормально отдельно. Но когда я реализовал то же самое в своей процедуре, он показывает значение null во всех столбцах, кроме столбца data. - Bharadwaj; 27.03.2014

comment

Понятно :) Несоответствие имени столбца. Спасибо. - Bharadwaj; 27.03.2014

arrow_upward
0
arrow_downward

Я предполагаю, что причина, по которой вы получаете только 3 или 4 месяца, заключается в том, что у вас нет данных за недостающие месяцы? Если вы хотите отобразить столбцы для отсутствующих месяцев, вам необходимо:

Создайте тип данных Table со всеми месяцами, которые вы хотите отобразить, и присоедините к нему остальные таблицы в своем запросе. Затем вы можете использовать функцию PIVOT как обычно.
Если вы знаете, сколько столбцов заранее, то есть по одному на каждый месяц в конкретном году, и оно не изменится, вы можете просто использовать операторы CASE (по одному на каждый месяц) для переноса данных без оператора PIVOT.

При необходимости могу привести примеры.

sarin 27.03.2014

comment

Пропущенных месяцев нет. Отправлять только данные за 3 или 4 месяца. То, что я показываю в вопросе, является образцом данных. - Bharadwaj; 27.03.2014

comment

отобразите ваш запрос, чтобы помочь, пожалуйста - sarin; 27.03.2014

comment

Результат, который я дал, - это то, что я получаю в таблице temp. Я не могу изменить основной запрос [мне не разрешено :(] и это слишком большой запрос. - Bharadwaj; 27.03.2014

arrow_upward
0
arrow_downward

Select Month(Mdate)md,'A' AS Col,sum(A) as a from Product group by Month(MDate)
union all
Select Month(Mdate)md,'B',sum(b) as a from Product group by Month(MDate)
union all
Select Month(Mdate)md,'C',sum(c) as a from Product group by Month(MDate)
union all
Select Month(Mdate)md,'D',Count(A) as a from Product group by Month(MDate)

Попробуйте Pivot с приведенным выше запросом, чтобы получить требуемый результат....

Nagu 27.03.2014

comment

Приведенный выше запрос select * from tmp. Не могли бы вы опубликовать запрос pivot также в результате. - Bharadwaj; 27.03.2014

транспонировать строки в столбцы в sql

Ответы (3)

Вопросы по теме