Нужно динамически сводить таблицу делая столбец даты новыми заголовками таблицы

Я видел почти все примеры pivot в интернете, как текстовые учебники, так и видео. Ни один из них не решает проблему в данный момент.

То, что я хочу, - это свернуть следующую таблицу:

Таблица Продаж 

    SalesId  | SalesDate   | SalesLocation  | SalesAmount
    -----------------------------------------------------
     1       | 2012-03     | New York, NY   | 3,000
     2       | 2012-04     | Miami, FL      | 2,500
     3       | 2012-05     | Carmel, CA     | 2,850
     4       | 2012-06     | Berkeley, CA   | 1,900
     5       | 2012-07     | Akron, OH      | 4,200
     6       | 2012-08     | Portland, OR   | 2,200

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

    Account Info    | 2012-03      | 2012-04     | 2012-05     | 2012-06
    ---------------------------------------------------------------------
    SalesId         | 1            | 2           | 3           | 4
    SalesLocation   | New York, NY | Miami, FL   | Carmel, CA  | Berkeley, CA
    SalesAmount     | 3,000        | 2,500       | 2,850       | 1,900
Большинство учебных пособий по этому предмету включают в себя создание сумм из нескольких строк, которые имеют либо один и тот же город, один и тот же статус или один и тот же тип.

В этой ситуации каждая строка уже имеет определенную уникальную дату, которая никогда не будет дублироваться, поэтому все записи уникальны и поэтому не требуют функции, подобной SUM.

До сих пор, после просмотра невероятно ясного решения Джойсью на оси с использованием Столбец даты поток на ASP.net форумы, я зашел так далеко: 

    declare @cols varchar(max)

    select @cols = (select distinct SalesDate from SalesTable for xml path(''))

    select @cols = replace(@cols, '', '[')
    select @cols = replace(@cols, '', '],')
    select @cols = left(@cols, len(@cols) - 1)

    declare @query varchar(max)

    select @query = 'select * from (select SalesId, SalesLocation, SalesAmount 
    from SalesTable) src pivot (max(SalesAmount) for SalesDate in ('
    + @cols + ')) piv;'

    execute(@query)

Хотя это не то, что я искал, это единственный учебник, который позволил мне фактически вернуть результирующий набор, где верхние столбцы на самом деле являются датами (yay), как показано ниже: 

    SalesId | SalesLocation  | SalesAmount | 2012-03 | 2012-04 | 2012-05 
    1       | New York, NY   | 3,000       | {null}  | {null}  | 4
    2       | Miami, FL      | 2,500       | {null}  | 3       | {null}
    3       | Carmel, CA     | 2,850       | {null}  | {null}  | {null}
    4       | Berkeley, CA   | 1,900       | 1       | {null}  | {null}
    5       | Akron, OH      | 4,200       | {null}  | {null}  | {null}
    6       | Portland, OR   | 2,200       | {null}  | 2       | {null}

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

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

2 2
sql-server tsql pivot

2 ответа:

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

DECLARE @cols AS NVARCHAR(MAX),
    @Convertcols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @SQL  AS NVARCHAR(MAX),
    @PivotColumn Varchar(100),
    @i Int = 1,
    @AggColumn Varchar(100)
DECLARE @Columns TABLE (ID int IDENTITY(1,1), AccountInfo Varchar(max))

SELECT @cols = STUFF((SELECT  ', '+ QUOTENAME(SalesDate)
                    from Sales
                    group by SalesDate
                    order by SalesDate
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SELECT @Convertcols = STUFF((SELECT  ', CAST( '+ QUOTENAME(SalesDate) + ' AS VARCHAR(max)) AS ' + QUOTENAME(SalesDate)
                    from Sales
                    group by SalesDate
                    order by SalesDate
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

INSERT INTO @Columns
SELECT C.name  
FROM SYS.COLUMNS C
INNER JOIN SYS.TABLES  T   ON C.OBJECT_ID = T.OBJECT_ID
WHERE T.NAME = 'Sales'
AND C.name <> 'SalesDate'

While @i <= (SELECT MAX(ID) FROM @Columns)
BEGIN 
SELECT @AggColumn = AccountInfo FROM @Columns Where ID = @i

set @query = 'SELECT AccountInfo,' + @Convertcols +
             ' FROM 
             (
                SELECT ''' + @AggColumn + ''' AS AccountInfo, SalesDate, '+ @AggColumn +' 
                FROM Sales
            ) X
            PIVOT 
            (
                Max('+ @AggColumn +')
                FOR SalesDate in (' + @cols + ')
            ) P '


SET @SQL = CONCAT(@SQL, CHAR(10), CASE WHEN @i = 1 THEN '' ELSE 'UNION ' END, CHAR(10) , @query) 
SET @i = @i+1  

END 

EXECUTE (@SQL)

Результат:

Введите описание изображения здесь

1

Для того, чтобы вы могли вращаться, вам нужно иметь по крайней мере один неповоротный столбец-то, чего не хватает вашей модели.

Документация: https://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

0